articles:

accessdatasource converting empty strings to null values

I'd read in numerous places that normally people have problems with null values being converted to empty strings, so was very excited when i encountered the opposite problem, no really - extremely excited....

Here is the offending code:

dim a as new accessdatasource
dim x as new customer
...
...
a.InsertParameters.Add("CustEmail", TypeCode.String, x.CustEmail.ToString)
a.InsertParameters.Add("CustName", TypeCode.String, x.CustName.ToString)
...
...
a.InsertCommand = "INSERT INTO [customers] ([CustEmail],[CustName]) VALUES (?,?)"
a.Insert()

Despite passing in what were know to be empty strings for the values customer email and customer name, when checking in the database the fields contained NULL values

I suspected that this might be something to do with the setting of the parameters (well there wasn't much else it could be). A bit of reading around the UI.WebControls.Parameter class brought to my attention the "ConvertEmptyStringToNull" property.....this looks promising.

So the code was change to this:

dim a as new accessdatasource
dim x as new customer
...
...
Dim p As New UI.WebControls.Parameter("CustEmail", TypeCode.String, x.CustEmail.ToString)
p.ConvertEmptyStringToNull = False
a.InsertParameters.Add(p)

p = New UI.WebControls.Parameter("CustName", TypeCode.String, x.CustName.ToString)
p.ConvertEmptyStringToNull = False
a.InsertParameters.Add(p)
...
...
a.InsertCommand = "INSERT INTO [customers] ([CustEmail],[CustName]) VALUES (?,?)"
a.Insert()

And hey presto - empty strings rather than NULL values in the database.

Comments

Posted on Thursday, March 1, 2012 5:30:00 AM GMT by Ernie
Thanks! Solved my problem.

Post a comment

name:
(optional)

email:
(optional)

comment: