recordset paging won't show second page

[Supaya]

Hi I'm querying a database and returning the records using absolute
page and all that stuff.

The user can choose to do a keyword search or display all records.
the page is set up so that the sql statement gets passed back to the
page using the querystring method.

This works great when the user selects the display all...i can browse
through the records perfect....however, when the user enters a keyword
I get the first page and when i click on next it displays no records
found....

the whole sql string is getting passed thru...i did a response.write
to make sure and it seems intact...

any help would be greatly appreciated.

Thanx

[Utada P.W. SIU]

can u post any code so we can know what's wrong with you program?

"Supaya" <tonyabishop@hcse.ca> wrote in message
news:eaf17c37.0404200607.2dd83766@posting.google.c om...

> Hi I'm querying a database and returning the records using absolute
> page and all that stuff.
>
> The user can choose to do a keyword search or display all records.
> the page is set up so that the sql statement gets passed back to the
> page using the querystring method.
>
> This works great when the user selects the display all...i can browse
> through the records perfect....however, when the user enters a keyword
> I get the first page and when i click on next it displays no records
> found....
>
> the whole sql string is getting passed thru...i did a response.write
> to make sure and it seems intact...
>
> any help would be greatly appreciated.
>
> Thanx

[Ken Schaefer]

>> The error is on line 8

> How do you know? I didn't post any code

Exactly

Cheers
Ken

"Supaya" <tonyabishop@hcse.ca> wrote in message
news:eaf17c37.0404200607.2dd83766@posting.google.c om...
: Hi I'm querying a database and returning the records using absolute
: page and all that stuff.
:
: The user can choose to do a keyword search or display all records.
: the page is set up so that the sql statement gets passed back to the
: page using the querystring method.
:
: This works great when the user selects the display all...i can browse
: through the records perfect....however, when the user enters a keyword
: I get the first page and when i click on next it displays no records
: found....
:
: the whole sql string is getting passed thru...i did a response.write
: to make sure and it seems intact...
:
: any help would be greatly appreciated.
:
: Thanx

[Supaya .]

Sorry, here is the code

---Get parameters---
iPageSize = cInt(perpage) '---perpage comes from the form, this works
fine

'--- set sql query ---
if keyword<>"" then
if category="boardname" then
sql="select * from tblprofile where board_id in (select id from
tblboards where " & category & " LIKE '%" & keyword & "%') and
accountstatus='A'"
else
sql="select * from tblprofile where " & category & " LIKE '%" &
keyword & "%' and accountstatus='A'"
end if
elseif request.form("displayall") = "on" or request.form("displayall") =
"ON" then
sql="select * from tblprofile where accountstatus='A'"
else
sql="select * from tblprofile where firstname LIKE '" & firstname & "'
and accountstatus='A'"
end if

sql = sql & " order by " & sortby & " " & sortorder & ";"
'-------------------------------------

response.write sql 'just testing the value

'--- Get variables from the url, if any ---
if request.querystring("sql")<>"" then
sql = trim(request.querystring("sql"))
end if
if request.QueryString("psize") <> "" then
iPageSize = CInt(trim(Request.QueryString("psize")))
end if

' ---Retrieve page to show or default to 1---
If Request.QueryString("page") <> "" Then
iPageCurrent = CInt(trim(Request.QueryString("page")))
Else
iPageCurrent = 1
End If
'--------------------------------------

'---just testing the values to see what the hold---
response.write "<br><br>" & sql & "<br>"
response.write "page size " & iPageSize & "<br>"
response.write "current page " & iPageCurrent & "<br>"

' ---connect to database ---
Set objPagingConn = Server.CreateObject("ADODB.Connection")
objPagingConn.Open CONN_STRING

' ---Create recordset and set the page size---
Set rs= Server.CreateObject("ADODB.Recordset")
rs.PageSize = iPageSize

' --- Indicates to the web server where the cursor for the current
recordset should be created
' --- adUseClient creates cursor on client from ADOVBS.INC
rs.CursorLocation = adUseClient
rs.CacheSize = iPageSize

' ---Open RS---
rs.Open sql, objPagingConn, adOpenStatic, adLockReadOnly, adCmdText


' ---Get the count of the pages and records using the given page size---
iPageCount = rs.PageCount
iRecordCount = rs.RecordCount

' ---If the request page falls outside the acceptable range,
' ---give them the closest match (1 or max)
If iPageCurrent > iPageCount Then iPageCurrent = iPageCount
If iPageCurrent < 1 Then iPageCurrent = 1

'--again, just checking the value
response.write "Page count " & iPageCount & "<br>"

' ---Check page count to prevent bombing when zero results are returned!
If iPageCount = 0 Then
Response.Write "No records found!"
Else
' ---Move to the selected page
rs.AbsolutePage = iPageCurrent

'---Set numeric value of first and last record number being displayed
iFRecord = rs.AbsolutePosition

If CInt(iPageCurrent) = CInt(iPageCount) Then
iLRecord = iRecordCount
Else
iLRecord = iFRecord + (rs.PageSize - 1)
End if
%>

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Supaya .]

hi ken, sorry bout that. I've posted the code!

:) Tonya


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[roger]

"Supaya ." wrote

> response.write sql 'just testing the value
>
> '--- Get variables from the url, if any ---
> if request.querystring("sql")<>"" then
> sql = trim(request.querystring("sql"))
> end if

Try this -

'--- Get variables from the url, if any ---
if request.querystring("sql")<>"" then
sql = trim(request.querystring("sql"))
end if

response.write sql 'just testing the value


--
roger