query search on access db ... scratching my hair ... help plz

[cooldv]

I have posted this Q earlier also, but NO solution so far. I still
hope there must be someone with a solution.

My Q: displaying only SPECIFIC records from an access db with ASP
query.

e.g. let's say there r 700 records in a DB; out of that 50 are from
NY, 100 from NJ and rest from other states.

(i) If i want to display all 700 records, then the script works fine,
the paging function (previous 1 2 3 4 .... next) works perfectly.

(ii) Now, if i run a query and want to display only the records from
NY >> then the trouble starts.

the results of query: number of records (50), number of pages (3 at 20
records per page), and its display on first page (first 20 records)
--- all these are returned correctly. BUT,
the next pages of the query results (the 21-40 records on page 2 and
41-50 on page 3) are not displayed.
Intead all the 700 records start getting displayed from next and
previous querystring buttons.

here is the demo i have put up:
http://www.as.pgims.org/query/query.asp

Any kind souls out there???

 

[Ken Schaefer]

You need to pass your selection criteria from page to page. Otherwise, when
you go to the next page, your criteria is not being applied,and the whole
resultset (all 700 records) are being returned.

Cheers
Ken

: I have posted this Q earlier also, but NO solution so far. I still
: hope there must be someone with a solution.
:
: My Q: displaying only SPECIFIC records from an access db with ASP
: query.
:
: e.g. let's say there r 700 records in a DB; out of that 50 are from
: NY, 100 from NJ and rest from other states.
:
: (i) If i want to display all 700 records, then the script works fine,
: the paging function (previous 1 2 3 4 .... next) works perfectly.
:
: (ii) Now, if i run a query and want to display only the records from
: NY >> then the trouble starts.
:
: the results of query: number of records (50), number of pages (3 at 20
: records per page), and its display on first page (first 20 records)
: --- all these are returned correctly. BUT,
: the next pages of the query results (the 21-40 records on page 2 and
: 41-50 on page 3) are not displayed.
: Intead all the 700 records start getting displayed from next and
: previous querystring buttons.
:
: here is the demo i have put up:
: http://www.as.pgims.org/query/query.asp
:
: Any kind souls out there???

 

[cooldv]

hi ken,

thanks for ur suggestion. can you tell me how do u *pass your
selection criteria from page to page* as you mentioned.

 

[Bob Barrows]

hi ken,

thanks for ur suggestion. can you tell me how do u *pass your
selection criteria from page to page* as you mentioned.

In a hidden form field? As part of the querystring? In a Session variable?
Take your pick.

 

[Ken Schaefer]

If you are using Form posts go to from page to page, then you'll need to
create hidden form inputs, eg:

<input type="hidden" name="txtState" value="<%=strState%>">

and on the next page, check to see if there is a value in
Request.Form("txtState"), and if so, you include that in the WHERE clause of
your SQL statement - *the same way that you created the recordset in the
first place, for the first page of results*

If you are using plain hyperlinks, you can do this via the QueryString.

Cheers
Ken

: hi ken,
:
: thanks for ur suggestion. can you tell me how do u *pass your
: selection criteria from page to page* as you mentioned.

 

[cooldv]

Hi Ken,

