Weird Dataset, SQL Server, VB.NET error


I have a windows app, written in VB.NET, using VS.NET 2003, which, in part accesses a SQL Server 7.0 database, using some datasets.

Go along fine, load data, display data, change data, process data, delete data,  . . . great.

Then "out of the blue" start getting DBNull error message. Exact text:

"An unhandled exception of the type 'System.Data.StrongTypingException' occurred in MyApp.exe
Additional information: Cannot get value becuase it is DBNull"

A. Yea so? For the column/field to which it breaks upon, the DB allows Null values . . . so? it is Null?, it is perfectly OK. Why throw an error?
B. Yes, dataSet.EnforceConstraints = False

I used dataset wizard to create the dataset, xml (which btw has <xs:element name="fieldname" type="xs:int" minOccurs="0" />)

I really do not understand why it is throwing the error in the first place, as the database allows a null for the column (and the column is not part of the table(s) key).

What is really driving me batty is that it worked before, with same data (that is some null values in the column now causing trouble).  It is just that all of the sudden it starts throwing this stupid error.

Is building/rebuilding form/solution/project doing something behind the scenes?  Is it somehow re-running/running "Run Custom Tool" on the dataset?  Is it the bindgs getting thrown off.

As a lot of the underlying code is system/VS generated I am not sure as to where or what to begin hunting down cause of the problem (i.e. why it works then all of the sudden stops) or why/where to fix code so it does not give the stupid DBNull error.

Any help appreciated.


btw am getting the error in mydataset.vb code at/when it Gets:

Public Property FieldName As Integer
                    Return CType(Me(Me.tableMaterialColor.FieldNameColumn),Integer)
                Catch e As InvalidCastException
                    Throw New StrongTypingException("Cannot get value because it is DBNull.", e)
                End Try
            End Get
                Me(Me.tableMaterialColor.FrequencyColumn) = value
            End Set
End Property

I understadn the above code.  Yes, for some rows it is null.  But, as I said above, it did not throw the error before and poof it is throwing the error.

There are other columns set up exactly the same (except type might be different, e.g. string, or decimal, etc.) and they do not throw the error, even though some rows/values are also DBNull.

I could, and did go in, add code and catch whether it is Null before it throws the error/skip the error, but still leaves unanswered why it started doing this in the first place . . . does one have to go in a periodically/randomly add code, if/when VB/VS/SQL decides it just feels like it? :)
Who is Participating?

Improve company productivity with a Business Account.Sign Up

natlozConnect With a Mentor Commented:
When retrieving code from a need to check for DBNull or you will get a crash...I do this when populating forms from a dataset for every value that CAN be NULL in the database...

If Not IsDBNull(drv("varCompanyCode")) Then
      txtCompanyCode.Text = CStr(drv("varCompanyCode"))
      txtCompanyCode.text = ""
End If

tlfeetAuthor Commented:
Hi NatLoz,

Thanks for advice.  Unfortunately, it is not much help.

Tried what you suggested (see code below) still get same error.

In MyClass/Form:

100      If Not (IsDBNull(dsMyDataSet.MyTable(Me.BindingContext(dsMyDataSet, "MyTable").Position).FieldNumber1)) Then
101            UseField1 = dsMyDataSet.MyTable(Me.BindingContext(dsMyDataSet, "MyTable").Position).FieldNumber1
102      End If
104      If Not (IsDBNull(dsMyDataSet.MyTable(Me.BindingContext(dsMyDataSet, "MyTable").Position).FieldNumber2)) Then
105            UseField2 = dsMyDataSet.MyTable(Me.BindingContext(dsMyDataSet, "MyTable").Position).FieldNumber2()
106      End If

in mydataset.vb code:

Public Property FieldNumber1 As String
                    Return CType(Me(Me.tableMyTable.FieldNumber1Column),String)
                Catch e As InvalidCastException
                    Throw New StrongTypingException("Cannot get value because it is DBNull.", e)
                End Try
      End Get
                Me(Me.tableMyTable.FieldNumber1Column) = value
            End Set
End Property
Public Property FieldNumber2 As String
                    Return CType(Me(Me.tableMyTable.FieldNumber2Column), String)
                Catch e As InvalidCastException
                    Throw New StrongTypingException("Cannot get value because it is DBNull.", e)
                End Try
        End Get
              Set(ByVal Value As String)
              Me(Me.tableMyTable.FieldNumber2Column) = Value
        End Set
End Property

As you can see, other than names both fields are exactly alike.
In the database some rows have Null values for both fields, some rows one is null, the other not, vice versa, some rows have values for both.

