Pass a variable from a web form to Query in Access 2000

[JohnSmith]

I have a text box on my asp page. Is it possible to pass that value to a query in Access and run the report?

[jpiller]

Hey John,

That's a pretty easy one! The best way to do it is to have the first part of your search string hardcoded into the page and then attach the search criteria to the end of it using a form. So, an example might look something like this:

Page 1:

<form name='Query' method='get'>
Enter your criteria: <input name='criteria' type='text'>
<input type='submit' value='Go'>
</form>

Page 2:
Dim strCriteria
Dim rs

strCriteria = Request.Querystring("Criteria") 'This gets the value from the text box.

'Open connection to database

rs.Open "SELECT * FROM table1 WHERE something = '" & strCriteria & "'", connectionstring

Let me know if you need more help

Jim P.

[JohnSmith]

Thanks for your reply but not what I was looking for. I have an asp page with a text box. When I click the submit button I want to pass that value into the Access database query and display the report from Access into the browser.

I am able to get the text box value into the query but unable to open a report in Access from my asp page.

Here is my code to pass a value from a asp page to a query within Access:

YOUR HELP IS GREATLY APPRECIATED..

Dim cmd, rs, connect
Const adCmdStoredProc = &H0004
Const adParamInput = &H0001
Const adVarChar = 200

Set cmd = Server.CreateObject ("ADODB.Command")
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("courses.mdb") & ";Persist Security Info=False"
cmd.ActiveConnection = connect
cmd.CommandText = "q_Names"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter ("@fname",adVarChar,adParamInput ,10,"John")
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs = cmd.Execute



do while not rs.EOF

Response.Write rs("firstName") & " " & rs("lastName") & "<br>"
rs.MoveNext
loop

'DoCmd.OpenReport
rs.Close
Set rs = nothing
set cmd = nothing

[mendhak]

JPiller's answer is correct.

In the line

cmd.Parameters.Append cmd.CreateParameter ("@fname",adVarChar,adParamInput ,10,"John")

Instead of "John", you would put the variable strCriteria, which in turn you get from the Request.QueryString() line.

[JohnSmith]

I want to open an Access Report with criteria...

[jpiller]

Okay, is the access report saved in the database? If so, check out this link that tells how to view a saved access report from within an ASP page:

http://www.4guysfromrolla.com/webtech/042600-1.shtml

Hope this is more along the lines of what you're looking for.

Jim P.