database search-a newbie question

leca07 · Dec 18, 2002

Hi everyone...
Since I'm a complete ASP newbie I'd really really really appreciate your help. I have to make a simple

database search based on Access' .mdb-file. I've found some code samples but they all list search

results on the same page, bellow SEARCH button and I want them listed on a blank page. I'm posting

you my html code and database's name is products.mdb. If anyone of you have a time to add ASP

code please do so or at least point me to a site where I can get the code. Thanx in advance.
Jane

<html>
<head>
<title>Product Search</title>
</head>
<body >
<table width="780" border="0" cellspacing="0" cellpadding="0">
<tr><td> <form name="form1" method="get" action="">
key word <input type="text" name="textfield">
product <input type="text" name="textfield2">
manufactor <input type="text" name="textfield3">
price range <select name="select">
<option selected>$1-50</option>
<option>$50-100</option>
<option>$100-200</option></select></form></td></tr>
<tr><td><input type="submit" name="Search" value="Search"></td></tr>
</table>
</body>
</html>

mwolf00 · Dec 18, 2002

page1.asp
<html>
<head>
<title>Product Search</title>
</head>
<body >
<table width="780" border="0" cellspacing="0" cellpadding="0">
<tr><td> <form name="form1" method="get" action="page2.asp">
key word <input type="text" name="textfield">
product <input type="text" name="textfield2">
manufactor <input type="text" name="textfield3">
price range <select name="priceSelect">
<option selected value=1>$1-50</option>
<option value=2>>$50-100</option>
<option value=3>>$100-200</option></select></form></td></tr>
<tr><td><input type="submit" name="Search" value="Search"></td></tr>
</table>
</body>
</html>

page2.asp

<%
Dim Conn, dbpath
Set Conn = Server.Createobject("ADODB.Connection&quot

Dbpath ="DBQ=" & server.mappath("/databases/mydb.mdb&quot

Conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; " & dbpath
set objrs = server.createobject("ADODB.recordset&quot

strSQL = "SELECT field1, field2, field3, price FROM myTable WHERE price "
select case request("priceSelect&quot

case "1"
strsql = strsql & "BETWEEN 1 AND 50 "
case "2"
strsql = strsql & "BETWEEN 50 AND 100 "
case "3"
strsql = strsql & "> 100 "
if trim(request("textfield&quot

) <> "" then strSQL = strSQL & "AND field1 LIKE '*" & trim(request("textfield&quot

) & "*' "
if trim(request("textfield2&quot

) <> "" then strSQL = strSQL & "AND field2 LIKE '*" & trim(request("textfield2&quot

) & "*' "
if trim(request("textfield3&quot

) <> "" then strSQL = strSQL & "AND field3 LIKE '*" & trim(request("textfield3&quot

) & "*' "

set objRS = conn.execute(strsql)

if not objrs.eof then
strHTML = "<table border=1>"
do while not objRS.EOF
strHTML = strHTML &_
"<tr>"&_
"<td>" & objrs("field1&quot

& "</td>" &_
"<td>" & objrs("field2&quot

& "</td>" &_
"<td>" & objrs("field3&quot

& "</td>" &_
"<td>" & objrs("price&quot

& "</td>" &_
"</tr>"
objrs.movenext
loop
strHTML = strHTML & "</table>"
else
strHTML = "No Records Found"
end if

response.write strHTML
%> -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

database search-a newbie question

leca07

Technical User

mwolf00

Programmer

Similar threads

Part and Inventory Search

Sponsor