I am having a problem working with MS Access query?s. I am trying to give 

a query more than one parameter and have been trying a number of methods 

but failing. I have set up MS Access query to take a number or parameters 

and it work by its self. I can make it work with a single parameter with 

ASP, but how can I adjust the script in order to use numerous parameters? 

i.e. 3 etc. This is the script that I have below:

I currently have it looking at a query called ViewAllJobsQuery using the 

parameter Design. I however want it to use the same ViewAllJobsQuery but 

using numerous parameters such as Pam1, Pam2, Pam3 etc.

I am using Win 2K with MS Access 2K.

Thanks

Graham

-------------------------------------------------------

Dim objRS, objComm, objParam, strCat1, intCounter

Set objComm = Server.CreateObject("ADODB.Command")



objComm.ActiveConnection = strConnect ' fill in the command properties

objComm.CommandText = "ViewAllJobsQuery"

objComm.CommandType = adCmdStoredProc



' now the parameters (There's only one parameter here but I want to send

' many variables, ie. Design, Computing, Education etc to the MS Access

' Query)

Set objParam = _

objComm.CreateParameter("Cat1", adVarChar, adParamInput, 50)

objComm.Parameters.Append objParam



strCat1 = "Design" ' you can change this if you like

objComm.Parameters("Cat1") = strCat1



Set objRS = objComm.Execute 'execute command&generate the recordset



Set objComm = Nothing 'don't need the Command&Parameter objects

Set objParam = Nothing ' ... so we can clean them up

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

: I am having a problem working with MS Access query?s. I am

: trying to give a query more than one parameter and have been

: trying a number of methods but failing

:

<snip>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Can't you do it like this (watch for wrapping):



Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open strConnect



Set objCommand = Server.CreateObject("ADODB.Command")

With objCommand

    .ActiveConnection = objConn

    .CommandType = adCmdStoredProc

    .CommandText = "sp_name_here"

    .Parameters.Append .CreateParameter("param1", advarchar, adParamInput,

50, strVar1)

    .Parameters.Append .CreateParameter("param2", advarchar, adParamInput,

50, strVar2)

    .Parameters.Append .CreateParameter("param3", advarchar, adParamInput,

50, strVar3)

End With



Set objRS = objCommand.Execute



?



Cheers

Ken

suggest that you learn SQL to some extent,



try :



SQLString=3D"SELECT * FROM <table name> WHERE <x>=3D"& pam1 &" AND <y>=3D"&

 pam2

&" AND <z>=3D"& pam3



objComm.execute(SQLString)



where x, y and z are your columns in your table called <table name>



At 17:32 27/05/01 +0000, you wrote:

>I am having a problem working with MS Access query=92s. I am trying to give

>a query more than one parameter and have been trying a number of methods

>but failing. I have set up MS Access query to take a number or parameters

>and it work by its self. I can make it work with a single parameter with

>ASP, but how can I adjust the script in order to use numerous parameters?

>i.e. 3 etc. This is the script that I have below:

>I currently have it looking at a query called ViewAllJobsQuery using the

>parameter Design. I however want it to use the same ViewAllJobsQuery but

>using numerous parameters such as Pam1, Pam2, Pam3 etc.

>I am using Win 2K with MS Access 2K.

>Thanks

>Graham

>-------------------------------------------------------

>Dim objRS, objComm, objParam, strCat1, intCounter

>Set objComm =3D Server.CreateObject("ADODB.Command")

>

>objComm.ActiveConnection =3D strConnect ' fill in the command properties

>objComm.CommandText =3D "ViewAllJobsQuery"

>objComm.CommandType =3D adCmdStoredProc

>

>' now the parameters (There's only one parameter here but I want to send

>' many variables, ie. Design, Computing, Education etc to the MS Access

>' Query)

>Set objParam =3D _

>objComm.CreateParameter("Cat1", adVarChar, adParamInput, 50)

>objComm.Parameters.Append objParam

>

>strCat1 =3D "Design" ' you can change this if you like

>objComm.Parameters("Cat1") =3D strCat1

>

>Set objRS =3D objComm.Execute 'execute command&generate the recordset

>

>Set objComm =3D Nothing 'don't need the Command&Parameter objects

>Set objParam =3D Nothing ' ... so we can clean them up