Programmers Resource - Displaying Data by User Query

View Full Version : Displaying Data by User Query

kasim

06-24-2003, 04:47 AM

Hi All. I am creating a website linked to a database. Functionality is adding, deleting, viewing, searching but for weeks now I have become stuck on the search page. I have completed add, and display that fully work. The search function consists of 2 pages. One is a html document and the other ASP. In the html document the user specifies the search criteria and then the ASP page is supposed to do the search which isn’t working much to my frustration! Please Please Please cam someone help me with this code. Many Thanks!

search.htm

<title>Search Log</title>
<form action="searchsubmit.asp" method="POST">
<select name="txtField">
<option value="Analyst_Name">Analyst Name</option>
<option value="Task_No">Task No</option>
</select>
<input type="Text" name="txtSearch" size="20"><p>
<input type="Submit" name="Submit" value="Search">
</form>

searchsubmit.asp

<%@ Language="VBScript"%>
<% Option Explicit %>

<%
dim rs
dim con
dim sql

Set con = GetADOConnection()

sql = "SELECT * FROM ServReqLog Where " & request.form("txtField") & " LIKE '%%" & request.form("txtSearch") & "%%';"
set rs = con.Execute(sql)
%>

<HTML>

<HEAD>
<TITLE>Search Results</TITLE>
</HEAD>

<BODY>
<font size="3" face="Arial">Search Results</font><hr>

<%
do while NOT rs.EOF
%>

<% If request.form("txtField")="Analyst_Name" then response.write rs("AnalystName") & "<br>"%>
<% If request.form("txtField")="Task_No" then response.write rs("TaskNo") & "<br>"%>
<hr width="200" align="left">

<%
CurrentRecordset.Movenext
loop
%>

</BODY>
</HTML>

aghill

06-24-2003, 07:20 AM

Try changing the following....

dim rs
Set RS = Server.Createobject("ADODB.Recordset")
dim con
Set con = GetADOConnection()
dim sql
sql = "SELECT * FROM ServReqLog Where " & request.form("txtField") & " LIKE '%%" & request.form("txtSearch") & "%%';"
rs.Open sql, con
rs.movefirst

From what I can see, the sql statement doesn't specify which field in the db to compare the forms to. In the code where you write the response you should have a If then statement, with one condition being nothing returned. Such as below:

If (NOT rs.EOF AND NOT rs.BOF) then
DO while (NOT rs.eof)
Here's where the code to actually write the search results goes
rs.movenext
Loop
Else
Response.Write "There are records which meet your search parameters."
End if

rs.Close
set rs = nothing
con.close
set con = nothing

kasim

06-24-2003, 07:26 AM

A thanks to everyone who replied and tried to help me with the problem but I have solved it myself! AT LAST!!

It was just a matter of removing a few underscores and changing request.form to request.Form!

Thanks anyway!!;)