Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > query search on access db ... scratching my hair ... help plz

Reply
Thread Tools

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

 
 
cooldv
Guest
Posts: n/a
 
      09-14-2003
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???
 
Reply With Quote
 
 
 
 
Ken Schaefer
Guest
Posts: n/a
 
      09-14-2003
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

"cooldv" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
: 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???


 
Reply With Quote
 
 
 
 
cooldv
Guest
Posts: n/a
 
      09-15-2003
hi ken,

thanks for ur suggestion. can you tell me how do u *pass your
selection criteria from page to page* as you mentioned.
 
Reply With Quote
 
Bob Barrows
Guest
Posts: n/a
 
      09-15-2003
cooldv wrote:
> 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.


 
Reply With Quote
 
Ken Schaefer
Guest
Posts: n/a
 
      09-15-2003
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

"cooldv" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
: hi ken,
:
: thanks for ur suggestion. can you tell me how do u *pass your
: selection criteria from page to page* as you mentioned.


 
Reply With Quote
 
cooldv
Guest
Posts: n/a
 
      09-16-2003
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" <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> 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
>
> "cooldv" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed) om...
> : hi ken,
> :
> : thanks for ur suggestion. can you tell me how do u *pass your
> : selection criteria from page to page* as you mentioned.

 
Reply With Quote
 
Ken Schaefer
Guest
Posts: n/a
 
      09-16-2003
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



"cooldv" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) om...
: 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" <(E-Mail Removed)> wrote in message
news:<(E-Mail Removed)>...
: > 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
: >
: > "cooldv" <(E-Mail Removed)> wrote in message
: > news:(E-Mail Removed) om...
: > : hi ken,
: > :
: > : thanks for ur suggestion. can you tell me how do u *pass your
: > : selection criteria from page to page* as you mentioned.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
(Hair Loss) Natural 100% Proven Techniques To Grow Hair rogerp C Programming 0 03-22-2009 07:45 AM
(Hair Loss) Natural 100% Proven Techniques To Grow Hair rogerp Python 0 03-22-2009 07:45 AM
hair loss help, grow hair again for better photos thehairlossman@operamail.com Digital Photography 13 03-19-2006 01:31 AM
Re: plz help!!! plz plz plz plzplzplz help the noob alkzy Microsoft Certification 0 10-31-2004 10:04 PM
Computer Problems... Plz Plz Plz HELP ME..... Nick Computer Support 0 06-04-2004 08:50 PM



Advertisments