Pass value from dropdown list to another ASP SQL

Thamil · August 24th, 2006, 10:29 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

dparsons · August 24th, 2006, 11:24 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."

Thamil · August 25th, 2006, 08:24 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

dparsons · August 25th, 2006, 08:41 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."

Thamil · August 25th, 2006, 08:54 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

dparsons · August 25th, 2006, 09:10 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."

Thamil · August 25th, 2006, 09:29 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!!!

dparsons · August 25th, 2006, 09:36 AM

Do you ever even iterate through the recordset or attempt to?

"The one language all programmers understand is profanity."

Thamil · August 25th, 2006, 09:44 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

Thamil · August 25th, 2006, 09:54 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