quick question on UPDATE query

[vujjeni]

If i want to set a text value in the database to null how should I pass that in the UPDATE Statement ?
I tried these and didn't work --

Update val = ' '
Update val = NULL
Update val as NULL
Update val = "' '"

This has been bugging me for the past few minutes so I appreciate if any one can show me the way. Thanks

[Lunatic3]

DBnull.Value

[vujjeni]

DOES'T seem to work...
Is this how to do it ?

set DBNull.columnName

[Edneeis]

Try UPDATE val IS NULL

[vujjeni]

Nope...IS NULL doesn't work either


I guess I will use
Set Val = ' '

then trim the space out when I load the table values into my program.
Thanks

[EyeTalion]

here's my Update query...txtVendor.Text can have a NULL and it gets updated fine.

sqlcmd = "update pcms.pcms_pcard_transaction_header set vendor_name=?, transaction_amt=?, sales_tax_amt=?, updated_by=?,updated_date=? where trans_seq_no=" & sFirstCell
            Dim objCommand As New OleDbCommand(sqlcmd, objCon)
            objCommand.Parameters.Add("@vendor_name", System.Data.OleDb.OleDbType.Char, 42).Value = txtVendor.Text

then when I go to populate my textboxes on my form, I do a NULL check...

If (objDR("vendor_name")) Is DBNull.Value Then
   txtVendor.Text = String.Empty
  Else : txtVendor.Text = (objDR("vendor_name"))
 End If

HTH....

[Sheppe]

You could put in a Chr(0) as the value. Chr(0) returns a null equivelant.

[Tal McMahon]

isn't it

myparam=nothing

thats what I use

tal

[Pirate]

Originally posted by vujjeni
DOES'T seem to work...
Is this how to do it ?
set DBNull.columnName

Set keyword is no longer used in VB.NET . A workaround is to set the field in the database itself to accept null values . This works well all the time .