Passing parameters in Access DB through ASP3 storedproc

[jasonsalas]

Hi everyone,

Just a quick query...I've been meaning to pass multiple parameter values in a stored procedure ("queries" in Access) with the following syntax:


Dim SQL, objConn, objRS
SQL = "spStory"
Set objConn = Server.CreateObject("ADODB.Connection&quot
objConn.Open "dsn=DSN;uid=USERNAME;pwd=PASSWORD;"

Set objRS = Server.CreateObject("ADODB.Recordset&quot
objRS.Open SQL,objConn

...where "spStory" takes a number of values from a previous page to generate a detail page based on various criteria, such as AUTHOR, DATE, and TOPIC(s).

I can do this with a single parameters, using the following:
SQL = "spStory '" & Request.QueryString("ArticleID&quot & "'"

...but I'd like to incorporate more values. If I concatenate more REQUEST.QUERYSTRING statements, the whole thing comes out as one long string, which doesn't produce anything at all.

How are parameters delimited in this fashion? Commas? Semi-colons?

Thanks!

 

[culshaja]

This is a different way to do what you want but it allows passing of parameters into a query:

Dim cmSelect
Dim rsResults

set cmSelect = Server.CreateObject("ADODB.Command")
set cmSelect.ActiveConnection = objConn
cmSelect.CommandType = adCmdStoredProc 'might be adCmdText
cmSelect.CommandText = "SELECT x FROM y WHERE z = ?"
cmSelect.Parameters.Append cmSelect.CreateParameter("@vY", adChar, adParamInput, 4, CStr(Request.QueryString("y"))
set rsResults = cmSelect.Execute

This technique of using ? as parameter placeholders works in INSERT, UPDATE, DELETE, and SELECT statements. You just have to remember to append the parameters in the order that they are to be used, and do not set the option on the command obkect for named parameters.

Hope this might help.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305