Invalid column name

[flann]

I'm trying to update a sql database field with a string value. I'm getting a wierd error saying "Invalid column name 'NO'". The field that I'm trying to update is just simply the value out of a dropdownlist, and the two values are 'yes' and 'no'. Here is my update statement.

 

       Dim sqlcommand As New SqlCommand
       Dim judgement As String = ddlLegal.SelectedValue
       sqlcommand.Connection = connPayables
       sqlcommand.CommandText = "UPDATE ClientInfo SET OutJudgements = " & judgement & " WHERE LeadID = " & intLeadid
       connPayables.Open()
       sqlcommand.ExecuteNonQuery()
       connPayables.Close()

 

I know that the variable "judgement" is holding the correct value, what could be causeing my error?

[Joe Mamma]

what could be causeing my error?

Not using parameters!

[flann]

Not using parameters!

 

What do you mean?

I use the same type of update statement on other forms, and they work fine.

[Joe Mamma]

What do you mean?

I use the same type of update statement on other forms, and they work fine.

That is just a coincidence. . .

 

Use parameters. . . never build sql!

[Nerseus]

You don't have to use SqlParameter objects, but in your example I definitely would. Your problem, however, is probably because OutJudgements is a string column and you're not putting the value in single quotes. Try this:

' ...
sqlcommand.CommandText = "UPDATE ClientInfo SET OutJudgements = '" & judgement.Replace("'", "''") & "' WHERE LeadID = " & intLeadid
' ...

 

Two things I changed:

1. I put single quotes around the value in judgement (they're embedded in the CommandText string - look next to each side of the double quotes).

2. Added Replace to the judgement variable. This replace will double up the single quotes. Without that, you may allow SQL injection to get in your code. By using parameters you won't have to worry about that.

 

-ner

[Joe Mamma]

Without that' date=' you may allow SQL injection to get in your code. By using parameters you won't have to worry about that.[/quote']Exactly.

Also the code reads cleanly:

 

 

 

 

 

Dim cmd As New SqlCommand("UPDATE ClientInfo SET " & _ 
[indent]"OutJudgements = @judgement where LeadID = @leadId", new SqlConnection(someConnStr))
[/indent]


try 
[indent]

cmd.Parameters.Add(@judgement, ddlLegal.SelectedValue) 

cmd.Parameters.Add(@leadId, intLeadid) 

cmd.Connection.Open() cmd.ExecuteNonQuery()
[/indent]
finally 

[indent]

try [indent]cmd.Connection.Dispose()
[/indent]


finally 
[indent]cmd.Dispose()
[/indent]
end
[/indent]end

BTW, there are alot of bad practices you are employing in your example.