|
Subject:
|
Pass value from dropdown list to another ASP SQL
|
|
Posted By:
|
Thamil
|
Post Date:
|
8/24/2006 10:29:05 AM
|
Hi,
I am New to ASP. Need your help please.
I am working with Oracle Database and ASP.
I am having problem to pass value from dropdown list to another ASP to SQL statement. Below are codes:
First Page code:
<html>
<head>
<title>test</title>
</head>
<body>
<form name="test" action="patients.asp" method="POST">
Select Ward:
<select name="wardlist">
<option value="Arnwood">Arnwood</option>
<option value="Beacon">Beacon</option>
</select>
<input type="submit" value="Submit">
<input type="Reset" name="Reset">
</form>
</body>
</html
Main Page:
<%
Dim paraval
paraval = Request.Form("wardlist")
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "PIMSP_MHLD","REPORT","REPORT"
Set Session("PIMSP_MHLD_conn") = conn
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn,3,3
sql="SELECT spont.shortname..... WHERE spont.shortname = & paraval &"
%>
....
....
Thanks in Advance
Thamil
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/24/2006 11:24:14 AM
|
Does the variable have a value?
Is there an Oracle error?
Your select is incorrect it should be: SELECT....WHERE spont.shortname='" & paraval &"' WHERE something "
"The one language all programmers understand is profanity."
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/25/2006 8:24:53 AM
|
Hi,
Thanks for your interest.
Yes. Variable gets value from dropdown list.(see my first page,If any error in that coding, please let me know)
And I have changed the SQL statement & paraval & into '" & paraval & "', but still it doesnt work. ASP opens, but no records displayed.
Need your further assistance.
Thanks
Thamil
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/25/2006 8:41:33 AM
|
Write the value of sql out to the screen, copy that query, and execute it inside of Oracle and see if you do have a result set returned; doing this will then at least know tell you if your query is not returning data or if its your code that isnt handling the data correctly.
hth.
"The one language all programmers understand is profanity."
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/25/2006 8:54:10 AM
|
Hi,
I have tested the sql in TOAD, works fine. Returns data Hadrian,etc...
I assigned this value to the above variable in the main ASP. Instead of getting values from Dropdown list from another asp.
But still its not working. ASP doesnt brings any records at all. No error message
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/25/2006 9:10:51 AM
|
ummm, in your original code snippet you populate the value of sql AFTER you have opened your recordset if this is how your code is truly structured, you are passing in an empty string to the record set....
"The one language all programmers understand is profanity."
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/25/2006 9:29:23 AM
|
Changed the coding structure into:
<% dim paraval,conn,rs
paraval = Hadrian
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "PIMSP_MHLD","REPORT","REPORT"
Set Session("PIMSP_MHLD_conn") = conn
sql="SELECT spont.shortname as ward ...and spont.shortname = '" & paraval & "' "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
%>
put sql before record set open. No use. ASP returns only title and THEAD, not any records or record column structure. no error message. Sorry.
Any other ideas!!!
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/25/2006 9:36:43 AM
|
Do you ever even iterate through the recordset or attempt to?
"The one language all programmers understand is profanity."
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/25/2006 9:44:12 AM
|
Hi,
No. This is my first attempt, passing variable to another ASP in SQL statement.
I am learning, I welcome your suggestion.
Thanks
Thamil
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/25/2006 9:54:05 AM
|
Hi,
Additional Info:
The ASP works fine without any parameter value declaration coding(I removed them and checked it)
Please tell me, How can I include the codes for variable declaration into my below asp code to get value from other asp(dropdown) and pass it to sql statement.
Working code ASP:
<%
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "PIMSP_MHLD","REPORT","REPORT"
Set Session("PIMSP_MHLD_conn") = conn
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn,3,3
sql="SELECT spont.shortname..... WHERE spont.shortname = Hadrian"
%>
......
......
Thanks
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/25/2006 10:01:11 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."
|
|
Reply By:
|
Imar
|
Reply Date:
|
8/25/2006 10:17:43 AM
|
quote:
Set Session("PIMSP_MHLD_conn") = conn
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.
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/25/2006 10:28:44 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."
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/29/2006 4:02:25 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><FONT
FACE="Arial" COLOR=#000000 ><CAPTION><FONT FACE="Arial" COLOR=#600000 SIZE=2><B>
Test - Inpatients - Ongoing - passing variable </B></CAPTION>
<THEAD>
<TR >
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial"
COLOR=#ffffff>Ward</FONT></TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial"
COLOR=#ffffff>Pname</FONT></TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial"
COLOR=#ffffff>DOB</FONT></TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial"
COLOR=#ffffff>PID</FONT></TH>
<TH BGCOLOR=#3399B7 BORDERCOLOR=#000000 ><FONT SIZE=2 FACE="Arial"
COLOR=#ffffff>Admit Date</FONT></TH>
</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><B><%=Server.HTMLEncode(rs.Fields("ward").Value)%><BR></FONT></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><B><%=Server.HTMLEncode(rs.Fields("pname").Value)%><BR></FONT></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><B><%=Server.HTMLEncode(rs.Fields("DOB").Value)%><BR></FONT></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><B><%=Server.HTMLEncode(rs.Fields("HOSPITAL_NO").Value)%><BR></FONT></B></TD>
<TD BGCOLOR=#7BB1C6 BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000><B><%=Server.HTMLEncode(rs.Fields("admit_date").Value)%><BR></FONT></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
|
|
Reply By:
|
Imar
|
Reply Date:
|
8/29/2006 6:06:03 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
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/29/2006 6:25:37 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
|
|
Reply By:
|
Imar
|
Reply Date:
|
8/29/2006 6:35:22 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
|
|
Reply By:
|
Thamil
|
Reply Date:
|
8/29/2006 7:10:36 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.
|
|
Reply By:
|
dparsons
|
Reply Date:
|
8/29/2006 7:12:37 AM
|
Glad you got it working.
"The one language all programmers understand is profanity."
|
|
Reply By:
|
rstelma
|
Reply Date:
|
8/29/2006 11:37:53 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.
|
