[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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
  • 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)
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: 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

873 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