how do you insert a null value in a parameterized SQL statement?

[Desolator144]

Now that I've switched over to parameterized queries, everything is great except along with handling all the special characters that would otherwise disrupt an all text SQL stament, it's handling the word NULL. My code basically does this for every variable used in the entire SQL statement:

If strCallName = "" Then
      strCallName = "NULL"
End If

and for the parameter adding:

m_nonQCMD.Parameters.AddWithValue("@strCallName", strCallName)

and that worked great for my old all text ones because leaving the word NULL out of single quotes would tell it yeah, that's actually null and pass it a true null value. Now with parameterized ones, it's literally passing it the word NULL I tried just leaving the string empty but then when I try to execute the command, it says that the parameter cannot be a zero length string. So how do you give a null value to a a parameterized query and make it actually send that to the database in the statement? Would strCallName = vbNull work or would it still say it's a zero length string?

[ForumAccount]

System.DBNull.Value

[Desolator144]

aha! So that's where they hide it. I assume I can just do:

m_nonQCMD.Parameters.AddWithValue("@strCallName", System.DBNull.Value)

but that would require a LOT of IF statements stating if it's empty, do that otherwise use the string variable. That'd be like 500 IF statements, one on every single parameter add in my entire program. That would kinda suck.

Can I assign System.DBNull.Value to a string variable directly instead and have it work? If not, I could just declare the string variables as objects instead and then assign them to the string value and if it's blank, then assign it System.DBNull.Value and it would work cuz it's just a generic object, right?

[ForumAccount]

System.DBNull.Value can't be converted to a string it will give you a conversion error. Dealing with DBNulls are annoying, but you have no choice really when dealing with databases.

[Desolator144]

oh great, I tested a few out and when it hit one that was blank, it gave me the error "Data type mismatch in criteria expression."

If strBreeder <> "" Then
     m_nonQCMD.Parameters.AddWithValue("@strBreeder", strBreeder)
Else
     m_nonQCMD.Parameters.AddWithValue("@strBreeder", System.DBNull.Value)
End If

This is related to dog agility competitions btw in case you didn't notice from the Call Name and Breeder anyway, is there anything wrong with that code? It kinda sounds like it wants a string value and nothing but a string value. Well if it doesn't want the word NULL or a zero length string or a null object's value, what does it want?

[jmcilhinney]

An empty string is still a string so inserting an empty string is fine. It's inserting Nothing that you can't do, so that should be:

If strBreeder Is Nothing Then
     m_nonQCMD.Parameters.AddWithValue("@strBreeder", DBNull.Value)
Else
     m_nonQCMD.Parameters.AddWithValue("@strBreeder", strBreeder)
End If

There's no need to qualify anything with System because the System namespace is always imported by default.

[Desolator144]

I actually noticed that while cruising through the MSDN library and removed it but obviously it still didn't like it regardless of how I qualified it by the way, some examples in the library in other languages show the exact command with the null being stated that way so apparently it's supposed to work, it just doesn't. Perhaps it's the type of database I'm trying to send it to that's rejecting it. I'm using an Access database and the command object is an OleDB one.
In fact, one of the technet archives has a C# question like this and his answer was to do:

if(string.IsNullOrEmpty(FirstName))
{
cmd.Parameters.AddWithValue("@FirstName", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@FirstName", FirstName);
}

and that's exactly what I did and it doesn't work so logically it must be something in the difference and he was using a MYSQL database I believe. Could that be the reason? And if so, how do I get around it?

EDIT: I just read in some documentation that "This exception is thrown whenever the .NET Framework Data Provider for OLE DB encounters an error generated by the server" so yeah, it pretty much is the Access database whining about it. Anyone know what format/datatype it really wants?

[jmcilhinney]

First up, an empty string is a legitimate value to store in a database from a technical perspective. It's up to you whether you want to do so for your application or not.

As for this error, there's no reason that an error should occur inserting null values into a Text column in Access UNLESS that column specifically disallows nulls. Note that Access Text columns can also disallow empty strings.

[Desolator144]

I noticed that

m_nonQCMD.Parameters.Item("@strBreeder").IsNullable

results in false for some reason. But it said the property was "read or set" so...

m_nonQCMD.Parameters.AddWithValue("@strBreeder", "placeholder")
m_nonQCMD.Parameters.Item("@strBreeder").IsNullable = True
m_nonQCMD.Parameters.Item("@strBreeder").Value = DBNull.Value

that didn't work either lol. Didn't think it would cuz it doesn't seem like a local error inside my program.

I looked at the specifications for the two fields I'm testing this out with and both have Required = no and no default value set either. There's no stated format or validation rule either and they're not indexed. Allow Zero Length is set to No, unfortunately. I dunno what exactly she expects it to be if there's no data for that field, but she sure didn't give a default value either. Is Allow Zero Length set to "No" Access-speak for also don't allow NULL? Cuz I could have sworn my old format SQL statements just used the word NULL in an all text, non-parameterized statement and it worked and left it blank.

[Desolator144]

okay, I've been messing around with this for a while longer and I decided that since it was a data type mismatch error, instead of just using a generic null, i'd use vbNullString. I saw that in two code examples online and though sure, why not try it. So I switched over to:

If strBreeder = "" Then
    strBreeder = vbNullString
End If

now that sounds silly cuz null is null but who knows, maybe Access has different types of nulls. Well apparently that's the case because it didn't give me a data mismatch error. Now as soon as it hit a blank one and tried to execute the statement using a vbNullString. Now it errors out saying:

Parameter @strBreeder has no default value.

So the default action of Access when it's not accepting zero length strings and gets passed a null string is to resort to the default value and since there isn't one, it just throws an error? That's just stupid.
I was reminded that I could just skip those fields completely in the SQL statement if they're blank but I really don't want to tear apart every single SQL statement and build them with like 15 triplets of IF statements. It'd need one on the column name, one on the value, and one on the add parameter statement ugh! That'd be a headache to troubleshoot later cuz it wouldn't even look like an SQL statement anymore. Anyone got any other suggestions that may work? They needed a working version immediately so I sent them one that uses " " (one space character) but that's just awful and I want to fix it ASAP.

[techgnome]

"Is Allow Zero Length set to "No" Access-speak for also don't allow NULL? "
That would be correct... it also means that it CANNOT accept "" (or vbNullString) as a value.... you have to give it SOMEthing. Like a space " " ... some thing, any thing.... just not nothing or empty string.

-tg