ASP Prepared Statements
Finally, in the last part of this Prepared Statement series I will show you how to eliminate spaghetti SQL from your ASP applications:
|Set dbCommand = Server.CreateObject("ADODB.Command")|
Set dbCommand.ActiveConnection = dbConnection
dbCommand.CommandType = adCmdText
dbCommand.CommandText = "SELECT COUNT(username) FROM login WHERE username=? and pwd=?"
This code fragment basically sets up the ADODB objects in your ASP application. I assume that the connection object is already setup and is set to dbConnection. The Command object needs an active connection to operate upon, the command type is SQL text (adCmdText), and finally the actual SQL statement is placed in CommandText. Note that here I am using nameless parameters by order of question marks.
In the next fragment I will show you how to setup your SQL parameters in ASP:
|dbCommand.Parameters.Append (dbCommand.CreateParameter("username", adChar, adParamInput, Len(username), username))|
dbCommand.Parameters.Append (dbCommand.CreateParameter("pwd", adChar, adParamInput, Len(pwd), pwd))
Set rs = dbCommand.Execute
You can also use named parameters, just take a look at the C# example a couple of posts below for a similar example. Now here is the part that had me in a frenzy when I first learned of this technique many years ago...
Ideally we really want to setup the Command object and only use the parameters iteratively when inserting or updating several rows in a loop. In order to make all of this work, before the next iterator, we have to remove the old parameters. This can be done with calls to dbCommand.Parameters.Delete(0) as many times as you have parameters. This will essentially delete the parameter at the head of the list, until the list is no more. This is one way to tackle this issue.
So there you have it, writing prepared statements in 4 different programming environments. Check the blog back in a few and I will have some more interesting tips to write about...