Pass value from dropdown list to another ASP SQL

dparsons · August 25th, 2006, 10:01 AM

Oh, my fault. I assumed that you were looping through your recordset somewhere in code. Ok since it seems you are, more or less, a beginner in ASP let me explain something to you.

When you do:

SET rs = Server.CreateObject("ADODB.Recordset")

rs is now an object of type recordset, which means it now has methods and properties associated with it. To answer your question, Data IS being returned to you from Oracle but you never deal with the recordset which is why it appears that the data is not being returned.

(Just an FYI, the code below will show you how to access the data, but .NET has a much easiser way to work with tabluar data ala binding the record set to a datagrid; when the page is displayed the data is automatically rendered as an HTML table without all of the below nonsense.)

I assume you want this data wrote to the screen so here you go.

<table>
<%
...connection code

...recordset code

Do While Not rs.EOF
%>
<tr>
   <td><%=rs("[field1]")%></td>
   <td><%=rs("[field2]")%></td>
   <td><%=rs("[field3]")%></td>
</tr>
<%
rs.MoveNext
Loop
rs.close
conn.close
set rs = Nothing
set conn = Nothing

%>
</table>

So, what this does is say you have 10 rows of data returned from Oracle, the loop will execute 10 times and each time it executes it will add a new HTML Table row to the table. Notice where I do this: <td><%=rs("[field1]")%></td> (replace [field1] with an actual column name returned in your query) this will write the the information in that column associated with the current row into the table cell.

A few points:

The command <%=foo%> is the same as doing a Response.Write()
Make sure you always call rs.MoveNext when moving through a recordset so that the cursor will move to the next row of data.
ALWAYS ALWAYS ALWAYS remember to close your connection and recordset objects and dispose of them by setting them to Nothing (Nothing is the VB equivalant to C/C++/C# null keyword)

HTH.

"The one language all programmers understand is profanity."

Imar · August 25th, 2006, 10:17 AM

Quote:

quote:Set Session("PIMSP_MHLD_conn") = conn

Quote:

quote:ALWAYS ALWAYS ALWAYS remember to close your connection and recordset objects

In addition to that: NEVER NEVER NEVER store a connection object in session state. It's the best way to bring your server to a grinding halt. Instead, use the bankrobber's 3G rule: Go in, Get what you want and Get out. In programmers terms: Create and open a connection on every page that requires it, and close and destroy it on the very same page as well.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
Want to be my colleague? Then check out this post.

dparsons · August 25th, 2006, 10:28 AM

Imar makes an excellent point and to extend it, there is NO reason to store a connection in Session. If, maybe, you meant to store your CONNECTION STRING in that value, I would advise placing it instead in the Global.asa file. You can then reference that value from anywhere within your application doing this: Application("[name]")

So you would only ever have to change the string once (when switiching from a development to production system for example).

"The one language all programmers understand is profanity."

Thamil · August 29th, 2006, 04:02 AM

Dear dparsons,

sorry for my delayed reply. I just come back to work after a long bank holiday weekend.

I have attached full codes for the those ASPs. I have done what you have asked me to do. Unfortunately, Still I got some problem with my ASPs, I am not able to spot the problems. Can you please have a another look for me.

Gateway Page:

<HTML>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<BODY>

<%
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "PIMSP_MHLD","REPORT","REPORT"
    Set Session("PIMSP_MHLD_conn") = conn

    sql="SELECT distinct shortname FROM service_points where shortname is not null order by shortname"

    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
%>

<form action="\\Ncfile1\mhldinformation\ASP\ASP Learning\Inpatients.asp" method=post>

Choose Ward:

<select name="ward" >

<%
On Error Resume Next
rs.MoveFirst
Do While Not rs.EOF
%>

<option value="<%= rs("shortname") %>"> <%= rs("shortname") %> </option>

<%
rs.MoveNext
Loop
%>

</select>
<input type="submit" value="Submit" />

<input type="Reset" value="Reset"/>

</form>

</BODY>
</HTML>

Main Page:

<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<TITLE>passing variable test</TITLE>
</HEAD>
<BODY>
<%
    Dim Paraval
    paraval = Request.Form("ward")

    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "PIMSP_MHLD","REPORT","REPORT"

sql="SELECT spont.shortname as ward,patnt.Surname||' '||patnt.Forename pname,patnt.dttm_of_birth as DOB,patnt.pasid as HOSPITAL_No,TRUNC(sstay.start_dttm) as admit_date FROM pims.patients patnt,pims.service_point_stays sstay,pims.service_points spont WHERE patnt.patnt_refno=sstay.patnt_refno and spont.spont_refno=sstay.spont_refno and sstay.end_dttm is null and spont.name not like 'ZZ%' and spont.shortname = '" & paraval & "' "

Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, Conn,3,3
%>

<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><CAPTION><B>
Test - Inpatients - Ongoing - passing variable </B></CAPTION>
<THEAD>
<TR >
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 >Ward</TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 >Pname</TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 >DOB</TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 >PID</TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 >Admit Date</TH>

</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><B><%=Server.HTMLEncode(rs.Fields("ward").Value)% ><BR></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><B><%=Server.HTMLEncode(rs.Fields("pname").Value) %><BR></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><B><%=Server.HTMLEncode(rs.Fields("DOB").Value)%> <BR></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><B><%=Server.HTMLEncode(rs.Fields("HOSPITAL_NO"). Value)%><BR></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><B><%=Server.HTMLEncode(rs.Fields("admit_date").V alue)%><BR></B></TD>
</TR>
<%
rs.MoveNext
loop
rs.close
conn.close
set rs = Nothing
set conn = Nothing
%>

</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>

The Main Page only returns the table headings, not data results.

Thanks very much for your help.

Thamil

Imar · August 29th, 2006, 06:06 AM

Take a look at this:

<form action="\\Ncfile1\mhldinformation\ASP\ASP Learning\Inpatients.asp" method=post>

It looks like your pages and forms don't go through IIS, but that you request them directly. You need to access them with an http address like this:

<form action="http://YourServerName/Inpatients.asp" method=post>

HtH,

Imar

Thamil · August 29th, 2006, 06:25 AM

Hi,

I have changed it into
<form action="http://nww.ncumbria.nhs.uk/mhld/information/ASP/ASP Learning/Inpatients.asp" method=post>.

But still I am having the same problem that what I had before.

The problem is, this ASP works fine, if I dont call any variable from the gateway(value from dropdown list).

ASP doesnt say any error message at all. Just displays only page heading and table headings, not results...

I welcome your suggestion.

Thanks

Thamil

Imar · August 29th, 2006, 06:35 AM

How do you browse to the page? That is, what do you see in the address bar of your browser?

And how does the HTML source in the browser look like? (Right-click and choose View Source). Do you still see the ASP code?

Imar

Thamil · August 29th, 2006, 07:10 AM

Hi,

Its working now.

The Error was, I made a mistake in form action(I didnt send the form through IIS, you are right Imar)

Thanks very much for your help.

I can give you feedback of A+++++++++++++++++

Excellent, what a helping people you are.

My sincere thanks to dparsons and Imar.

dparsons · August 29th, 2006, 07:12 AM

Glad you got it working.

"The one language all programmers understand is profanity."

rstelma · August 29th, 2006, 11:37 AM

You're probably going to have to go back to a prior suggestions and do:

Response.Write sql
Repsonse.End

Then copy that sql statement from the page and run it in TOAD and see what you get.