(Part 1 is here)
Previously, I provided a simple example of using parameterized queries in classic ASP; however, that sample lacked a few things such as explicit typing for the parameters. It also created a read-only ADODB.RecordSet which, obviously, isn't one-size-fits-all.
In the last installment, we had worked up this code to do our query:
Set objConnection = Server.CreateObject("ADODB.Connection") objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _ & "Initial Catalog=website;User Id=user;Password=password;" _ & "Connect Timeout=15;Network Library=dbmssocn;" strSql = "SELECT name, info FROM [companies] WHERE name = ?;" set objCommand = Server.CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection objCommand.CommandText = strSql objCommand.Parameters(0).value = strSearch Set objSearchResults = objCommand.Execute()
As I noted then, this code has a minor performance issue because ADODB is going to have to made a round-trip to SQL to figure out the parameter type before it can execute the query. We can fix this and do input validation by explicitly typing our parameters like this:
Set objConnection = Server.CreateObject("ADODB.Connection") objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _ & "Initial Catalog=website;User Id=user;Password=password;" _ & "Connect Timeout=15;Network Library=dbmssocn;" strSql = "SELECT name, info FROM [companies] WHERE name = ?;" set objCommand = Server.CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection objCommand.CommandText = strSql set objParameter = objCommand.CreateParameter("search", adVarChar, adParamInput, 20) objCommand.Parameters.Append objParameter obParameter.value = strSearch Set objSearchResults = objCommand.Execute()
Here, we are creating an explicit parameter with a type of adVarChar (ie, it's a string) that is an input parameter with a maximum length of 20. We append the parameter to our ADODB.Command object and set the parameter's value to the search string we want in our command. More info about ADODB.Parameter objects is here, more info about the possible types is here.
We may want to be able to write to the ADODB.RecordSet that we create; however, the code above won't work for that because it creates a recordset with the default parameters (Set objSearchResults = objCommand.Execute()). If we want to be able to update the recordset, we have to create it with explicit parameters:
Set objConnection = Server.CreateObject("ADODB.Connection") objConnection.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" _ & "Initial Catalog=website;User Id=user;Password=password;" _ & "Connect Timeout=15;Network Library=dbmssocn;" strSql = "SELECT name, info FROM [companies] WHERE name = ?;" set objCommand = Server.CreateObject("ADODB.Command") objCommand.ActiveConnection = objConnection objCommand.CommandText = strSql set objParameter = objCommand.CreateParameter("search", adVarChar, adParamInput, 20) objCommand.Parameters.Append objParameter obParameter.value = strSearch Set objSearchResults = Server.CreateObject("ADODB.RecordSet") objSearchResults.Open objCommand,null,adOpenDynamic,adLockOptimistic
Now, we are explicitly providing parameters to indicate that we want a dynamic cursor (adOpenDynamic) and that we want optimistic locking (adLockOptimistic). This creates a recordset that can be updated via the RecordSet.Update method (http://msdn.microsoft.com/en-us/library/ms676529(VS.85).aspx).