VB.NET Conversion from type 'DBNull' to type 'Decimal' is not valid when field has data

Question

I have been looking for a solution to this problem for a while now and am feeling really stuck; I can find solutions to problems that are similar, but not the same as mine.

I am pulling data from a local MSSQL database using a stored procedure, then send the data to a web service. Every part of the component works well except for a pesky problem will a DBNull being returned from the DataRow field, when I know that the field is not null and has valid data. The database and associated INSERT statements that add to this field are designed to not allow NULL entries. When I debug break the program on or right before the line that throws the error I see that the field has valid data for the current row. If I add a null check to the code (as below) the operation continues as normal:

        Dim dataResultsTable = Me.myViewTableAdapter.GetData(int)

        For Each myDataRow In dataResultsTable.Rows
                        If worker.CancellationPending Then
                            e.Cancel = True
                        Else
                            If myDataRow.Item("syncd") = 0 Then
                               If IsDBNull(myDataRow.Item("bidamount")) Then
                                  _bidAmt = ""
                               Else
    // With out the null check this line throws an error
                                  _bidAmt = myDataRow.Item("bidamount") 
                               End If    

                               If IsDBNull(myDataRow.Item("date_time")) Then
                                  _dateTime = ""
                               Else
    // With out the null check this line throws an error
                                  _dateTime = myDataRow.Item("date_time")
                               End If

// . . . . . . . . 

                               worker.ReportProgress(percentcomplete)
                            End If
                         End If
                      Next

The question here is: Why is the read operation returning DBNull instead of the data in the database? Any and all help here is much appreciated!

EDIT: Just to be clear, the operation does return the proper data, but the data is not being saved into the variable.

Demonstrate how you know that the data is returning non-null values. Unrelated to the question, but you should add an Exit For after the e.Cancel = True — Joel Coehoorn
– Joel Coehoorn, Commented Feb 7, 2012 at 3:10
Thanks @JoelCoehoorn, I missed that one and I am usually pretty good at that kind of thing. — Kingsolmn
– Kingsolmn, Commented Feb 7, 2012 at 4:34
@JoelCoehoorn, When I break execution of the program around the If worker.CancellationPending Then statement, and then expand the value of the dataResultsTable variable I see that those fields that throw an error has values in them. I'm stumped on this one, it should work by all accounts that I can see. (Only come across this situation once before) — Kingsolmn
– Kingsolmn, Commented Feb 7, 2012 at 4:44

John Woo · Accepted Answer · 2012-02-07 03:13:01Z

1

This is only a suggestion. In order not to use IsDBNull, why don't you alter your Stored Procedure by converting default value for Null fields using COALESCE

For example:

SELECT COALESCE(bidamount,''), -- this will convert null value into empty one
       COALESCE(date_time,''), ..... 
FROM .....

as with your code _bidAmt = "" and _dateTime = ""

                    If worker.CancellationPending Then
                        e.Cancel = True
                    Else
                        If myDataRow.Item("syncd") = 0 Then
                              _bidAmt = myDataRow.Item("bidamount")    
                              _dateTime = myDataRow.Item("date_time")
                        End If
                           worker.ReportProgress(percentcomplete)
                        End If
                     End If

answered Feb 7, 2012 at 3:13

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kingsolmn Over a year ago

This is a good approach, but the question of why the result set contains data and not DBNull but the .item method returns DBNull is the question. The data is there, but it is returned as null for some reason. But I am going to put your suggestion in my pocket for later, I come across that situation semi-often. Thank you @john

Kingsolmn Over a year ago

although you didn't exactly answer the original question, I marked your answer as accepted because you had the answer to another related problem I was having before I had it! Thank you.

Chris Dunaway · Accepted Answer · 2012-02-07 15:05:26Z

0

Another suggestion is to use the Field method of the DataRow along with nullable types.

For example, if the column in the database is a decimal, you can use something like this:

decimal? _bidAmt = myDataRow.Field<decimal?>("bidamount");

This way, NULL values in the database will be handled properly.

BTW: I hope you are not storing dates in your database using strings as your code seems to imply!!

answered Feb 7, 2012 at 15:05

Chris Dunaway

11.2k4 gold badges40 silver badges52 bronze badges

Collectives™ on Stack Overflow

VB.NET Conversion from type 'DBNull' to type 'Decimal' is not valid when field has data

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related