Parameterized query in Classic Asp

Question

My db access code is like following:

set recordset = Server.CReateObject("ADODB.Recordset")
set cmd1  = Server.CreateObject("ADODB.Command")
cmd1.ActiveConnection = Conn //connection object already created
cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd1.CommandType = adCmdText
set prm = cmd1.CreateParameter("@prm", 200, 1,200 , "development")
cmd1.Parameters.Append prm
set recordset = cmd1.Execute

But there is no db hit going. Please help with this. I am using sql server 2005.

Thanks.

Where you say 'connection object already created' - can you post the full code? Are you opening your connection properly? Do you get any error messages, or just an empty recordset? (And are you using On Error Resume Next?) — Dylan Beattie, Oct 04 '11 at 21:50
@DylanBeattie: I know this because when I simply remove the parameter part and "where clause" from the query, it returns the records. I am also using the sql profiler to check it but no hit is going to the db. No error message is showing. — Gaurav Pandey, Oct 04 '11 at 21:52
Is `adCmdText` defined anywhere? You don't reference any other ADO constants by name. — Cheran Shunmugavel, Oct 05 '11 at 03:50
I think that if it is not even hitting the database, might be it is giving the error but it is being suppressed? But it gives the error when I put the parameter name "@prm" in place of "?" in my query.. Is there any way to know what is going on? — Gaurav Pandey, Oct 05 '11 at 06:33
See the answer i posted for you at: http://stackoverflow.com/questions/7659253/wrong-query-being-generated-when-using-parameterized-query-in-classic-asp/7659445#7659445 — MicBehrens, Oct 05 '11 at 09:50
See the answer i posted for you at: http://stackoverflow.com/questions/7659253/wrong-query-being-generated-when-using-parameterized-query-in-classic-asp/7659445#7659445 — MicBehrens, Oct 05 '11 at 09:51

Eduardo Molteni · Accepted Answer · 2019-04-22T01:03:07.820

8

In my code, this is how I get a recordset from a command:

Set rs = server.createobject("ADODB.Recordset")
Set cmd = server.createobject("ADODB.Command")

cmd.ActiveConnection = Conn //connection object already created
cmd.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd.CommandType = adCmdText
cmd.CommandTimeout = 900 

set prm = cmd.CreateParameter("@prm", 200, 1, 200, "development")
cmd.Parameters.Append prm

' Execute the query for readonly
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenForwardOnly, adLockReadOnly

Hope it helps

edited Apr 22 '19 at 01:03

answered Oct 05 '11 at 12:55

Eduardo Molteni

38,786
23
141
206

There is no need to create an extra object variable `prm`, instead just combine the build of the `Parameter` and the `Append()` to the `Parameters` collection. Like `Call cmd.Parameters.Append(cmd.CreateParameter("@prm", adVarChar, adParamInput, 200))`, then set the value using `cmd.Parameters("@prm").Value = "development"`. – user692942 Apr 05 '17 at 11:27
@Lankymart: these are just samples based on the OP code, the important part here is how to open the recordset – Eduardo Molteni Apr 05 '17 at 17:17
Followup for anyone reading - "cmd1" on the "set prm..." line should read "cmd". But I can't make an edit less than 6 characters. – Jacob M. Apr 19 '19 at 14:51

Stephen Quan · Answer 2 · 2022-03-24T21:10:57.717

I like using Parameters.Refresh, i.e.

set recordset = Server.CReateObject("ADODB.Recordset")
set cmd1  = Server.CreateObject("ADODB.Command")
cmd1.ActiveConnection = Conn ' connection object already created
cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"
cmd1.CommandType = adCmdText
cmd1.Prepared = True ' only needed if u plan to reuse this command often
cmd1.Parameters.Refresh
cmd1.Parameters(0).Value = "development"
set recordset = cmd1.Execute

score 2 · Answer 3 · answered Dec 11 '19 at 17:26

If you have a complex criteria using parameters here is an example I had to create based on my requirements

    declare @loc smallint = ? , @dt1 date = ? SET @loc = ISNULL(@loc, 999) 
    SELECT m.* , c.*
    FROM Costs c INNER JOIN MbrData m ON c.SN = m.SN and c.startDT = m.startDT 
    WHERE (m.LocationID = @loc OR @loc = 999) AND (MonthYear = @dt1 OR @dt1 IS NULL) 
    ORDER BY m.LocationID

then in your asp

    cmd.CommandText = strSQL ' the string above
cmd.CommandType = 1 ' adCmdText
cmd.Parameters.Append cmd.CreateParameter("@loc",2,1) 'adSmallInt=2, adParamInput=1
cmd.Parameters("@loc") = rptlocation ' scrubbed location ID
cmd.Parameters.Append cmd.CreateParameter("@dt1",7,1) 'adDate=7, adParamInput=1
cmd.Parameters("@dt1") = scrubbed formatted date
set rst = cmd.Execute

score 1 · Answer 4 · answered Oct 04 '11 at 21:54

1

Looks like you aren't referencing your named parameter correctly in your query.

Try replacing:

cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = ?"

with:

cmd1.CommandText = "SELECT * FROM lbr_catmaster where catname = @prm"

and see if that helps.

answered Oct 04 '11 at 21:54

Dylan Beattie

53,688
35
128
197

I also tried this, but then an error occurs which says "Must declare the scalar variable @prm" – Gaurav Pandey Oct 04 '11 at 21:55
2

@Dylan: that's the syntax in ADO.net, not in classic ADO – Eduardo Molteni Oct 07 '11 at 14:11
@Lankymart: using `@prn` it's and ado.net feature – Eduardo Molteni Apr 05 '17 at 17:15
@EduardoMolteni Actually, both are relevant depending on what `CommandType` is used with the `ADODB.Command` object. `adCmdText` uses `?` and `adCmdStoredProc` uses `@variable` that matches the named parameter using the `CreateParameter()` method. – user692942 Mar 31 '22 at 09:16

score 0 · Answer 5 · answered Oct 04 '11 at 22:06

0

Try leaving off the parameter name:

set prm = cmd1.CreateParameter(, 200, 1,200 , "development")

answered Oct 04 '11 at 22:06

PaulStock

11,053
9
49
52

Parameterized query in Classic Asp

5 Answers5

Linked

Related