Weird Dataset, SQL Server, VB.NET error

Posted on 2004-09-08
Last Modified: 2012-06-27

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? :)
Question by:tlfeet
  • 5
  • 4

Accepted Solution

natloz earned 500 total points
ID: 12010719
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


Author Comment

ID: 12011747
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.

Expert Comment

ID: 12016951
Hmmm...I am at a loss...sorry!
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 12019755
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.

Expert Comment

ID: 12021466
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!

Expert Comment

ID: 12021476
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

Expert Comment

ID: 12021490
My rule of thumb is...if a column can contain a NULL...check for a NULL

Author Comment

ID: 12021563

OK datarowview, just did not see "drv"


Expert Comment

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

If Not dsDataRow.IsFieldNameNull Then...

It works!


Author Comment

ID: 12208067
Glad it helps John.

Featured Post

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question