Prepared Statement

jmss66 · April 16th, 2012, 04:36 PM

I am trying to modify my existing SQL statement in opening a table with a parameter. I read somewhere that a prepared statment will prevent SQL injection. Below is as far as I was able to go in my research. I am also updating a record. When I run the program I ger an error message:

ADODB.Recordseterror '800a0cb3'Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype

The code is :

Code:

Dim rsUsers
 Set objCmd = Server.CreateObject("ADODB.command")
 set rsUsers = Server.CreateObject("ADODB.Recordset")
 objCmd.ActiveConnection = objConn
 objCmd.CommandType = adCmdText
 objCmd.CommandText = "SELECT * FROM Member WHERE SSN = ?"
 objCmd.Parameters.Append(objCmd.CreateParameter("@SSN", adChar, adParamInput, Len(strSSN), strSSN))
 rsUsers.CursorType = adOpenKeyset
 rsUsers.LockType = adLockOptimistic
 rsUsers.Open = objCmd.Execute()

I am not even sure if my code above is how a prepared statement should look like.
Please anyone, please point me to the right direction or help me with my code above.

disel2010 · April 17th, 2012, 04:41 AM

Hi,

what you show looks about right actually..

Although strictly speaking you don't have a prepared statement yet (only when you add objCmd.Prepared = True you'll get a prepared or precompiled command), that's probably not causing the recordset error..

You probably should look at your connection and check whether your provider supports the cursortype / locktyp.. if it doesn't it will actually run under another one...

jmss66 · April 17th, 2012, 12:17 PM

It works with the old code. It's just that I am redoing the code to add a prepared statement. This is the old code.

Code:

Dim rsUsers
set rsUsers = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Member WHERE SSN = " & strSSN & ";"
rsUsers.Open strSQL, objConn,adOpenKeyset,adLockOptimistic,adCmdText

The locktype worked with the code above. So I don't know why it doesn't work with the revised code.

jmss66 · April 17th, 2012, 12:38 PM

I launched another browser and tried running the code. I am no longer getting the error message and it is now working. I will add objCmd.Prepared = True. My question is which line do I add it in? Does it matter in which line I add to?

I actually added after the line ActiveConnection and looks like it worked.

Thanks for your help.