records won"t return using Form Variables

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

Re: records won"t return using Form Variables

Kevin wrote:
> 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")

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%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=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.