Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


DBNull to String not vaild

Posted on 2006-06-23
Medium Priority
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
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
  • 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)
Independent Software Vendors: 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: 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 1000 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 600 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 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