Wrong query being generated when using parameterized query in Classic Asp

Question

I am making a database call through the following parametrized query.

set recordset = Server.CReateObject("ADODB.Recordset")
set cmd1  = Server.CreateObject("ADODB.Command")
cmd1.CommandText = "SELECT * FROM tbl_catmaster where (catname =? or catname =?) ORDER BY catname"
cmd1.ActiveConnection = Conn //connection object already created
cmd1.Parameters(0) = "programmer"
cmd1.Parameters(1) = "developer"
set recordset = cmd1.Execute

My problem is that when I see the query in the sql server profiler, it is like :

"Select catname,catname FROM tbl_catmaster"

Please help. I am using sql server 2005.

possible duplicate of [Parameterized query in Classic Asp](http://stackoverflow.com/questions/7654446/parameterized-query-in-classic-asp) — Fionnuala, Oct 05 '11 at 12:59

MicBehrens · Accepted Answer · 2011-10-06T19:48:39.133

0

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

edited Oct 06 '11 at 19:48

answered Oct 05 '11 at 09:47

MicBehrens

1,780
8
34
57

This code snippet is giving error as :Wrong number of arguments or invalid property assignment: 'commandCreateText' – Gaurav Pandey Oct 05 '11 at 09:54
Because i originally was using it as a function :) Putting the function in a page which is included in all pages does so only the 3 lines at the bottom of the code snippet should be repeated :) Code snippet edited... – MicBehrens Oct 05 '11 at 13:43
hey erizias, now the error gone but the query is not executing... could it be the sql server's version problem? – Gaurav Pandey Oct 06 '11 at 16:49
i just edited the answer ... Try it out and see if anything is found :) – MicBehrens Oct 06 '11 at 19:49
hmm... finally... can't tell you how much you have helped me... thanks :) – Gaurav Pandey Oct 06 '11 at 19:59

score 0 · Answer 2 · answered Oct 05 '11 at 12:58

0

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

answered Oct 05 '11 at 12:58

Eduardo Molteni

38,786
23
141
206

Wrong query being generated when using parameterized query in Classic Asp

2 Answers2

Linked