Closed Thread Icon

Topic awaiting preservation: SQL back to Default when I go to anothe page problem(ASP) Pages that link to <a href="https://ozoneasylum.com/backlink?for=12990" title="Pages that link to Topic awaiting preservation: SQL back to Default when I go to anothe page problem(ASP)" rel="nofollow" >Topic awaiting preservation: SQL back to Default when I go to anothe page problem(ASP)\

 
Author Thread
Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-18-2003 00:46

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") & "&nbsp;" &rs("Name")& "&nbsp;" &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> &nbsp;")
Next
%>

<%
rs.Close
conn.Close
Set conn= Nothing
%>
</body>
</html>



Please help me.

Hiroki Kozai

Rhino
Bipolar (III) Inmate

From: New Jersey, USA
Insane since: Jul 2003

posted posted 11-18-2003 10:35

Hiroki....you gotta take some thought into the code you write. Think about why it is not working and what is different from it showing correctly on the first page, but not on any additional page.

Anyway, you will need to pass the "Winery Region" that was originally selected. So, when you are building the Page links add the region into the QueryString. Also, make sure that you add additional code at the top of the page to be able to grab the request.QueryString value since it is now not being submitted by a form value.

poi
Paranoid (IV) Inmate

From: France
Insane since: Jun 2002

posted posted 11-18-2003 13:12
quote:
Hiroki....you gotta take some thought into the code you write.

Hum, I'm pretty sure I already heard that somewhere

Mathieu "POÏ" HENRI

Hiroki
Paranoid (IV) Inmate

From: NZ
Insane since: Dec 2002

posted posted 11-19-2003 00:19

Hi, many thanks for your help.
Now it works fine!
Make querystring....It was great tip!
Cheers.



Hiroki Kozai

« BackwardsOnwards »

Show Forum Drop Down Menu