Wrong Query Being Generated When Using Parameterized Query In Classic Asp

I am making a database call through the following parametrized query. set recordset = Server.CReateObject('ADODB.Recordset') set cmd1 = Server.CreateObject('ADODB.Command') cmd1.C

Solution 1:

I would do like this:

function commandCreateText(conn, sql)
  dim recordset, cmd1
  set cmd1 = server.createobject("adodb.command")
  set recordset = server.createobject("adodb.recordset")
  set cmd1.activeconnection = conn //which you already have defined
  cmd1.commandtext = sql
  cmd1.commandtype = 1
  set commandCreateText = cmd1
end function

sql = "select * from tbl_catmaster where catname = ? or catname = ? order by catname"
set cmd1 = commandCreateText(conn, sql)
set recordset = cmd1.execute(, array("programmer", "developer"))

if recordset.eof then
  response.write("No records found!")
else
  do until recordset.eof
    response.write("1 record found!")
    recordset.movenext
  loop
end if

Solution 2:

You need to create and append the parameters, not sure about the profiler.

set prm = cmd1.CreateParameter("@prm", 200, 1,200 , "developer")
cmd1.Parameters.Append prm