Processing from User selection 1

[JanS]

Hi,

I am attempting to do the following:

1. read data from a SQL Server database and display in a listbox ie names of reports

2. allow the users to select an entry in the listbox ie which report they would like to run

3. Determine the item the user chose and send the appropriate stored procedure name to a new page which will prompt for parameters.

I can get items 1 and 3 working okay. The thing I am having trouble with is associating the selection the user makes from the listbox and the value for the stored procedure name that should be submitted to the next page.

I have tried an array, hidden fields etc but I cant get the VBScript to read the value of the chosen item in the listbox so I can read through the array and pass the correct stored procedure name.

I am trying to make the pages dynamic so that when new reports are created, all that has to happen is an entry is added to the table in the SQL database, no code/web pages should need to be changed/modified.

Can anyone give me ideas? I can show you my current code if you need to see.....

thanks
Jan

 

[link9]

Try making the VALUE for the OPTIONs in your SELECT box the name of the stored procedure you want to run -- that should make it alot easier

<SELECT name=reports>
<OPTION VALUE=spReport1>Run Report 1</OPTION>
<OPTION VALUE=spReport2>Run Report 2</OPTION>
</SELECT>

And so on....


Paul Prewett

 

[JanS]

Thanks. That helps a little but how to I pass the VALUE of the OPTION the user has selected to the next form??

The next form needs to know the name of the stored procedure that relates to the report the user has selected. This is so I can prompt for the correct parameter set and dymanically create the SQL statement to run the required report.

Im I trying to do too much? Perhaps VBScript/ASP etc isnt cut out to do what im trying....?????

 

[ozpeppers]

&quot;If you trying to load a table of categories in a select drop menu and pass the selected category on to the next page try this. It suits VBscript and ASP&quot;

&quot;First Page, name is first.asp&quot;

<form name=&quot;form2&quot; action=&quot;second.asp&quot;>
<select name=&quot;searchFor&quot; width=&quot;400&quot;>
<option selected value=&quot;0&quot;></option>

<%

Set objConn= Server.CreateObject(&quot;ADODB.Connection&quot
Set objRS1= Server.CreateObject(&quot;ADODB.Recordset&quot

objConn.Open ( &quot;DSN etc &quot; )
SQL = &quot;SELECT SearchID, ClassType FROM tbl where Classtype is not null order by SearchID&quot;

&quot;SearchID and ClassType are fields in the table, ClassType is what will be displayed in the drop menu&quot;

objRS1.Open SQL, objConn

Do While Not objRS1.EOF

Response.Write &quot;<option value=&quot;&quot;&quot; & objRS1(&quot;SearchID&quot & &quot;&quot;&quot;>&quot; & objRS1(&quot;ClassType&quot & &quot;</option>&quot;

objRS1.MoveNext
Loop
objRS1.Close
Set objRS1 = Nothing
objConn.Close
Set objConn= Nothing

%>

</select>

</form>


&quot;searchFor is the varible being passed&quot;
&quot;Second Page, name is second.asp&quot;

Set objConn1 = Server.CreateObject(&quot;ADODB.Connection&quot
Set objRS1 = Server.CreateObject(&quot;ADODB.Recordset&quot
objConn1.Open ( &quot;DSN etc&quot; )
sqlString = &quot;SELECT reportID, report FROM tblreports WHERE reportID LIKE '&quot; & Request(&quot;searchFor&quot & &quot;'&quot;

objRS1.Open sqlString, objConn1

While not objRS1.EOF ' Execute SQL Statement

Response.write objRS1(“report&quot

Wend

objRS1.Close
Set objRS1 = Nothing
objConn1.Close
Set objConn1= Nothing

 

[link9]

ASP/VBScript is cut out for what you're trying to do -- it's extremely powerful, and with a little creativity, it can do just about anything --

So say you have that first page with the select box like I showed ya --

Then, on the next page, if you want to execute the stored procedure, you can just do this:

<%
dim strProcedure
strProcedure = request.form(&quot;reports&quot 'name of your select
dim con, comObj
set con = server.createObject(&quot;ADODB.Connection&quot
con.open (&quot;DSN=dsnName;UID=uid;PWD=pwd&quot

comObj.ActiveConnection = con
comObj.CommandType = 4 'adCmdStoredProcedure
comObj.CommandText = strProcedure 'from previous page
comObj.Parameters(&quot;@param1&quot = someValue
comObj.Parameters(&quot;@param2&quot = someOtherValue
comObj.Execute

response.write(comObj.Parameters(&quot;@returnValue&quot

set comObj = nothing
set con = nothing
%>

And there you have it -- the more parameters and whatnot you have, the longer you code will be -- but just place any return values in OUTPUT parameters, and then retrieve them after you input all your values and then .Execute.

Easy.
Paul Prewett

 

[JanS]

Thanks for your help Paul - works like a charm