Solved

Weird Dataset, SQL Server, VB.NET error

Posted on 2004-09-08
10
941 Views
Last Modified: 2012-06-27
Hi,

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.

Thanks,
Mike

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

Public Property FieldName As Integer
            Get
                Try
                    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
            Set
                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? :)
0
Comment
Question by:tlfeet
  • 5
  • 4
10 Comments
 
LVL 7

Accepted Solution

by:
natloz earned 500 total points
ID: 12010719
When retrieving code from a Dataset...you 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"))
else
      txtCompanyCode.text = ""
End If

               
0
 

Author Comment

by:tlfeet
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
103
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
      Get
            Try
                    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
            Set
                Me(Me.tableMyTable.FieldNumber1Column) = value
            End Set
End Property
       
Public Property FieldNumber2 As String
      Get
            Try
                    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.
0
 
LVL 7

Expert Comment

by:natloz
ID: 12016951
Hmmm...I am at a loss...sorry!
0
 

Author Comment

by:tlfeet
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
etc.

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.
0
 
LVL 7

Expert Comment

by:natloz
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!
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Expert Comment

by:natloz
ID: 12021476
drv is a Datarowview....in code I use them to loop through datasets to retrieve specific values to populate a form from a dataset table...eg...

'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
                Next
0
 
LVL 7

Expert Comment

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

Author Comment

by:tlfeet
ID: 12021563
Howdy,

OK datarowview, just did not see "drv"

Thanks.
0
 
LVL 6

Expert Comment

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

If Not dsDataRow.IsFieldNameNull Then...

It works!

John
0
 

Author Comment

by:tlfeet
ID: 12208067
Glad it helps John.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now