SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conversion from string "" to type 'Date' is not valid.

    Hey everyone,
    I know that there are hundreds of articles online about this same error, but I have been going at this for almost 2 days and it's time I just started a thread of my own.

    In my application, I have a textbox that has a CalendarExtender hooked up to it as shown here;

    Code:
    <asp:TextBox ID="ActualAdmitDate" runat="server"></asp:TextBox>
                                <asp:CalendarExtender ID="Calendarextender2" runat="server" CssClass="calStyle" 
                                    Enabled="True" Format="MMMM d, yyyy" PopupButtonID="calImg3" 
                                    TargetControlID="ActualAdmitDate">
                                </asp:CalendarExtender><img id="calImg3" alt="" class="style10" src="Styles/calendar.png" />
    I also have more textboxes and controls that take information, but this issue is only happening with the date selections.
    Basically, it's possible that a user doesn't select a date out of this CalendarExtender textbox, leaving the value as ""

    I am trying to figure out how I can either store a Null value or "" in my database if the user doesn't select a date in the CalendarExtender. Here is the code I am currently using, along with the stored procedure that the values are passed into. Here is one condition of the code being used

    The DecDateMade variable is the variable that is throwing the error.

    Code:
                Dim DecMadeBy As String
                Dim DecDateMade As Date
                Dim DecTimeMade As String
                Dim ContID As String
                    Dim ContText As String
                    Dim ReferElsewhere As String
                    Dim ReferLocation As String
                    If B4A.Checked = True Then
                        ReferElsewhere = "Yes"
                        If B4McLean.SelectedIndex <> 0 Then
                            ReferLocation = B4McLean.SelectedValue
                        Else
                            ReferLocation = B4Else.Text
                        End If
                    ElseIf B4B.Checked = True Then
                        ReferElsewhere = "No"
                        ReferLocation = "N/A"
                    End If
    If CheckBoxList1.SelectedValue = "" And CheckBoxList2.SelectedValue = "" And PatNoCriteria.Checked = False And ClosedOther.Checked = False Then
    
                        DecMadeBy = MadeBy.Text
                        If (DecisionDate.Text = "") Then
                            DecDateMade = Nothing
                        Else
                            DecDateMade = Convert.ToDateTime(DecisionDate.Text)
                        End If
                        If (DecisionTime.Text.ToString = "") Then
                            DecTimeMade = "N/A"
                        Else
                            DecTimeMade = DecisionTime.Text
                        End If
                        ContID = ""
                        ContText = ""
                        AdmDecNoAdmit(DecDateMade, DecTimeMade, DecMadeBy, ContID, ContText, ReferElsewhere, ReferLocation)
    End If

    Stored Procedure:
    Code:
        Protected Sub AdmDecNoAdmit(ByVal DecDateMade As Date, ByVal DecTimeMade As String, ByVal NoAdmDecMadeBy As String, ByVal Cont As String, ByVal ContText As String, ByVal ReferElsewhere As String, ByVal ReferLocation As String)
            Dim strSQL As String = "INSERT into NoAdmit(InquiryID_fk,ContID, ContText, ReferElsewhere, ReferLocation, MadeBy, DecisionDate, DecisionTime)VALUES(@InquiryID, @ContID, @ContText, @ReferElsewhere, @ReferLocation, @MadeBy, @DecDateMade, @DecTimeMade)SELECT ID = SCOPE_IDENTITY()"
            Dim myConn As New SqlConnection(StringConnection)
            Dim queryCommand As New SqlCommand
     
            queryCommand.Connection = myConn
     
            With queryCommand
                .CommandType = CommandType.Text
                .CommandText = strSQL
                .Parameters.AddWithValue("@InquiryID", Session("NewInquiryID"))
                .Parameters.AddWithValue("@ContID", Cont)
                .Parameters.AddWithValue("@ContText", ContText)
                .Parameters.AddWithValue("@ReferElsewhere", ReferElsewhere)
                .Parameters.AddWithValue("@ReferLocation", ReferLocation)
                .Parameters.AddWithValue("@MadeBy", NoAdmDecMadeBy)
                .Parameters.AddWithValue("@DecDateMade", DecDateMade)
                .Parameters.AddWithValue("@DecTimeMade", DecTimeMade)
                myConn.Open()
                Session("DecID") = .ExecuteScalar()
                Session("DecID") = Convert.ToInt32(Session("DecID"))
                'MsgBox(Session("DecID"), MsgBoxStyle.Critical)
                myConn.Close()
            End With
            queryCommand.Dispose()
     
            Dim UpdSQL As String = "Update Inquiry SET DecisionMade = 'Do Not Admit', DecisionID='" & Session("DecID") & "' WHERE Inquiry.ID = '" & Session("NewInquiryID") & "'"
            Dim UpdateCommand As New SqlCommand
            UpdateCommand.Connection = myConn
            With UpdateCommand
                .CommandType = CommandType.Text
                .CommandText = UpdSQL
                myConn.Open()
                .ExecuteNonQuery()
                myConn.Close()
            End With
            myConn.Dispose()
            UpdateCommand.Dispose()
     
        End Sub

  2. #2
    SitePoint Addict darkwater23's Avatar
    Join Date
    Nov 2005
    Location
    Omaha, NE
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey!

    You should be able to do something like this:

    Code VBNET:
     
    If DecDateMade Is Nothing Then
        queryCommand.Parameters.AddWithValue("@DecDateMade", DBNull) ' Or DBNull.Value depending on the framework, I believe
    Else
        queryCommand.Parameters.AddWithValue("@DecDateMade", DecDateMade)
    End If

    This assumes that your table is setup to allow null values for that date field, though.
    Last edited by darkwater23; Mar 15, 2012 at 15:32. Reason: Left something out

  3. #3
    SitePoint Guru Jason__C's Avatar
    Join Date
    Oct 2009
    Location
    Racoon City
    Posts
    660
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Shouldn't you use DateTime instead of Date object?
    Chuck Norris is so tough,
    mosquitos ask for permission before they bite him

  4. #4
    SitePoint Addict darkwater23's Avatar
    Join Date
    Nov 2005
    Location
    Omaha, NE
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't know if the DateTime is required. I usually code in C# and I always use a DateTime, but the OP may be ok with a Date. I was focusing more on how to get a null value in the table. Since the code is setup to pass a Date object set to Nothing if the user didn't select a date, I'm suggesting using that as a flag and I think the DBNull is what the OP needs.

  5. #5
    SitePoint Member
    Join Date
    Nov 2011
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    darkwater, your condition inside the stored procedure worked perfectly. I had to modify a couple things inside my code and variable declartions, but everything is storing perfectly. Also, I believe the syntax for asp.net4 is DBNull.Value, that is storing perfectly for me.
    Thanks again to everyone for their help, I couldn't have gotten this done without you guys.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •