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)
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Get Top Visible Row of Datagridview 6 32
Get hold of longitude and latitude in iframe string 11 45 help 4 29
VB.Net Tranfer data between to VB.Net apps 8 25
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…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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