Hi,

Tools --> Access2000db, win2k, asp

So, I have a web form that captures 3 pieces of data: Course, SiteName,
StartDate. Here's my code:

' Declare form variables to capture
Dim varCourse, varSiteName, varStartDate

varCourse = Request.Form(Trim("Course"))
varSiteName = Request.Form("SiteName")
varStartDate = Request.Form("StartDate")


I am trying to then use those captured variables as parameters in a query. I
want the 2nd query to be the one, but I can't even get the first query to
work:

1>>>> Set rstRespond = cnnSimple.Execute("SELECT * FROM
[rpt-Training-Roster] WHERE Course='varCourse' ORDER BY LastName")

2>>> Set rstRespond = cnnSimple.Execute("SELECT * FROM [rpt-Training-Roster]
WHERE Course='varCourse' AND SiteName='varSiteName' AND
StartDate='varStartDate' ORDER BY LastName")


[rpt-Training-Roster] -- is a canned query already residing in the Access
db -- using fields from multiple tables

The result doesn't throw an error -- but it comes back telling me no records
can be found -- when I know they are in there.

Any ideas of what I am doing wrong?

thanks

[quoted text, click to view]

The Jet query engine has no idea what your vbscript variables are. it's
running in a totally different process. Do this instead

Dim sSQL, cmd
sSQL = "SELECT * FROM [rpt-Training-Roster] " & _
"WHERE Course= ? ORDER BY LastName"

set cmd=createobject("adodb.command")
cmd.CommandText=sSQL
Set cmd.ActiveConnection=cnnSimple
Set rstRespond = cmd.Execute(,array(varCourse), 1)

Look up the Command's Execute method at msdn.microsoft.com/library for
details.

Here is some info on using Access canned queries:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFTNGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.