Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 24th, 2006, 10:29 AM
Authorized User
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Pass value from dropdown list to another ASP SQL

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
 
Old August 24th, 2006, 11:24 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old August 25th, 2006, 08:24 AM
Authorized User
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 25th, 2006, 08:41 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old August 25th, 2006, 08:54 AM
Authorized User
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 25th, 2006, 09:10 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
 
Old August 25th, 2006, 09:29 AM
Authorized User
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!!!

 
Old August 25th, 2006, 09:36 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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

"The one language all programmers understand is profanity."
 
Old August 25th, 2006, 09:44 AM
Authorized User
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

No. This is my first attempt, passing variable to another ASP in SQL statement.

I am learning, I welcome your suggestion.

Thanks

Thamil

 
Old August 25th, 2006, 09:54 AM
Authorized User
 
Join Date: Aug 2006
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Editable Dropdown list in ASP.NET Anypond BOOK: Wrox's ASP.NET 2.0 Visual Web Developer 2005 Express Edition Starter ISBN: 978-0-7645-8807-5 5 February 17th, 2011 09:43 PM
Pass value from dropdown list to another ASP SQL Thamil BOOK: Professional Crystal Reports for VS.NET 0 August 25th, 2006 04:18 AM
Access SQL Statement to Format a Dropdown List jonesl3 Access 3 November 24th, 2004 10:08 AM
Issue with dropdown list and submit - ASP.NET Montenegro ASP.NET 1.0 and 1.1 Basics 0 March 24th, 2004 04:40 PM
Pass a List/Array to a Stored Procedure in SQL Ser sankar SQL Server 2000 4 November 11th, 2003 05:01 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.