PDA

View Full Version : Help with displaying Data

Mooks
11-04-2005, 10:07 AM
Hello All,

I have been pulling my hair out trying to work out what is wrong with my code.

I have a SQL database setup, with five columns.

I basically want the user to choose two drop down lists (Col001 and Col003) and the script will scan through the database to find the two matching columns, and give me the output on Col005.

Here is my code

This is Search.asp
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="Connections/suburb.asp" -->



<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_suburb_STRING
Recordset1.Source = "SELECT Suburb FROM dbo.suburb ORDER BY ID"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Recordset2
Dim Recordset2_numRows

Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_suburb_STRING
Recordset2.Source = "SELECT Suburb FROM dbo.suburb ORDER BY ID"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()

Recordset2_numRows = 0
%>

<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>SUBURB DISTANCE SEARCH</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form ACTION="results.asp" METHOD="post" id="frmSubSearch" name="frmStatSearch">
<select name="form1" style="WIDTH: 200px; HEIGHT: 18px">

<%
Response.Write "<option VALUE="&chr(34) &"-----" &chr(34)
Response.Write ">" &"-ALL-"
while not Recordset1.EOF
Response.Write "<option VALUE="&chr(34)& Recordset1("Suburb")&chr(34)
Response.Write ">" & Recordset1("Suburb")
Recordset1.MoveNext
wend
%>
</select>

and

<select name="form2" style="WIDTH: 200px; HEIGHT: 18px">

<%
Response.Write "<option VALUE="&chr(34) &"-----" &chr(34)
Response.Write ">" &"-ALL-"
while not Recordset2.EOF
Response.Write "<option VALUE="&chr(34)& Recordset2("Suburb")&chr(34)
Response.Write ">" & Recordset2("Suburb")
Recordset2.MoveNext
wend
%>
</select>


<p>&nbsp; </p>

<input type="submit" name="Submit" value="Submit">
</form>

<p>&nbsp; </p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>

</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>


This is Results.asp

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<html>
<head>
<title>SUBURB DISTANCE RESULTS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<p>Search Results for
<% Response.Write Request.Form("form1") %>
and
<% Response.Write Request.Form("form2") %>
=
<%Response.Write Request.Form("SELECT Col005 FROM dbo.sub_distmr_vic WHERE Col001 = '" &("form1") &"' AND Col003 = '" &("form2") &"'") %>
</p>
<p>&nbsp;</p>
<p>&nbsp; </p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>


Any ideas;)
Brandoe85
11-04-2005, 05:17 PM
Welcome to codingforums :)
I didn't really look at your code much, but it should be simple enough, fill your drop down lists with the columns, and then on the next page, select your column where the columns match. This seems to work for me:

first page:

<html>
<head>
</head>
<body>
<%
Dim Con, strCon, strSQL, RS, strCol1, strCol2
strCon="your connection"

strSQL = "Select col1, col2 From test"

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open strCon
Set RS = Con.Execute(strSQL)
%>
<form name="drops" action="Find.asp" method="post">
<select name="drop1" >
<option value="">Select column 1</option>
<%
Do Until RS.EOF
%>
<option value="<%= RS("col1")%>"><%= RS("col1") %></option>
<%
RS.Movenext
Loop
%>
</select>
<select name="drop2" >
<option value="">Select column 2</option>
<%
RS.MoveFirst
Do Until RS.EOF
%>
<option value="<%= RS("col2")%>"><%= RS("col2") %></option>
<%
RS.Movenext
Loop
%>
</select>
<input type="submit" value="Find" />
</form>
</body>
</html>


Find.asp

<html>
<head>
</head>
<body>
<%
Dim Con, strCon, strSQL, RS, strCol1, strCol2
strCon="your connection"

strCol1 = Request.Form("drop1")
strCol2 = Request.Form("drop2")

strSQL = "Select col3 From test where col1 = '" & strCol1 & "' and col2 = '" & strCol2 & "'"

Set Con = Server.CreateObject("ADODB.Connection")
Con.Open strCon
Set RS = Con.Execute(strSQL)

Do Until RS.EOF
Response.Write(RS("col3") & "<br>")
'you probably want to set something up when the record isnt found
RS.Movenext
Loop
%>
</body>
</html>
Bullschmidt
11-10-2005, 02:40 PM
I basically want the user to choose two drop down lists (Col001 and Col003) and the script will scan through the database to find the two matching columns, and give me the output on Col005.


Here's how I often generate a SQL statement for something like that:

Classic ASP Design Tips - Search Criteria on Multiple Fields
http://www.bullschmidt.com/devtip-searchcriteria.asp