excuse my dumbo brain! just how do you write the code for what you
suggested?
(the demo of my trouble and the code of the file is also shown at:
http://www.as.pgims.org/query/query.asp)

here is my code:
<%
PageNo = Request.QueryString("Page")
IF isNumeric(PageNo) THEN
PageNo = CLng(PageNo)
END IF
IF PageNo < 1 THEN PageNo = 1

set conn = server.createobject("adodb.connection")
DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNtemp=dsntemp & "DBQ=" & server.mappath("db.mdb")
conn.Open DSNtemp

dim givenn
dim familyn
dim newman
dim siti
dim prov
dim cantree

givenn=request.form("ftn")
familyn=request.form("ltn")
newman=request.form("fresher")
siti=request.form("cty")
prov=request.form("stt")
cantree=request.form("ctry")

If givenn = "any" or givenn = "" then
givenn = ""
End If
If familyn = "any" or familyn = "" then
familyn = ""
End If
If newman = "0000" or newman = "" then
newman = ""
End If
If siti = "any" or siti = "" then
siti = ""
End If
If prov = "any" or prov = "" then
prov=""
End If
If cantree = "any" or cantree = "" then
cantree = ""
End If

sqlstmt = "SELECT * from database WHERE firstnam like '%"& givenn &"%'
and lastnam like '%"& familyn &"%' and census like '%"& newman &"' and
city like '%"& siti &"%' and state like '%"& prov &"%' and country
like '%"& cantree &"%' ORDER by lastname"

TotalRecs = rs.recordcount
rs.Pagesize=5
TotalPages = cInt(rs.pagecount)
rs.absolutepage=PageNo

<%
If PageNo > 1 Then
%>
<a href="dbQ.asp?page=<%= PageNo - 1 %>">&lt;&lt;
Prev</a>&nbsp;&nbsp;
<%
End If
%>

<%
response.write "Page " & PageNo & " of " & TotalPages & " "
DO WHILE iMenuCount <= TotalPages
Response.Write "<a href=""dbQ.asp?Page=" & iMenuCount
IF PageNo = iMenuCount THEN
Response.Write """ class=""selected"">" & iMenuCount & "</a>&nbsp;"
ELSE
Response.Write """ title=""Page " & iMenuCount & """>" & iMenuCount
& "</a>&nbsp;"
END IF
iMenuCount = iMenuCount + 1
Loop

If PageNo < TotalPages Then
%>
<a href="dbQ.asp?Page=<%= PageNo + 1 %>">Next &gt;&gt;</a>
<%
End If
%>

 

[Ken Schaefer]

Where you are creating the "Next" and "Previous" links. eg:

: <%
: If PageNo > 1 Then
: %>
: <a href="dbQ.asp?page=<%= PageNo - 1 %>">&lt;&lt;
: Prev</a>&nbsp;&nbsp;
: <%
: End If
: %>

You see how you are adding the "&page=<%=PageNo-1%>" data to pass to the
next page? You also need to pass your SQL criteria as well, eg givenN,
familyN etc.

On the next page, you need to get that out of the Request.QueryString
collection (not the Request.Form collection), and recreate your SQL
statement.

To streamline everying, I suggest you change the method of your initial form
from method="post" to method="get". That way you can just access the
Request.QueryString collection no matter which page the user is coming from.

Cheers
Ken



: Hi Ken,
:
: excuse my dumbo brain! just how do you write the code for what you
: suggested?
: (the demo of my trouble and the code of the file is also shown at:
: http://www.as.pgims.org/query/query.asp)
:
: here is my code:
: <%
: PageNo = Request.QueryString("Page")
: IF isNumeric(PageNo) THEN
: PageNo = CLng(PageNo)
: END IF
: IF PageNo < 1 THEN PageNo = 1
:
: set conn = server.createobject("adodb.connection")
: DSNtemp="DRIVER={Microsoft Access Driver (*.mdb)}; "
: DSNtemp=dsntemp & "DBQ=" & server.mappath("db.mdb")
: conn.Open DSNtemp
:
: dim givenn
: dim familyn
: dim newman
: dim siti
: dim prov
: dim cantree
:
: givenn=request.form("ftn")
: familyn=request.form("ltn")
: newman=request.form("fresher")
: siti=request.form("cty")
: prov=request.form("stt")
: cantree=request.form("ctry")
:
: If givenn = "any" or givenn = "" then
: givenn = ""
: End If
: If familyn = "any" or familyn = "" then
: familyn = ""
: End If
: If newman = "0000" or newman = "" then
: newman = ""
: End If
: If siti = "any" or siti = "" then
: siti = ""
: End If
: If prov = "any" or prov = "" then
: prov=""
: End If
: If cantree = "any" or cantree = "" then
: cantree = ""
: End If
:
: sqlstmt = "SELECT * from database WHERE firstnam like '%"& givenn &"%'
: and lastnam like '%"& familyn &"%' and census like '%"& newman &"' and
: city like '%"& siti &"%' and state like '%"& prov &"%' and country
: like '%"& cantree &"%' ORDER by lastname"
:
: TotalRecs = rs.recordcount
: rs.Pagesize=5
: TotalPages = cInt(rs.pagecount)
: rs.absolutepage=PageNo
:
: <%
: If PageNo > 1 Then
: %>
: <a href="dbQ.asp?page=<%= PageNo - 1 %>">&lt;&lt;
: Prev</a>&nbsp;&nbsp;
: <%
: End If
: %>
:
: <%
: response.write "Page " & PageNo & " of " & TotalPages & " "
: DO WHILE iMenuCount <= TotalPages
: Response.Write "<a href=""dbQ.asp?Page=" & iMenuCount
: IF PageNo = iMenuCount THEN
: Response.Write """ class=""selected"">" & iMenuCount & "</a>&nbsp;"
: ELSE
: Response.Write """ title=""Page " & iMenuCount & """>" & iMenuCount
: & "</a>&nbsp;"
: END IF
: iMenuCount = iMenuCount + 1
: Loop
:
: If PageNo < TotalPages Then
: %>
: <a href="dbQ.asp?Page=<%= PageNo + 1 %>">Next &gt;&gt;</a>
: <%
: End If
: %>
:
:
: > If you are using Form posts go to from page to page, then you'll need to
: > create hidden form inputs, eg:
: >
: > <input type="hidden" name="txtState" value="<%=strState%>">
: >
: > and on the next page, check to see if there is a value in
: > Request.Form("txtState"), and if so, you include that in the WHERE
clause of
: > your SQL statement - *the same way that you created the recordset in the
: > first place, for the first page of results*
: >
: > If you are using plain hyperlinks, you can do this via the QueryString.
: >
: > Cheers
: > Ken
: >
: > : > : hi ken,
: > :
: > : thanks for ur suggestion. can you tell me how do u *pass your
: > : selection criteria from page to page* as you mentioned.