asked on

Pass parameters to query and export to excel

I need to have a user enter a start_date and end_date on a form. Click submit and have it pass these two variables to the access query. There may be more variables to pass later from the form. Then return the results as an excel file on the screen.

Here is the query (select_weekly_by_date):
PARAMETERS Start_Date DateTime, End_Date DateTime;
SELECT [Weekly_Report].[Request_Date], [Weekly_Report].[RC_Code], [Weekly_Report].[Last], [Weekly_Report].[First], [Weekly_Report].[SBCUID], [Weekly_Report].[RVP], [Weekly_Report].[VP], [Weekly_Report].[From_City], [Weekly_Report].[To_City], [Weekly_Report].[Departure_Date], [Weekly_Report].[Return_Date], [Weekly_Report].[Hotel_Days], [Weekly_Report].[Car_Rental_Days], [Weekly_Report].[Airfare], [Weekly_Report].[Hotel_Expense], [Weekly_Report].[Car_Rental_Expense], [Weekly_Report].[Meals], [Weekly_Report].[Mileage_Expense], [Weekly_Report].[Other], [Weekly_Report].[Parking], [Weekly_Report].[Total_Expense], [Weekly_Report].[Reason_for_Trip], [Weekly_Report].[Customer_Visited], [Weekly_Report].[Travel_Type], [Weekly_Report].[Request_Status]
FROM Weekly_Report
WHERE ((([Weekly_Report]![Request_Date]) Between [start_date] And [end_date]));

Here is the code I have so far to use another query and pull all records and export to excel:
<%
Dim conn,rs,strsql, dbconn
set conn = server.CreateObject("ADODB.Connection")
set rs = server.CreateObject("ADODB.Recordset")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("travel_request.mdb")

strsql = "SELECT * FROM weekly_report"
rs.Open strsql, conn, adOpenStatic, adLockReadOnly, adCmdText
Response.ContentType = "application/vnd.ms-excel"
%>

I've tried doing something like this:
Cmd.CommandText ="select_weekly_by_date"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("@Start_Date") = Request("Start_Date")
cmd.Parameters("@End_Date") = Request("End_Date")

I get an error though:
ADODB.Command (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

fritz_the_blank

What happens if you try running this in your database query analizer? Does it run okay?

Fritz the Blank

bhinshawnc

ASKER

Runs ok in access

fritz_the_blank

Hmmm,

Usually "Item cannot be found in the collection corresponding to the requested name or ordinal" means that you are addressing a field that does not exist in the recordset or in the table. I don't suppose that this works (Access can be a little funny):

cmd.Parameters("Start_Date") = Request("Start_Date")
cmd.Parameters("End_Date") = Request("End_Date")

Fritz the Blank

bhinshawnc

ASKER

Made your change and now I'm getting this when I load just the asp without calling it from the form - I take this to mean that it's talking with the query and passing nulls for both fields since I'm not calling it from the form?

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver]Invalid datetime format (null)
/travel_requests/Weekly_export2.asp, line 19

Dim conn, cmd, rs
set conn = server.CreateObject("ADODB.Connection")
set cmd = server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("travel_request.mdb")
cmd.ActiveConnection = conn
Cmd.CommandText ="select_weekly_by_date"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("Start_Date") = Request("Start_Date")
cmd.Parameters("End_Date") = Request("End_Date")
rs.Open cmd,,3,3

rs.Open is line 19

fritz_the_blank

Right, in order to see if this works without grabbing values from the form, you will need to try something like:

cmd.Parameters("Start_Date") = #2001-06-09#
cmd.Parameters("End_Date") = #2001-01-01#

If you are using anything other than Access, then you will need to replace the # with '

Fritz the Blank

bhinshawnc

ASKER

I tested as suggested - it's pulling the records. I had tried it before posting but had the date as(#x/x/x#)

How should I setup my form to call the asp and pass it the start_Date and end_Date?

Then display it with
Response.ContentType = "application/vnd.ms-excel"

fritz_the_blank

First, you will need to change your lines back to this:

cmd.Parameters("Start_Date") = Request("Start_Date")
cmd.Parameters("End_Date") = Request("End_Date")

Next, create a form with two fields named Start_Date and End_Date. Try running your page from there and see if you get any errors. I you want to make sure that you are getting data, you can do something like:

Response.Write("<Table>")
do while not RS.EOF
Response.Write("<TR>")
for i=0 to RS.Fields.count-1
if i=0 then
response.write("<TD>" & FormatDateTime(RS(i),2) & "</TD>")
else
response.write("<TD>" & RS(i) & "</TD>")
end if
next
Response.Write("</TR>")
rs.movenext
loop

This should display all of your data, so you will know if you are on the right track. If for some reason this doesn't work, try:

