Error with NULL using EXECUTE SCALAR

I keep getting an error message when I try to place the result of an execute scalar command into a text box. The msg is:"conversion from 'DBNull' to type 'String' is not valid"

The code I am using is:

Dim con as OLEDBConnection

con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:\caps.mdb")

Dim cmd As OLEDBCommand

cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue

texBox1.Text = cmd.ExecuteScalar

The code works fine as long as there is a value for the Product. However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this?

Chas28

You need to first check the returning value of the query that if it is null then you don't need to set the text box text because its text property require an empty or non empty string not the Null value.

Try this:

if not cmd.ExecuteScalar =DBNull.Value then

texBox1.Text = cmd.ExecuteScalar

End If

Don't forget to click "Mark as Answer" on the post that helped you.
This earns you a point.

Zeeshan Malik
http://zeemalik.wordpress.com

|||

Sorry, but "if not cmd.ExecuteScalar =DBNull.Value" does not work. I get the error message :

'= is not defined for system.data.OLEDbCommand' and 'system.DBNull''

but thanks for trying.

Chas28

|||

ok try this:

Dim returnValue as String

returnValue = cmd.ExecuteScalar

if not ( returnValue =DBNull.Value OR returnValue =Nothing)then

texBox1.Text = returnValue

End If

Don't forget to click "Mark as Answer" on the post that helped you.
This earns you a point.

Zeeshan Malik
http://zeemalik.wordpress.com