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:

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

maybe anybody has any ideas?

regards, andi

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:
>
> ---
> cmdSQL.CommandText = "SELECT count(*) FROM trades WHERE IF(@string_field is
> null,string_field is null,string_field=@string_field)"
> cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
> ---
>
> maybe anybody has any ideas?

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/...rator_equal-to


--
Armin

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



"Armin Zingler" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> 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:
>>
>> ---
>> cmdSQL.CommandText = "SELECT count(*) FROM trades WHERE IF(@string_field
>> is
>> null,string_field is null,string_field=@string_field)"
>> cmdSQL.Parameters.AddWithValue("@string_field", stringVar)
>> ---
>>
>> maybe anybody has any ideas?
>
> 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/...rator_equal-to
>
>
> --
> Armin