[RESOLVED] Need Help with Handling Null

[viperbyte]

Hello everybody. In an insert routine I get a "FormatException was unhandled" - "Failed to convert parameter value from a String to a DateTime." error when attempting to insert a record. The error points to myCommand.ExecuteNonQuery(). In the record insert attempt I put values in txtFirstName and txtLastName and purposely leave the rest of the fields blank. In the table FirstName and LastName must have values. For the rest of the columns NULL is allowed. How do you guys and gals handle this situation in a graceful way? I haven't coded any validation yet. I don't want to insert '0' or 'n/a' or anything like that in the table where NULLs are permitted. Would someone please share their insight?

Dim myCommand As SqlCommand
myCommand = New SqlCommand
myCommand.Connection = CN
myCommand.CommandText = "sInsertGuide"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, ParameterDirection.Output).Value = Me.TxtFirstName.Text
        myCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 50, ParameterDirection.Output).Value = Me.TxtLastName.Text
        myCommand.Parameters.Add("@Qualification", SqlDbType.VarChar, 2048, ParameterDirection.Output).Value = Me.TxtQuailfication.Text
        myCommand.Parameters.Add("@DateOfBirth", SqlDbType.DateTime, 10, ParameterDirection.Output).Value = Me.TxtDateOfBirth.Text
        myCommand.Parameters.Add("@DateOfHire", SqlDbType.DateTime, 10, ParameterDirection.Output).Value = Me.TxtDateOfHire.Text

myCommand.ExecuteNonQuery()

[HanneSThEGreaT]

Concerning your Visitor Message :

Hey, howdy, hello!

have a look at this thread :

http://www.codeguru.com/forum/showthread.php?t=403073

It shows how to post properly.

Thanx for trying to do things right!

[viperbyte]

Thanks for the scoop.

[HanneSThEGreaT]

No problem That is what I'm here for.

As to your problem, I think you should cast / convert your Me.TxtDateOfHire.Text and Me.TxtDateOfBirth.Text to DateTime or date.

Something like :

CDate(Me.TxtDateOfBirth.Text)

Or properly ( for your context ) :

myCommand.Parameters.Add("@DateOfHire", SqlDbType.DateTime, 10, ParameterDirection.Output).Value = CDate(Me.TxtDateOfHire.Text)

I hope it helps.

[viperbyte]

Well HanneSThGreat that CDate() idea didn't work. I'm going to go with plan 'B'. I'll try to send in a default value to the stored procedure of '1/1/1900' if the field date is blank in the send routine. And then change the stored procedure to send in null(unknown) to the column if '1/1/1900' is the value sent in by the client. This is not what I had in mind as a graceful solution. It's messy as far as I'm concerned and still hope someone can help me. Thanks for the suggestion though.

[DataMiser]

It would seem that the problem is that you are sending an empty string to a field that is expecting a date. This should not be confused with a null. Have you tried testing to see if your textbox contains a valid date beforehand and if not set the value to null rather than the default empty string?

[sotoasty]

I agree with DataMiser. If your field is empty, that is not the same as null. So when the DB tries to store an empty string, the data does not match the required criteria for the field (it must be a date or null). Check the value of "Me.TxtDateOfHire.Text", if it is empty, set the parameter value to System.DBNull.

[viperbyte]

Hi guys. Thanks to your help i was able to insert NULL. I used a variable 'myNull' of System.DBNull to use in case the TxtDateOfBirth = "". Same for the other text box. So it's:if TxtDateOfHire.txt = "" then use mynull for the value. So great I now have a few rows with NULLs in date columns. New problem though. Now my listview can't load the rows that have NULLS in the datecolumns. I have a variable of type SqlDataReader that can't read NULL values. I get a "Data is Null. This method or property cannot be called on Null values." error. Can somesome offer some help on how to get my rdr loop code keep marching along? It's looking grim.

    Private Sub LoadGuides()
        Dim i As Integer = 0
        Dim Cmd As New SqlCommand("sReadAllGuides", CN)
        Cmd.CommandType = CommandType.StoredProcedure
        Dim lSingleItem As ListViewItem
        Dim rdr As SqlDataReader = Cmd.ExecuteReader
        ListView1.Items.Clear()
        If rdr.HasRows Then
            While rdr.Read()
                lSingleItem = ListView1.Items.Add(rdr.GetString(0), 0)
                lSingleItem.SubItems.Add(rdr.GetString(1))
                lSingleItem.SubItems.Add(rdr.GetString(2))
                lSingleItem.SubItems.Add(rdr.GetDateTime(3)) '<---- el chokeoh
                lSingleItem.SubItems.Add(rdr.GetDateTime(4))
                lSingleItem.SubItems.Add(rdr.GetValue(5))
            End While
        End If
        rdr.Close()
    End Sub

[viperbyte]

I found some help on handling the datareader NULL situation. My sub works now. Thank you to all for your help!

    Private Sub LoadGuides()
        Dim i As Integer = 0
        Dim Cmd As New SqlCommand("sReadAllGuides", CN)
        Cmd.CommandType = CommandType.StoredProcedure
        Dim lSingleItem As ListViewItem
        Dim rdr As SqlDataReader = Cmd.ExecuteReader
        ListView1.Items.Clear()
        If rdr.HasRows Then
            While rdr.Read()
                lSingleItem = ListView1.Items.Add(rdr.GetString(0), 0)
                lSingleItem.SubItems.Add(rdr.GetString(1))
                lSingleItem.SubItems.Add(rdr.GetString(2))
                If Not (rdr.IsDBNull(rdr.GetOrdinal("DateOfBirth"))) Then
                    lSingleItem.SubItems.Add(rdr.GetDateTime(3))
                Else
                    lSingleItem.SubItems.Add(" ")
                End If
                If Not (rdr.IsDBNull(rdr.GetOrdinal("DateHire"))) Then
                    lSingleItem.SubItems.Add(rdr.GetDateTime(4))
                Else
                    lSingleItem.SubItems.Add(" ")
                End If
                lSingleItem.SubItems.Add(rdr.GetValue(5))
            End While
        End If
        rdr.Close()
    End Sub