Invalid column name

[flann]

Im trying to update a sql database field with a string value. Im getting a wierd error saying "Invalid column name NO". The field that Im 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 dont have to use SqlParameter objects, but in your example I definitely would. Your problem, however, is probably because OutJudgements is a string column and youre 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 (theyre 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 wont have to worry about that.

-ner

 

[Joe Mamma]

Without that, you may allow SQL injection to get in your code. By using parameters you wont have to worry about that.

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.