cmd.Parameters("Start_Date") = "#" & Request("Start_Date") & "#"

cmd.Parameters("End_Date") = "#" & Request("End_Date")& "#"

I am calling it quits for tonight, so good luck.

Fritz the Blank

bhinshawnc

ASKER

Here is my form:
<html>
<head>
<title>Weekly Report Export</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<form method=post action="weekly_export2.asp">
<table style="border-style: outset; border-width: 1" cellspacing="1"">
<tr>
<td style="background-color: #000080; text-align: Center; border-style: outset; border-width: 1" colspan="5"><a name="Search"><font style="font-size: 12pt; color: #FFFFFF; font-weight: bold">Weekly
Report Search</font></a></td>
</tr>
<tr>
<td style="background-color: #CCCCCC; color: #000000; border-style: outset; border-width: 1"><font style="font-size: 10pt; color: #000000">Start Date</font></td>
<td style="background-color: #FFFFFF; color: #000000; border-style: inset; border-width: 1"><input type="text" name="Start_Date" maxlength="10" value=""></td>
<td style="background-color: #CCCCCC; color: #000000; border-style: outset; border-width: 1"><font style="font-size: 10pt; color: #000000">End Date</font></td>
<td style="background-color: #FFFFFF; color: #000000; border-style: inset; border-width: 1"><input type="text" name="End_Date" maxlength="10" value="" size=""></td>
<td><input type="submit" value="Search"></td>
<input type="hidden" value="" name="Start_Date">
<input type="hidden" value="" name="End_Date">
</tr>
</table>
</form>

</body>

</html>

Here's the current code:
<%@LANGUAGE="VBSCRIPT"%>
<%OPTION EXPLICIT%>
<%Response.Buffer = True%>



<%
Dim varStart_Date, varEnd_Date
varStart_Date = request.form("Start_Date")
varEnd_Date = request.form("End_Date")

Dim conn, cmd, rs
set conn = server.CreateObject("ADODB.Connection")
set cmd = server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.Mappath("travel_request.mdb")
cmd.ActiveConnection = conn
Cmd.CommandText ="select_weekly_by_date"
cmd.CommandType = adCmdStoredProc
cmd.Parameters("Start_Date") = Request("varStart_Date")
'Request("#"&("Start_Date")&"#")
'Request("#" & "Start_Date" & "#")
'use this format to test #10/1/02#
'Request("Start_Date")
cmd.Parameters("End_Date") = Request("varEnd_Date")
'Request("#"&("End_Date")&"#")
'Request("End_Date")
rs.Open cmd,,3,3

%>

<html>
loop through all the records, etc

</html>

cmd.Parameters("Start_Date") = "#" & Request("Start_Date") & "#"

produces this error

ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/travel_requests/weekly_export2.asp, line 21

cmd.Parameters("Start_Date") = Request("varStart_Date")
gives me this error

Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver]Invalid datetime format (null)
/travel_requests/weekly_export2.asp, line 29

if I use
cmd.Parameters("Start_Date") = #x/x/x# it works so it seems that it's something to do with the Request portion.

I used this to see what the values are once they get to the form:
%
'// grab pass and name from form and hold in variables
varStart_Date = request.form("Start_Date")
varEnd_Date = request.form("End_Date")

'//show the values
response.write "Start = " & varStart_Date & ".<br>"
response.write "End = " & varEnd_Date & "."
%>

and the dates show up like 1/1/1, after entering 1/1/1 on the form

any help on what else to try is greatly appreciated

fritz_the_blank

I didn't think it would be:
cmd.Parameters("Start_Date") = "#" & Request("Start_Date") & "#"
I included that just in case.

The problem with this is:

cmd.Parameters("Start_Date") = Request("varStart_Date")

is that the name of the field on the form is just Start_Date, so it will return a null value--that is why you are crashing.

So, it should be something like:

cmd.Parameters("Start_Date") = Request("Start_Date") or
cmd.Parameters("Start_Date") = Cdate(Request("varStart_Date"))

Fritz the Blank

fritz_the_blank

I generally don't do this the way that you do, so I am a little rusty with the syntax that you are using. It looks like one other problem might be that you are not defining your command parameter before you populate it. Consider the following example:

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 = "qryStudentsOnCourse"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Append cmd.CreateParameter &_
("@CourseID",adVarChar,adParamInput ,10,"C0450")
Set rs = Server.CreateObject ("ADODB.Recordset")
Set rs = cmd.Execute

Perhaps you need to do something similar with your date parameters?

bhinshawnc

ASKER

I was able to get it working by changing the values on the form. Thanks for the help

Ended up being like this
cmd.Parameters("Start_Date") = Request("From_Date")

ASKER CERTIFIED SOLUTION

fritz_the_blank

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial