Syntax Error on Update SQL?

I don't see it

[txbakers]

I'm getting an Object needed error on the "set SQL=" line below. I'm used to writing in JScript, so the VB syntax might be tripping me up.

I don't see the error, can someone take a look?
Thanks.

dim SQL
set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Driver={MySQL ODBC 3.51 Driver};server=localhost;Database=charms;Option=1;"
set SQL = "update authde set purchcode='" & txn_id & "' where rrn=" & rsNxtLine("rrn")
cmd.Execute SQL
cmd.ActiveConnection.Close
'Response.Write(SQL)

[Krapulator]

Try this:

SQL = "update authde set purchcode='" & txn_id & "' where rrn=" & rsNxtLine("rrn")

set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = "Driver={MySQL ODBC 3.51 Driver};server=localhost;Database=charms;Option=1;"
cmd.CommandText = SQL
cmd.CommandType = 1
cmd.CommandTimeout = 0
cmd.Prepared = true
cmd.Execute()

[Krapulator]

oh and dont forget

dim SQL, cmd

[txbakers]

Thanks - I was trying to "SET" the string variable. I should have known better. I really need to slow down this past-midnight programming.

[korkus2000]

You should look at using SQL parameters instead of concatenating your SQL statement. It will help prevent injection attacks.

[Krapulator]

Good Point Korkus.

It would at least be worth replacing single quotes in the variables (if that hasn't been done earlier in the code).

[txbakers]

Yes, I always do the replace (') with ('').

[korkus2000]

The replace method is a resource hog. You will find that sql parameters really are the way to go.