DBNull to String not vaild

Posted on 2006-06-23
Last Modified: 2008-02-01
This question is worth 250 points and the points will be awarded to the individual that provides the best solution. Thanks!

Hi, I have a table that has many fields. Some fields get populated when a User adds data to the table and some don't, therefore, I end up with fields that have no data in them or DBNulls. When I try to read from those fields and insert the data into string variables, I get an error and my program stops. Is there a way to somehow globally turn the DBNull values off so they just load empty fields to the Form? The only way I found is to go to the table and load   ""   into the fields and then it accepts the value as an empty field (or sting), but there has to be a better global solution. Remember, I don't want to write code for each value I get from the table, but rather a more global solution to ignore DBNulls.
Question by:Mr_Fulano
  • 3
  • 3
  • 2
  • +2

Expert Comment

ID: 16974288
are u using data reader to grab ur data...



if so I do this.....

If reader.GetString(0) = Convert.DBNull Then
  textbox.text = ""
   textbox.text = reader.getstring(0)
End If

LVL 34

Expert Comment

ID: 16974673
Assuming that you're talking about adding new rows, you could make use of the DataColumn's .DefaultValue property.  Once your datatable exists - either by you creating it (strongly typed or on the fly) or a dataadapter filling it - you can cycle through the columns and set an appropriate default value for each.  Pseudo-code

   for each dc as DataColumn in MyTable.Columns
       if dcDataType is string then
            dc.DefaultValue = ""
       elseIf dcDataType is date then
            dc.DefaultValue = "01/01/2000"
       elseif dcDataType is Boolean then
            dc.DefaultValue = False
            dc.DefaultValue = "0"
       end if

Then any new row will start off with a non-null value in each field/column.

LVL 14

Expert Comment

ID: 16976098
Or you can check at runtime if the value is Null.

TextBox1.Text = IIf(MyDataSet.Tables(0).Item("Column1").Value.IsNull, String.Empty, MyDataSet.Tables(0).Item("Column1").Value)

Author Comment

ID: 16980349
Comments to posts above, thus far:

1). [bman9111] - No, I'm not using data reader. I search the datatable and get the data I need from each row, and put it into a string variable as shown below:

strUserName = m_dtPersonnel.Rows(m_rowPosition_P)("Username")    'Gets the user's login name.
strPassWord = m_dtPersonnel.Rows(m_rowPosition_P)("Password")     'Gets the user's password.

2). [Sancler] - I am not adding new rows, I am search already existing rows.

3). [ptakja] - I need to determine if the field is DBNull when I get the data from the table not just at runtime.

The way I've found to solve this problem is by using an IF statement for *each* of the items I retrieve from the table as shown below, but this is far too cumbersome when you're trying to populate a complete screen with data and you don't know where the DBNull field is going to be in the table. I would have to put a statement like this for each piece of data, which is what I'm trying to avoid. Is there a way to globally turn the DBNull error 'off' and just have it fill the string variable with "".

'This is the way I do it currently.
If dt_MyTable.Rows(rowPosition)("Firstname") Is DBNull.Value Then
   strFirstName = ""
 strFirstName =  dt_MyTable.Rows(rowPosition)("Firstname")
End If

Thanks for your help, but the solutions thus far are not practical for what I need to solve.

LVL 14

Accepted Solution

ptakja earned 250 total points
ID: 16980536
Why not this method then, which condenses the If - Else into one line of code:

strFirstName = IIf(dt_MyTable.Rows(rowPosition)("Firstname") Is DBNull.Value, String.Empty, dt_MyTable.Rows(rowPosition)("Firstname") Is DBNull.Value)

You already have at least one line of code to pull the value from the datatable anyway. This technique only makes the line a little longer.
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

LVL 34

Assisted Solution

Sancler earned 150 total points
ID: 16982529
So far as I know there is no way that, globally, you can turn off the DBNull error.  If that is correct it means that you are, at least for now, going to have to deal with it on a case by case basis.  I can see at least five approaches for that.

One is to code an IIF statement for each field/column into your select query to the database so that, when the data arrives, it is in the form that you want.  A second is to add expression columns containing IIF statements to your datatable and get your variables from those rather than the "raw" columns.  A third would be to use an IIF statement - on the lines suggested by ptakja - each time you get a variable.  A fourth would be to continue to do it as you are at the moment.  A fifth would be to create a function to take a datatable value and return either that value if it is valid or "" if it is DBNull and always fill your variables via that function.

Longer term, if you have the necessary access/permissions and it is possible and would not throw things out so far as other uses of the database are concerned, it might be worth amending the database itself so that all existing Null values are actually replaced in that and all new entries default to non-null values.


Expert Comment

ID: 16988735
On my web forms where I use a GridView I can "turn off" the behavior you are having a problem with.  Again, this is GridView, but from the contextual Grid View Tasks menu I select "Edit Columns".  From the "Selected fields" list I select a column and it displays that column's properties.  One of those properties is "ConvertEmptyStringToNull" (true/false).  If set to False and the user leaves the field blank, it inserts an empty string into the db field thus preventing a problem with Nulls down the road.

I believe all data bound controls inherit this property from the BoundField class:

"BoundField.ConvertEmptyStringToNull Property  
Note: This property is new in the .NET Framework version 2.0.

Gets or sets a value indicating whether empty string values ("") are automatically converted to null values when the data field is updated in the data source."


Author Comment

ID: 16989594
Thanks to all. Although you all provided good feedback I think "ptakja" and "Sancler" gave me what I needed to fix my problem. I am increasing the ponts to 400 and spliting them between ptakja and Sancler 250/150 respectively.

Thanks again!

LVL 14

Expert Comment

ID: 16989628
Just realized that I had a major typo in my post. Here's what it should have said:

strFirstName = IIf(dt_MyTable.Rows(rowPosition)("Firstname") Is DBNull.Value, String.Empty, dt_MyTable.Rows(rowPosition)("Firstname"))

Or, if you have Option Strict enabled, it would look like this (since I think the datatable returns an object not necessarily a string):

strFirstName = IIf(dt_MyTable.Rows(rowPosition)("Firstname") Is DBNull.Value, String.Empty, CStr(dt_MyTable.Rows(rowPosition)("Firstname")))

Author Comment

ID: 16993408
Thanks ptakja, I'm going to make these changes today and if I have any problems, I'll drop you a note. Thanks again.

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String ( Literal, only instead of starting and ending with w…
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 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

17 Experts available now in Live!

Get 1:1 Help Now