Handling DBNulls????

[bunchkles]

I am filling the textboxes on a windows form with records from an Access DB when the user selects a state in a combobox. I am using .executescalar. If a record in the DB is Null, I get an exception.

 

How can I tell the project that a DBNull = ""?

[PWNettle]

There are a variety of ways you could deal with this. Some of them would be:

 

1. Use IsNull in whatever SQL you're using to wrap your return value so that it returns empty string instead of null. For example, you might do something like this:

 

SELECT IsNull(fieldname, "") FROM tablename WHERE otherfield=somenum

 

(and I should mention that I don't have MS Access handy so I'm not positive on the MS SQL IsNull function - but I'm sure there is some way to convert a null to an empty string - I *think* that syntax is correct).

 

2. Another way to approach this in code would be to use a variable of type 'object' with your .ExecuteScaler, test that object to see if it's DBNull, and assign to your textbox accordingly, for ex: (C#):

 

//  This example is missing all the code that would actually setup
//    and properly execute the command...it's just a demo!
SqlCommand oCommand = new SqlCommand();

object oTest = oCommand.ExecuteScalar();

if (oTest != DBNull.Value)
{
   textBox1.Text = (string) oTest;
}
else
{
   textBox1.Text = "";

}

 

3. Use a Try/Catch to wrap your .ExcecuteScalar - but abusing Try/Catch isn't usually the best option if you can code to properly handle the expected behavior.

 

Paul

[Folle_Invasato]

DataRow r = ...

 

if( r.IsNull("myColumn") )

r["myColumn"] = "";

[Folle_Invasato]

Ups! i've not read that you are using ExecuteScalar...