How to make a parametrized SQL Query on Classic ASP?

Question

Can someone show me the simplest way of perform a parametrized SQL query using Classic ASP in VBscript?

A compilable example would be best.

Answer 1

Use the adodb.command object.

with createobject("adodb.command")
    .activeConnection = application("connectionstring")
    .commandText = "select * from sometable where id=?"
    set rs = .execute( ,array(123))
end with

I would also advise to use a custom db access object instead of using adodb directly. This allows you to build a nicer api, improves testability and add hooks for debuging/logging/profiling. Secondly you can add request scoped transactions with implicit rollback's on errors using the class_terminiate event. Oure db access object offers the following query api

call db.execute("update some_table set column=? where id=?", array(value, id))
set rs = db.fetch_rs("select * from some_table where id=?", array(id))
count = db.fetch_scalar("select count(*) from some_table where column > ?", array(value))

Answer 2

I'm assuming you are referring to a parameterized SQL Query. If this is the case, then the VBScript code would look something like this:

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "connectionstring"
SET cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = adoCon
cmd.CommandType= adCmdStoredProc 
cmd.CommandText = "GetCustomerByFirstName" 

cmd.Parameters.Append cmd.CreateParameter("@FirstName",adVarchar,adParamInput,50,"John")    

Set Rec = cmd.Execute()
While NOT Rec.EOF
  'code to iterate through the recordset
  Rec.MoveNext
End While

UPDATE: You need to include the ADOVBS.inc file for the constants to be recognized.

Here's a link: ADOVBS.inc

Answer 3

Another option to including adovbs.inc is to add a reference to the following type library near the top of your ASP. Supposedly this has better performance than an include:

<!--METADATA TYPE="TypeLib" NAME="ADODB Type Library" UUID="00000205-0000-0010-8000-00AA006D2EA4" FILE="C:\Program Files\Common Files\System\ado\msado15.dll" VERSION="2.5" -->

Here is a list of some type libraries.