(Resolved) Conversion from type 'DBNull' to type 'String' is not valid

[FastEddie]

I am importing an Excel spreadsheet, turning it into a dataset and inserting the results into SQL Server (only 100 rows).

I am having an issue with one of the date columns in Excel, which can have a value or not. The "F" column is the one that may be blank and this is the error I get: Conversion from type 'DBNull' to type 'String' is not valid.

How can I add a check to the Dim F As Date = CDate(row(5)) to handle a null?

' The use of letters instead of
    ' more descriptive variables is
    ' strictly for this sample peice
    ' of code.
    Public Sub PushDS2SQL()
        Try
            Dim row As DataRow
            For Each row In ExcelSlurp.Tables(0).Rows
                Dim A As String = CStr(row(0))
                Dim B As String = CStr(row(1))
                Dim C As String = CStr(row(2))
                Dim D As Integer = CInt(row(3))
                Dim E As Date = CDate(row(4))
                Dim F As Date = CDate(row(5))
                AddEligEmp(A, B, C, D, E, F)
            Next row
            MessageBox.Show( _
                "Eligible Employees Added", _
                "Success", _
                MessageBoxButtons.OK)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

[keystone_paul]

Edit - never mind I've tried it now and it didn't work.

[techgnome]

Ummmmm check it before you set it?

-tg

[FastEddie]

I just want to be able to handle the null value so that I can do something with it. Anything before the error is thrown.

[keystone_paul]

Can you not just do an IIF and check whether it is Null (or DBNull), ie something like

Dim F as Date = Iif(row(5) = DBNull, nothing, CDate(row(5))

[techgnome]

row(5) = DBNull
s/b
row(5) = DBNull.Value

-tg

[FastEddie]

Dim F As Date = IIf(row(5) = DBNull.Value, Nothing, CDate(row(5))) gives me this error.

Operator '=' is not defined for types 'Object' and 'System.DBNull'.

[keystone_paul]

OK - I cant replicate your code exactly so I'm working a bit in the dark but how about :

Dim F As Date = IIf(IsDBNull(row(5)), Nothing, CDate(row(5)))

[techgnome]

try either:
row(5) is DBNull.Value
or
row(5) is DBNull

-tg

[FastEddie]

I am not sure how to prove it but it appears that the error happens before the IIF takes place so it doesn't fix my problem.

I have stepped trough the code with the debugger sending the output straight to the Debug window and it fills all the rows until it comes to the first column without a date and then it goes right to the Catch part of my Try Catch Block without ever enterting into the immeadiate If part. (now that I think about it I think that proves that the error happens before the IIF takes place.)

Any other ideas?

[techgnome]

wait... so on what line DOES it error on?


UGH.... that's because it's not EVEN erroring on the DATE.... it's on the first one... the STRING.
Conversion from type 'DBNull' to type 'String' is not valid. <-- SEEE? string... not date...

it could be any one of these three:
Dim A As String = CStr(row(0))
Dim B As String = CStr(row(1))
Dim C As String = CStr(row(2))

You'll need to do the same thing with those as you did with the date... except use "" (or string.empty) instead of Nothing (or does Nothing work with strings? I don't know as I usually need string.empty ....aaaaaaannnny ways...)....

-tg

[keystone_paul]

How about commenting out the try.. catch structure so you can see exactly where the error is arising?

Actually thinking about it, it is surely either row(o), row(1) or row(2) that the error is occurring as the message is "conversion to type String is invalid", not Date, not Integer.

EDIT - Techgnome came to the same realisation as me - we're working on the assumption it was a date column that was failing but it isn't!

[techgnome]

we were led astray by the original posting, where he said and I quote:

How can I add a check to the Dim F As Date = CDate(row(5)) to handle a null?

And then we failed to actually read ALL of the text and post.... I know that's a problem with me, I tend to gloss ove3r things, especially when I see code...

-tg

[BukHix]

It is definitely the F column because earlier I did comment it out and it worked. In other words this code:

For Each row In ExcelSlurp.Tables(0).Rows
                Console.WriteLine( _
                CStr(row(0)) & " " & _
                CStr(row(1)) & " " & _
                CStr(row(2)) & " " & _
                CStr(row(3)) & " " & _
                CDate(row(4)) & " ") '& _
                ' CDate(IIf(row(5) Is DBNull.Value, Nothing, CDate(row(5)))))

produces these results, which is Coloumn A, B, C, D, and E (all 100 records are printed).

111 40001 Bob Jones 20 12/29/2008 
222 40002 Asa Hicks 50 12/9/2008 
333 40003 John Ball 50 3/18/2009 
444 30004 Dan Sanks 10 12/20/2003 
555 40005 Sandy You 20 9/15/2008

