sql UPDATE table

hi i need help here

i keep getting this error : Data type mismatch in criteria expression.

i try to update a table wich one of the fields has to be blank (trying to delete information) Here's how the code goes:

sql="UPDATE members SET "
sql = sql & "MbrNick = '" & wMbrNick & "',"
sql = sql & "MbrFirst = '" & wMbrFirst & "',"
sql = sql & "MbrLast = '" & wMbrLast & "',"
sql = sql & "MbrOpt = ''," <---- (this is ' ' , " )
sql = sql & " WHERE MbrNb=" & wMbrNb
conn.Execute sql
[...]

i've tried MbrOpt = '3'," it works. but if i leave it blank it doesn't work... how could i delete what is in MbrOpt plz?

thanks !
imust

Why not just set it to a space and make sure the db allows null for that field, or simply don't include it in your update statement

Failing that why not just delete it altogether from your database?


M3ckon

thanx m3ckon but it doesn't work. When i put the value " " in a text field it's fine. But for numeric fields, i can'T do this. Actually, there is a value in mbrOpt. (in my asp web page) The member now wants to delete the value on this fields --> leave it blank...

well you gave me an idea.. I'll set all numeric fields with the default value "0". Then on the html form, show textbox values that doesn't contains "0" otherwise let the textbox blank. What a d**n waste of time coding but... I have no choice... or simply find a way to delete what's in numeric fields....

if someone knows plz tell!

imust

If you allow nulls on that field, couldn't you just set it to NULL?
It's a numeric field and it shouldn't accept a string such as '' or even ' '.

Try this:

sql="UPDATE members SET "
sql = sql & "MbrNick = '" & wMbrNick & "', "
sql = sql & "MbrFirst = '" & wMbrFirst & "', "
sql = sql & "MbrLast = '" & wMbrLast & "', "
sql = sql & "MbrOpt = NULL "
sql = sql & "WHERE MbrNb=" & wMbrNb