It bombs out on the FieldNumber2, only.
What is even weirder is it bombs out when it gets to line #104, that is the "If Not( IsDBNull . . ." is where it bombs out, in that it then calls the Get part of the FieldNumber2 Property, which of course then throws the Null error message.

I am missing something small here, some auto-generated code, somewhere in the dataset has changed, gone wacky on me . . . I think.
Hmmm...I am at a loss...sorry!
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

tlfeetAuthor Commented:
Hi Natloz,

I was wrong & right.
You wrong & right.

I playing around with some code & data, noticed a couple of things.

1. I was wrong. I said FieldNumber1 also had Null data, but yet, program was not bombing out when it got to such data, on FieldNumber1, but was bombing on FieldNumber2 when #2 had Null data.

In fact, it also bombed out on FieldNumber1 Null data - it was just that yesterday when testing, I did not get to records that had Null data in #1.  Tried some more records and bomb.

... at least behaving consistently.

2. You suggested syntax of "Not IsDBNull(drv("varCompanyCode")) " to check Null or not.

I do have a question, what is "drv" ?

As I mentioned the IsDBNull(Field) construct cuased the bomb out as the reference to the Field caused program to use the Get of the Field Property statement in the dataset, which of course causes the DBNull error to be thrown.

Anyway digging around in the dataset.vb code, I noticed it had some Functions named, "IsFieldNameNull" one for each field.

Like this:

Public Function IsFieldNameNull() as Boolean
     Return Me.IsNull(Me.tableName.FieldNameColumn)
End Function

So, rewrote code to reference the Function(s) in the dataset.

So now have, this syntax:

If Not dsDataRow.IsFieldNameNull Then

as opposed to IsDBNull(FieldName) construct.

This works fine.

Now that I see I have to check for Null values before using the values, have a question, why?

I thought the whole dataset, sqladapter, etc. construct was suppose to handle that sort of thing (behind the scenes).

I guess not, and can see why not, but that is why I had my confusion, thinking along the lines of "well, db set up to allow nulls, connecting using dataset, sqladapter, etc. etc., and yet it is throwing Null errors"

If it were a Key field/column or db was set up to not allow nulls, then I could see there would be an issue.  Just did not see why I needed to write code to check for DBNull and such.

Now, trying to figure out if I should manually re-write dataset so as to handle Nulls at that level rather than interspersing my code with all kinds of checks for null in the many places I reference the fields.

Obviously less code to write if I handle it at the dataset.vb level.

But might run into issues down the road (e.g. someone, at some point re-works the db) or
will VS and its auto-generate functions keep overwriting my changes to dataset.vb?

Thanks Natloz this has been a help.
From what I understand...the datasets can handle NULLs, it is when you want to bring them to your form or code that you have to do the checks. It seems pretty common from all that I have read. I prefer to check for NULLs in code myself just in case one gets through...I think it is good practice!
drv is a code I use them to loop through datasets to retrieve specific values to populate a form from a dataset

'create and fill the Data Adapter
                Dim oDa As New SqlDataAdapter(oComm) 'SQL Data Adapter object
                oDa.Fill(oDs, "spGetSpecificCompanyBranch")

                'Associate the dataview
                dvInfo.Table = oDs.Tables("spGetSpecificCompanyBranch")

                Dim drv As DataRowView 'Data Row View object to query DataView object

                For Each drv In dvInfo
                    If Not IsDBNull(drv("varAddress")) Then
                        txtAddress.Text = CStr(drv("varAddress"))
                    End If

                    If Not IsDBNull(drv("varCity")) Then
                        txtCity.Text = CStr(drv("varCity"))
                    End If

                    If Not IsDBNull(drv("varProvince")) Then
                        txtProvince.Text = CStr(drv("varProvince"))
                    End If

                    If Not IsDBNull(drv("varPostalCode")) Then
                        txtPostalCode.Text = CStr(drv("varPostalCode"))
                    End If

                    If Not IsDBNull(drv("varPhone")) Then
                        txtPhone.Text = CStr(drv("varPhone"))
                    End If

                    If Not IsDBNull(drv("varFax")) Then
                        txtFax.Text = CStr(drv("varFax"))
                    End If
My rule of thumb is...if a column can contain a NULL...check for a NULL
tlfeetAuthor Commented:

OK datarowview, just did not see "drv"

Thanks, tlfeet. This helped solve a problem I've been wrestling with for hours!

If Not dsDataRow.IsFieldNameNull Then...

It works!

tlfeetAuthor Commented:
Glad it helps John.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.