IsDBNull (field) doesn't work correctly.

Question

2.09/5 (3 votes)

See more:

I am checking a field for DBNull value using IsDBNull(field), but the statement never gets executed. When I press F11 to execute the line in Debug, it immediately jumps to the code in the DataSet.Designer.vb and stops at the field property statement after the catch e statement (see code below ).

VB

Public Property T3d() As String
    Get
        Try
            Return CType(Me(Me.tableStudentSchedule.T3dColumn),String)
        Catch e As Global.System.InvalidCastException
            Throw New Global.System.Data.StrongTypingException("The value for column 'T3d' in table 'StudentSchedule' is DBNull.", e)
        End Try
    End Get
    Set
        Me(Me.tableStudentSchedule.T3dColumn) = value
    End Set
End Property

The e.message is "Conversion from type 'DBNull' to type 'String' is not valid."
Since this is auto-generated code, I haven't changed it.

I've tried checking the field for dbnull several different ways including using the following function.

VB

Public Function NotNull(Of T)(ByVal Value As T, ByVal DefaultValue As T) As T
   If Value Is Nothing OrElse IsDBNull(Value) Then
     Return DefaultValue
   Else
     Return Value
   End if
End Function

It never gets to the function because it goes directly to the DataSet.Designer.vb and stops at the field property after the catch e statement

This is the code with all of the IsDBNull checks in the same code. None of them work the way I expect them to work.

VB

Do
  Dim schDateRow As TmsDatabaseDataSet.StudentScheduleRow = TmsDatabaseDataSet.StudentSchedule.FindBySchDate(DtCurrent)
  Dim pschDateRow As TmsDatabaseDataSet.StudentScheduleRow = TmsDatabaseDataSet.StudentSchedule.FindBySchDate(DtPrevious)
  schDateRow.Hlights = NotNull((pschDateRow.Hlights), "")
  schDateRow.T1 = NotNull(pschDateRow.T1, "")
  schDateRow.T2 = NotNull(pschDateRow.T2, "")
  schDateRow.T2d = NotNull(pschDateRow.T2d, "")
  schDateRow.T3 = NotNull(pschDateRow.T3, "")

  'Never executes the function.
  'Goes directly to the dataset designer field property
  schDateRow.T3d = NotNull(pschDateRow.T3d, String.Empty)

  'Never executes the if statement
  If IsDBNull(pschDateRow.T3d) Then
     schDateRow.T3d = String.Empty
  Else
     schDateRow.T3d = pschDateRow.T3d
  End If

  'Same as above
  If Not DBNull.Value.Equals(pschDateRow.T3d) Then
     schDateRow.T3d = pschDateRow.T3d
  Else
     schDateRow.T3d = String.Empty
  End If

  'Dies at each of the following lines as above
  schDateRow.T3d = If(Not DBNull.Value.Equals(pschDateRow.T3d), String.Empty, pschDateRow.T3d)
  schDateRow.T3d = If(IsDBNull(pschDateRow.T3d), String.Empty, pschDateRow.T3d)

   DtCurrent = DtPrevious
   DtPrevious = DateAdd(DateInterval.Day, -7, DtPrevious)
Loop While DtPrevious >= DtFrom

I've searched every article I can find to find out why it doesn't work. I check the field for DBNull before using it, but it never gets past the the IsDBNull(field) check.

By the way, I'm using Visual Studio 2013, since 2012 crashes all the time.

One more thing. This data fills a datagridview, including the record with the DBNull, so it fills the DataGridview properly with the DBNull value. I thought of doing the change in the DataGRidView, but this SHOULD work.

Thanks for any help.

Posted 25-Jun-15 4:41am

Andrew Alix

Updated 27-Jun-15 3:59am

v2

Add a Solution

Comments

Michael_Davies 27-Jun-15 12:37pm

Have you tried:

IF pschDateRow.T3d IS DBNull.Value Then
...

ledtech3 27-Jun-15 23:46pm

The property is expecting a string if it is cast to db.null then possibly that is it.
If you want a empty string(cell) then perhaps string.empty would be better.
Also is this code converted from C# to VB.Net ?

Andrew Alix 28-Jun-15 8:24am

Answer to Michael_Davies:
Tried this one. Still ends up in the catch statement of the DataSet designer.

Answer to ledtech3:
Tried string.empty as well. No go. This code is direct vb.net.

2 solutions

Solution 2

FOUND IT!!!!!

When addressing the field which is DBNull.value, as in the above code, I used

VB

If pschDateRow.IsT3dNull Then
    schDateRow.T3d = String.Empty
Else
    schDateRow.T3d = pschDateRow.T3d
End If

This worked perfectly. Found it by accident. Apparently the code generator generates a DBNull check labeled 'Is' + 'field name' + 'Null', which can be used to check the field directly without causing the DataSet.Designer.vb to choke on it.

Thanks for all of the suggestions.

Posted 28-Jun-15 3:05am

Andrew Alix

Comments

ledtech3 28-Jun-15 10:17am

Great, glad you got it.
Now that I'm not so tired.
To explain the original problem, the code generated in the try catch statement was first checking for a invalid cast exception such as string to integer or casting/converting to something that is not supported.
If it never hit that type exception then it would never throw the new exception of the DB.Null.
To me that is a Bug in the Code generator.
Also since it is using "CType" that is why I think the generator is of a "C,C++ or C#" flavor.

Andrew Alix 28-Jun-15 19:06pm

I agree. I would classify that as a bug too. But I never thought about the C# flavor in the Generator before. The whole debugger is probably written in one type of C anyway. It seems a stupid check for a string value when it might be DBNull. Perhaps that's why they included the ISNull code in the DataSet.Designer.vb to get around the problem.

By the way, Once I found this solution, I did a little more experimentation and found another method that worked as well, better in my case.

I was able to check every field by getting the number of fields in the row, and then using the following:

For i = 1 to nCols - 1 'Don't want to change the ID field (0)
If pschDateRow.IsNull(i) then
schDateRow.Item(i) = String.Empty
Else
schDateRow.Item(i) = pschDateRow.Item(i)
End If
Next

Just be careful that you check the Type Of (pschDateRow.Item(i)) so you don't get another error with String.Empty.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NewPast · Accepted Answer · 2015-06-27T21:12:00

Solution 1

check
Me.tableStudentSchedule.T3IsNull

then try

VB

Public Function NotNull(ByVal Value As Object, ByVal DefaultValue As T) As T
   If Value Is Nothing OrElse Value is DBNull.Value Then
     Return DefaultValue
   Else
     Return Value
   End if
End Function

Posted 27-Jun-15 21:12pm

NewPast

Updated 27-Jun-15 21:15pm

v4

Comments

Andrew Alix 28-Jun-15 8:07am

Tried that one too. Doesn't get to execute. Program trashes at the call and I end up at the catch statement in the DataSet.Designer.vb as in above.

Could the problem be in the first two statements of the loop? I'm addressing the datarow and then each column in it directly.

Andrew Alix 28-Jun-15 19:12pm

Missed your first line, Me.TableStudentSchedule.T3IsNull check, since it wasn't in a code. That worked. I found it by accident before I saw your solution (see below comments and see another way to check it in that comment)
Thanks a bunch for the help.

NewPast 29-Jun-15 9:27am

Thank you