MSFN Forums > Argh ASP help someone?

MSFN Forums > Coding, Scripting and Servers > Web Development (HTML, Java, PHP, ASP, XML, etc.)

beccatigger

Jan 24 2005, 05:59 PM

Hi, Long time no see!
I've got a major problem that's annoying me loads!
I've got this big project website that I've got to create using ASP and running a MS access db behind. It's a book database with a book table with book name, book ID, subject_area, etc.etc.
I want to make a pull down menu with the subject areas in so you can display the results from a chosen category.
So far, for the form, I've got this:
this is the search page search.asp

QUOTE

<html>
<head>
<title>List Box Titles</title>
<head>
<body>
<h2>Search by subject category</h2>
<p><br>
<form method="get" action="results.asp">
<select name="subject" size="1">
<option value=accountancy>Accountancy</option>
<option value=computing>Computing</option>
<option value=dvds>DVDs</option>
</select>
<input type="submit">
</form>
</body>
</html>

then the results.asp is this:

QUOTE

<html>
<head>
<title>Title Response</title>
<head>
<body>
<h1>Bibliographic Data</h1>
<%
varTitle= request.querystring("subject")
Set oRp=server.createobject("ADODB.recordset")
sqltext="Select * from books WHERE subject_area = "';response.write "<p> Title Searched: <b>" & subject_area &"</b>"
orp.open sqltext, "driver={microsoft access driver (*.mdb)};dbq=" & server.mappath("shop.mdb")
if orp.eof then
response.write varTitle & " Not Found"
else
response.write "<p>The Bibliographic Data is as follows:<br>"
response.write "<p>Book: <b>" & orp("Book_Name") & "</b><br>"
end if
response.write "<p> <a href='search.asp'>Another search</a>"
%>
</body>
</html>

The bit hightlighted in red is wrong but i don't know how to reference it (maybe to the query string but I don't know how to?!)

I'd be grateful for any help!

Carmon

Jan 24 2005, 06:18 PM

i think this shud be in the Web development section

gamehead200

Jan 24 2005, 06:24 PM

OMG!! She's back!! NO!!!!

[ Moved. ]

IcemanND

Jan 24 2005, 07:07 PM

Welcome back, try this
edited: to replace servervariables with querystring.

This should create your sql statement:

CODE

sqltext="Select * from books WHERE subject_area = '" & request.querystring("subject") & "'"

This should display on the page what request was:

CODE

response.write ("<p> Title Searched: <b>" & request.querystring("subject") & "</b>")

hazard0usIntent

Jan 24 2005, 11:53 PM

Actually this line is getting the value of the submitted form field:

CODE

varTitle= request.querystring("subject")

So the SQl statement just needs to be:

CODE

sqltext="Select * from books WHERE subject_area = '" & varTitle & "'"

Use request.querystring("form field name") to reference fields submitted in a form with a method of "get".

Use request.form("form field name") to reference fields submitted in a form with a method of "post".

I would not recommend using request.servervariables to reference submitted form fields as the methods above are more direct and efficient.

IcemanND

Jan 25 2005, 08:55 AM

oops, that is what I meant to use. QUERYSTRING not SERVERVARIABLES.

I edited my previous post to fix it

beccatigger

Jan 26 2005, 04:34 AM

OK, It took me a day, but I figured it out
You have to make a form, but it doesnt need a recordset as I suspected)
However, the results page needs to link from that pull down menu. So u name the <select> tag so I named it category. Then when I made the recordset for the second page, you then filter the subject_area from books table with the url category, so when it flips onto the results page,then url would be, e.g.:
search.asp?category=IT

Here's the code examples, in case anybody ever has to go through the hell of making one of these babys:

QUOTE

<%@LANGUAGE="VBSCRIPT"%>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styling.css" rel="stylesheet" type="text/css">
</head>

<body>
<form action="search.asp" method="get" name="category">
<select name="category" size="1" style="font-size:12px;color:#006699;font-family:verdana;background-color:#ffffff;" >

<option value="MS Office"><font class="pulldown">MS Office</font></option>
<option value="ICT">ICT</option>
<option value="Programming">Programming</option>
<option value="Networking">Networking</option>
<option value="Internet">Internet</option>
<option value="Games">Games</option>
<option value="DVDs">DVDs</option>
<option value="Videos">Videos</option>
<option value="Other">Other</option>
</select>
<input type=submit value="Submit!">
</form>
</body>
</html>

and the results page looks like this:

QUOTE

<%@LANGUAGE="VBSCRIPT"%>

<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("category") <> "") Then
Recordset1__MMColParam = Request.QueryString("category")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_searching_STRING
Recordset1.Source = "SELECT * FROM Books WHERE Subject_area = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<table border=2>
<tr>
<td><a href="details.asp?ID=<%=(Recordset1.Fields.Item("ID").Value)%>"><%=(Recordset1.Fields.Item("ID").Value)%></a></td>
</tr>
<tr>
<td><%=(Recordset1.Fields.Item("Book_Name").Value)%><br></td>
</tr>
<tr>
<td><%=(Recordset1.Fields.Item("Edition_number").Value)%></td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.