sql UPDATE statement

[jtm235]

In a SQL UPDATE statement, how to i "set" a field to empty? (because it was occupied before).

i get a syntax error (missing operator) that points to my sql string

this is the update command i am using but its not working...

"UPDATE trips set billedamount = '', billstatus = 'unbilled' WHERE tripid = " & DataGridView1.CurrentRow.Cells("tripid").Value.ToString & ""

as you can see, all i want to do is make the billstatus field = "unbilled"
and make the "billedamount" field = nothing(empty/null/whatever)

any help is appreciated

[HowTo]

Though i am not sure but you may try out this:

UPDATE trips set billedamount = @billedamount, billstatus = @billstatus WHERE tripid = " & DataGridView1.CurrentRow.Cells("tripid").Value.ToString & "
cmd.parameters.AddwithValue("@billedamount",textbox1.text)
cmd.parameters.AddwithValue("@billstatus",textbox2.text)
cmd.executenonquery

then leave the textbox1 empty..............

reply whether it works or not

[techgnome]

Technically you are setting it to empty.... what you've set it to is an empty string. Presumably though, what you are trying to do is set it to NULL.

Based on the previous code sample....

UPDATE trips set billedamount = @billedamount, billstatus = @billstatus WHERE tripid = " & DataGridView1.CurrentRow.Cells("tripid").Value.ToString 

If textbox1.text = "" Then
cmd.parameters.AddwithValue("@billedamount",DBNULL.Value)
Else
cmd.parameters.AddwithValue("@billedamount",textbox1.text)
End If

If textbox2.text = "" then
cmd.parameters.AddwithValue("@billstatus",dbnull.value)
else
cmd.parameters.AddwithValue("@billstatus",textbox2.text)
end if

cmd.executenonquery

-tg