Hi, guys. How are you?
Please help me.
You know I am trying to retrieve data according to dropdown choice.
Now SQL itself is working fine. I can suck data according to my selection.
But when I go to another page, SQL is back to default itself.
I don't know why it happens. My file is list.asp. Just wanted to show 10 items on a page. Often there are more than 10 items so that create another page. SQL statement works fine as long as I am on the same page.
When I go to page two, SQL back to default. I can choose query from dropdown box again. But if I go another page, it is gone. And Default sql is applied.
Do you know what I mean?
Here is my code:
code:
<%Option Explicit%>
<html>
<head>
<title>Wine List</title>
</head>
<body>
<%
Dim conn, rs, x
Dim pg, psize, maxpages, maxrecs, recct, i
'Create connection
Set conn = Server.createObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("../db/wine.mdb"))
'Create dynamic SQL ( Add to default sql)
Dim v_region
If Request("region")= "" then
v_region = "Canterbury"
elseif Not IsEmpty(Request("region")) Then
v_region = Request("region")
end if
'Create default SQL
Dim sql
sql = "SELECT WineryName, Name, Region, Price, Star, Vintage, Classic, Screw, AirNZ, Cuisine, Region"
sql = sql & " FROM Winery INNER JOIN (Wine_Winery INNER JOIN Wine ON Wine_Winery.WineID = Wine.WineID )"
sql = sql & " ON Winery.WineryID = Wine_Winery.WineryID WHERE Winery.Region = '"& v_region &"'"
'Create page index things
pg = Request("pg")
If pg = "" Then
pg = 1
End If
psize = Request("psize")
If psize = "" Then
psize = 10
End If
Set rs = server.CreateObject("ADODB.Recordset")
rs.cursorlocation = 3
rs.Open sql, conn
rs.pagesize=psize
maxpages = Int(rs.pagecount)
maxrecs = Int(rs.pagesize)
If Not rs.BOF Then
rs.absolutepage = pg
End IF
recct = 0
'End of create page index
%>
<%
'Test my SQL statement
response.write(sql)%>
<form method="post" action="">
<select name="region">
<option value="">Region</option>
<%
Dim rs_region, sql_region
sql_region = "SELECT Region FROM tblRegion"
Set rs_region = conn.execute(sql_region)
While NOT rs_region.EOF
%>
<option value="<%=rs_region("Region")%>" <% If Request("region") = rs_region("Region") Then %>Selected<% End If %>><%=rs_region("Region")%></option>
<%
rs_region.MoveNext
Wend
rs_region.Close
Set rs_region = Nothing
%>
</select>
<input type="Submit" value="Serch"> <input type="reset" value="Reset">
</form>
<table><tr><td><%If rs.BOF Then%>No Product found!<%Else%>You've foound:<%End IF%></td></tr></table>
<table align="center">
<%If rs.BOF Then
Response.write("<tr><td>No Product!</td></tr>")
Else
Do until rs.EOF or recct >= maxrecs
Response.Write("<tr>")
Response.Write("<td><b>")
Response.Write(rs("WineryName") & " " &rs("Name")& " " &rs("Region"))
Response.Write("</b></td>")
Response.Write("</tr>")
rs.MoveNext
recct = recct + 1
Loop
End IF
%>
</table>
<%
Response.Write("<br />")
'Here is generating page index.
For i = 1 to maxpages
Response.Write("<a href=""Wine_list22.asp?pg=" & i & """>" & i & "</a> ")
Next
%>
<%
rs.Close
conn.Close
Set conn= Nothing
%>
</body>
</html>
Please help me.
Hiroki Kozai
posted 11-18-2003 00:46
