Solved

DBNull to String not vaild

Posted on 2006-06-23
10
795 Views
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.
0
Comment
Question by:Mr_Fulano
[X]
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
10 Comments
 
LVL 8

Expert Comment

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

example.

reader.getstring(0)

if so I do this.....

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

0
 
LVL 34

Expert Comment

by:Sancler
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
       else
            dc.DefaultValue = "0"
       end if
   next

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

Roger
0
 
LVL 14

Expert Comment

by:ptakja
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)
   
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Mr_Fulano
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 = ""
Else
 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.

0
 
LVL 14

Accepted Solution

by:
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.
0
 
LVL 34

Assisted Solution

by:Sancler
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.

Roger
0
 
LVL 6

Expert Comment

by:manicsquirrel
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."

0
 

Author Comment

by:Mr_Fulano
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!

0
 
LVL 14

Expert Comment

by:ptakja
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")))
0
 

Author Comment

by:Mr_Fulano
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.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 …
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

732 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