Paging problem using parameter based SQL statement

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

August 6th, 2003, 09:19 AM

socoolbrewster

Junior Member

Join Date: Aug 2003

Location: UK

Posts: 10

Time spent in forums: < 1 sec
Reputation Power: 0

Paging problem using parameter based SQL statement

I have tried a number of paging scripts and have come to the conclusion it's not the paging script thats the problem. Its gettign crunch time for me as I need the this code is for to go live ASAP

Basically, a user enters a movie title using an input form. The value entered is stored in the variable name: cool

That variable is then passed onto the script using a request form statement in the script. Which is stored in the variable: strSearchtxt

The strSearchtxt is then embedded into an SQL statement and the required records are found.

In running the script it all works fine for the first page but on loading the second page I get: NO PAGE FOUND error

The error lies in the following code:

Response.Write "<a href='fiveon.asp&page="&PageCounter&"&strSearchtxt="&Request("cool") & "'>" & PageCounter & "</a>"

For example:

Say I entered TERMINATOR 3

as the criteria for my search

The first page works fine. On loading the second page the I get a page is not found. On examining the HTTP for the page to see what is getting passed it looks like this:

http://localhost/socoolbrew/fiveon....=Terminator%203

As the value is Terminator 3

could it be strSearchtxt=Terminator%203

Causing the problem? what does the 2 stand for?

The full listing for the script is as follows:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>

<%

Dim strSearchtxt
'StrSearchtxt = Server.HTMLEncode(Request.Form("cool"))
strSearchtxt = CStr(Trim(Request("cool"))) ' Get text to define SQL filter search
Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter
Dim strQuote

strQuote = Chr(34) ' The double quote character

'Get the Current page
Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then Page = 1

RowCount = 0

set con = server.CreateObject("ADODB.Connection")
Set rsPage = Server.CreateObject("ADODB.Recordset")

lsSQL = "SELECT * FROM review WHERE title = '" & strSearchtxt & "'"

Con.Open "DSN=brewster"

'Need a rich cursor type to support paging
rsPage.CursorType = 3 'adOpenStatic

'Set the number of records in each page to 10
rsPage.PageSize = 5

'Open recordset
rsPage.Open lsSQL, Con

'Set the current page based on the QueryString value
'Must cast it as an integer or else it will have problems.
rsPage.AbsolutePage = cInt(Page)

If Response.IsClientConnected = true then
Response.Write "<TABLE>"

'Loop though each of the records and break out when we
'have reached the max for this page
Do while not rsPage.eof and RowCount < rsPage.PageSize

'Write out content
Response.Write "<tr><td>" & rsPage("title") & "</td></tr>"

rsPage.Movenext
RowCount = RowCount + 1
Loop

Response.Write "</TABLE><p>"

'Page to determine the AbsolutePage to display.
For PageCounter = 1 to rsPage.PageCount
Response.Write "<a href='fiveon.asp&Page="&PageCounter&"&strSearchtxt="&strSearchtxt& "'>" & PageCounter & "</a>"
Next
rsPage.Close
set rsPage = Nothing
End if

%>
</body>
</html>

August 6th, 2003, 09:52 AM

Vlince

Contributing User

Join Date: Jun 2003

Location: Canada, Quebec, Montreal

Posts: 410

Time spent in forums: < 1 sec
Reputation Power: 6

Not sure if this can help but shouldn't this:

<a href='fiveon.asp&Page="&PageCounter........

become this:

<a href='fiveon.asp?Page="&PageCounter.........

Notice the change?

From & to ? on your first parameter

Hope this helps solve something
Sincerely

Vlince

August 6th, 2003, 10:08 AM

socoolbrewster

Junior Member

Join Date: Aug 2003

Location: UK

Posts: 10

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks for that have noted it. On changing it however I still get my errors :-(

My original code looks like this whereby on running the script when attempting to view the records for the next page the following error occurs:

Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/socoolbrew/fiveon.asp, line 49

What I believe the problem is that on first load the script works fine but then when running again for the second page the strSearchtxt see to equal to == NULL

<%@ Language=VBScript %>
<HTML>
<HEAD>
<TITLE></TITLE>
</HEAD>
<BODY>

<%

Dim strSearchtxt
'StrSearchtxt = Server.HTMLEncode(Request.Form("cool"))
strSearchtxt = Request.Form("cool") ' Get text to define SQL filter search
Dim Con
Dim rsPage
Dim Page
Dim RowCount
Dim PageCounter
Dim strQuote

strQuote = Chr(34) ' The double quote character

'Get the Current page
Page = Request.QueryString("Page")

'If there is no page set it to page 1
If Page = "" then Page = 1

RowCount = 0

set con = server.CreateObject("ADODB.Connection")
Set rsPage = Server.CreateObject("ADODB.Recordset")

lsSQL = "SELECT * FROM review WHERE title = '" & strSearchtxt & "'"

Con.Open "DSN=brewster"

'Need a rich cursor type to support paging
rsPage.CursorType = 3 'adOpenStatic

'Set the number of records in each page to 10
rsPage.PageSize = 5

'Open recordset
rsPage.Open lsSQL, Con

'Set the current page based on the QueryString value
'Must cast it as an integer or else it will have problems.
rsPage.AbsolutePage = cInt(Page)

If Response.IsClientConnected = true then
Response.Write "<TABLE>"

'Loop though each of the records and break out when we
'have reached the max for this page
Do while not rsPage.eof and RowCount < rsPage.PageSize

'Write out content
Response.Write "<tr><td>" & rsPage("title") & "</td></tr>"

rsPage.Movenext
RowCount = RowCount + 1
Loop

Response.Write "</TABLE><p>"

For PageCounter = 1 to rsPage.PageCount
Response.Write "<a href='fiveon.asp?Page=" & PageCounter & "'>" & _
PageCounter & "</a> "

Next
rsPage.Close
set rsPage = Nothing
End if

%>
</body>
</html>

August 6th, 2003, 10:24 AM

Vlince

Contributing User

Join Date: Jun 2003

Location: Canada, Quebec, Montreal

Posts: 410

Time spent in forums: < 1 sec
Reputation Power: 6

Well of course the variable strSearchtxt will equal "" that's because you're making a :

strSearchtxt = Request.Form("cool")

Notice the .Form, but when you click on your LINK the data ISN'T past via the .Form it is past via the .QueryString.

My suggestion to you is to simply use Request("cool") this way it will look in either the .Form OR .QueryString

Also, inside your link, you should name the parameter holding the "search criteria" cool instead of strSearchtxt:

Hope this helps!
Sincerely

Vlince

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: