I use the following code to display

JefB · Jun 20, 2003

I use the following code to display data from a SQL2000 database in a spreadsheet as an ".asp" page.

<%@ Language=VBScript %>
<%
'Change HTML header to specify Excel's MIME content type
Response.Buffer = TRUE
Response.ContentType = "application/vnd.ms-excel"
%>
<HTML>

<head>
<title></title>
</head>

<BODY bgcolor="#66FFFF">
Here is the info you requested.<p>
<%
' Create ADO Connection object
dim myConnection
set myConnection = CreateObject("ADODB.Connection&quot

' Open SQL Server Nationwide database...
myConnection.Open "DSN=nationwide2;UID=homer;Password=doh"

' Get a recordset of info from Airports table...
sqlStr = "exec RoomsByDay_Rollup_SP '6/1/03', '6/13/03'"
set rsAirports = myConnection.Execute(sqlStr)
%>

......

Note that the data comes from a stored procedure: "exec RoomsByDay_Rollup_SP '6/1/03', '6/13/03'"

My question is; how do I feed the two arguments to the stored procedure (currently hard-coded as '6/1/03', '6/13/03') as user suppled variables?

In other words, run as SQL code, the stored procedure is a date-range query and I would like to do the same online. Preferably, I'd feed the variables from a separate "request" page.

Thanks

JefB

mwolf00 · Jun 20, 2003

sqlStr = "exec RoomsByDay_Rollup_SP '" & startDate &"', '" & endDate & "'"

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

JefB · Jun 20, 2003

How do I feed the values to '" & startDate &"', '" & endDate & "'?

JefB

onpnt · Jun 20, 2003

IF the user is going to be able to interact with the script you're going to first need a palce for them to do that. A object for them to enter that data as a text input in a form or such. you don't have to submit those values to any other pages or send it a million miles away to be processed in order to get it to the SQL statement.
just set the page up to take a few pararmeters.
1) a hidden field in some way that is cahnged on the user performing some event to submit the values to the server.
2) in the upper portion of the script check for that changed hidden value so you know is that event occured and save yourself from errors in emtpy values in teh SQL statement.
3) validate the values entered
4) perform the task

so a example would be
<html>
<script>
function submitted() {
document.MyForm.hide.value = "submitted";
}
<body>
<%
If Request.Form("hide&quot

= submitted" Then
startDate = Request.Form("startDate&quot

endDate = Request.Form("endDate&quot

' do the SQL insert shown
sqlStr = "exec RoomsByDay_Rollup_SP '" & startDate &"', '" & endDate & "'"
Else
'load the rest of the script as if the user is viewing for the first time

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-2924[/sub]

http://www.onpntwebdesigns.com

JefB · Jun 20, 2003

Either I am missing something or the fact that the data displays as an Excel spreadsheet within the browser window is what is causing this to not work; I get a spreadsheet without any data.

The page that has the spreadsheet is "RoomsByDay.asp". That has the original code.

I also set up a separate page, "RoomsByDayNormal.asp" that uses a Frontpage Database Wizard to display the same data using the above stored procedure with a submit form for the "StartDate" and "EndDate".

Since I have the two arguments (from "RoomsByDayNormal.asp) I'd like to hyperlink to "RoomsByDay.asp" and feed the arguments to the sqlStr line.

Sorry if I am not making myself clear.

JefB

DreXor · Jun 20, 2003

This line of your code is building a text variable containing your SQL statement:
sqlStr = "exec RoomsByDay_Rollup_SP '6/1/03', '6/13/03'"

The Suggestion :
sqlStr = "exec RoomsByDay_Rollup_SP '" & startDate &"', '" & endDate & "'"

is a modification of the assignment to the variable sqlStr you are concatenating the SQL statement with values stored in the variables : startDate and endDate you must specify values for your variables in order to do this, and how to supply values coems from either hard coding or user input or calculation from either / both therein

see onpnt's reply above.

code example on how to hard code the page with the same dates you have :

[blue]' Add these lines, before the variable sqlStr is assigned[/blue]
[red]startDate = "6/1/03"[/red]
[red]endDate = "6/13/03"[/red]
[blue]' this is assingning values to the variables going into the SQL statement below.[/blue]

sqlStr = "exec RoomsByDay_Rollup_SP '" & startDate &"', '" & endDate & "'"

[blue]' this example is hard coded, so if you want user input to be involved, you'll need to add in support for it in your page. See the FAQ's in regards to how to add user input[/blue]

pgrant99 · Sep 30, 2003

Hi JefB,

You are really just building a string, so something like this should work:

' Get a recordset of info from Airports table...
sqlStr = "exec RoomsByDay_Rollup_SP '" & myStartDateVar & "', '" & myEndDateVar & "'"
set rsAirports = myConnection.Execute(sqlStr)

Notice I am still building the single quotes (') into the string around the dates, so that SQL Server won't freak.
HTH,
Phillip

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I use the following code to display

JefB

Programmer

mwolf00

Programmer

JefB

Programmer

onpnt

Programmer

JefB

Programmer

DreXor

Programmer

pgrant99

Programmer

Similar threads

Part and Inventory Search

Sponsor