I also tried filling all the dates that were empty on the spread sheet (as a test) and it worked then too. It is only when I uncomment the "F" coulmn that it crashes with that error. By the way I did just notice that since I added the IIF the error does now say "Conversion from type 'DBNull' to type 'Date' is not valid." I didn't notice that had changed until I read your posts.

NOTE: I also just realized that I used my "home" user name and not my "work" user name when I did my last post. Sorry about the confusion (if any).

[keystone_paul]

The mystery deepens, although its reassuring that the error does say "conversion to type Date" now if it is row 5 that is the culprit.

I would definitely leave the Try..Catch block out for the time being as error handling can obscure the issue.

Can you separate the IIF into a straighforward IF..THEN...ELSE block as below :

      Dim F As Date
      If IsDBNull(row(5)) then
           F = Nothing
      Else
           F = cdate(row(5))
      End If

Then run the code and check which line it breaks on. Presumably this will be F=cdate(row(5)). What does row(5) evaluate to?

[FastEddie]

KP doing it that way allowed the code to run all the way until it bumped into the parameter for the stored procedure. I then got this error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

The line that it crashed on was this:

.Parameters.Add("CDate", SqlDbType.DateTime, 20).Value = ChangeDate

On the first two records the ChangeDate was an actual date but on the numm the date was a tiime like this ChangeDate = #12:00:00 AM#. <--- Which I think is the date equivilent to Nothing.

I think I am going to have to change my stored procedure to accept default values of Null to get this to work because as far as I can tell there is no way to convert a date into a null value. Am I wrong?

[keystone_paul]

Ah OK so its not actually erroring within the code above at all now?

What probably should have said explicitly above is that that code will stop your code from falling over if it gets a null value when setting the variables but you need to consider what will happen when the next bit of the program processes the date.

It may be easier to rather than set the value as nothing, set it as a dummy date like "01/01/1753 12:00:00 AM" and then check for this value and treat it as a special case further down the line when processing the data.

[techgnome]

what you need to do now, is to reverse the logic....

If f is nothing then
    .Parameters.Add("CDate", SqlDbType.DateTime, 20).Value = DBNull
else
    .Parameters.Add("CDate", SqlDbType.DateTime, 20).Value = ChangeDate 
end if

-tg

[JuggaloBrotha]

Just my two cents:
Whenever you're getting data from an xml file, excel file, database, etc... you should use the members in the Convert class. IE use Convert.ToString(Rows(...)) because if a Null is present, the Convert.ToString() returns a String.Empty automatically, no need to check for DBNull.Value.

Same goes for Convert.ToInt32() instead of using CInt().

[FastEddie]

Reversing the logic did the trick. Thanks for hanging in there with me for so long!

[keystone_paul]

Reversing the logic did the trick. Thanks for hanging in there with me for so long!

I'd just be a bit cautious - just because the code runs doesn't mean its now all correct.

If you have anything that manipulates those variables in the course of your code then you need to make sure that it won't fall over when it comes across your "nothing" date.

Personally I wouldn't write nulls into to the database - as you are just at this point importing data from Excel why not take the opportunity to write in either a meaningful default or a dummy value, otherwise every bit of code querying the database is going to have to watch out for nulls, which as has been ably demonstrated on this thread is more complicated than checking for a dummy date.

[techgnome]

because NULL means UNKNOWN.... if there is no date, then the date is unknown... therefore, NULL is appropriate for the database. Null becomes the default "value", and it is meaningful... it means "I dunno".

-tg

[keystone_paul]

because NULL means UNKNOWN.... if there is no date, then the date is unknown... therefore, NULL is appropriate for the database. Null becomes the default "value", and it is meaningful... it means "I dunno".

-tg

This is true, however because a value of Null in the database cannot be treated the same as all the other values in the column (ie dates) it means that every time you query a value from the column you need to write special checks which makes the code more convoluted than it needs to be, however if you can safely select an arbitrary date (ie for example one so far in the past that it is obviously never going to be a real item of data), you can read the data in a much more straightforward manner and have your logic flag up the fact that it is a dummy record.

Its a matter of personal preference, but personally I prefer to elminate that kind of null checking which has shown to be problematic in this thread, by avoiding nulls in the database if at all possible, and during an import routine that is pulling in the data from another source is the ideal place to do that.

[FastEddie]

I am basicaly automating the import of data between one system that only understands csv data, which leaves blanks between the commas, into another that uses SQL Server as the datestore.

The application that uses SQL Server has a report that checks for a ChangeDate Value. If there is one there it is shown on the report if not (meaning it found a null) it defaults to the HireDate. I am kinda stuck with that functionality so that is why the code is set up that way.

[techgnome]

It's only a problem in code... in the database itself, it's easy enough to check for and/or filter on.

If this is a recurring action, you might want to look at using DTS or SSIS - depending on the verison of SQL Server and set it up as a job. Have it check a folder on the server, if it finds a file(s), then run the process to pull it in... when it's done, move the file(s) to an archive folder.

-tg