Pass parameters to query and export to excel

bhinshawnc
bhinshawnc used Ask the Experts™
on
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.



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2005

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

Fritz the Blank

Author

Commented:
Runs ok in access
Top Expert 2005

Commented:
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
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Author

Commented:
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

Top Expert 2005

Commented:
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

Author

Commented:
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"
Top Expert 2005

Commented:
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

Author

Commented:
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%>

<!--#include file="adovbs.inc"-->

<%
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

Top Expert 2005

Commented:
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
Top Expert 2005

Commented:
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?

Author

Commented:
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")
Top Expert 2005
Commented:
I said something like that in about the fourth post down...

Be sure to close out this question,

Fritz the Blank

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial