Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Weird Dataset, SQL Server, VB.NET error

Posted on 2004-09-08
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4

Accepted Solution

natloz earned 2000 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!
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

661 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