ADO Prepared Statement -> Empty String

[ND]

hi group,

i just found a strange behaviour with ADO.NET and a MySQL Database (I don't
know if it is caused by the database or the connector):

the following prepared statement didn't work when passing an empty string or
"nothing" to "stringVar"

---
cmdSQL.CommandText = "SELECT count(*) FROM trades WHERE
string_field=@string_field)"
cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
---

....any construct like:

---
if stringVar is nothing then
cmdSQL.Parameters.AddWithValue("@string_field", dbnull.value)
else
cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
end if

 

[Armin Zingler]

Am 02.08.2010 16:30, schrieb ND:

hi group,

i just found a strange behaviour with ADO.NET and a MySQL Database (I don't
know if it is caused by the database or the connector):

the following prepared statement didn't work when passing an empty string or
"nothing" to "stringVar"

---
cmdSQL.CommandText = "SELECT count(*) FROM trades WHERE
string_field=@string_field)"
cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
---

...any construct like:

---
if stringVar is nothing then
cmdSQL.Parameters.AddWithValue("@string_field", dbnull.value)
else
cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
end if
---

...didn't also work!!! (that's the point i don't understand) i found several
examples in the internet which said that this should work!
so i found a dirty solution which looks like:

Compare to NULL ("=NULL") is usally not allowed. The comparison is "IS NULL"
or "IS NOT NULL"

If the latter is too dirty, you must solve it at application level:

dim where as string
dim sql = "SELECT count(*) FROM trades WHERE "

if stringVar is nothing then
where = "string_field IS NULL"
else
cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
where = "string_field = @string_field"
end if

cmdSQL.CommandText = sql & where


I don't know MySQL well, but you may also check out the "<=>" operator:
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_equal-to

 

[ND]

Hi Armin,

thank you!

Yes, thats what came into my mind too, in between The problem was not the
ADO but the wrong sql-Syntax...

So i think, using the IF-Construct inside my statement is ok: "SELECT
count(*) FROM trades WHERE IF(@string_field is null,string_field is
null,string_field=@string_field)"

Regards, andi