How to make a parametrized SQL Query on Classic ASP?

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

A compilable example would be best.

From stackoverflow

• 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

  Pablo Santa Cruz : do I need to include some file to get ASP to recognize adVarchar and adParamInput constants?

  Jose Basilio : Yes. Here's a link to that file http://www.asp101.com/articles/john/adovbs/adovbs.inc.txt

• 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))
  

• 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.

  Abel : +1 excellent suggestion!