Paging Records Help

[anon007]

I have the code below but it gives me this error, Any help would be great.

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/search.asp, line 112


Code Here:
If Not rs.EOF=False Or Request.Form("searchcriteria")="" Then
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>No Results Found!<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
Else
' Set the page size of the recordset
RS.PageSize = nItemsPerPage

' Get the number of pages
nPageCount = RS.PageCount

nPage = CLng(Request.QueryString("Page"))

If nPage < 1 Or nPage > nPageCount Then
nPage = 1
End If

' First page
Response.Write "<A HREF=""search.asp?Keyword=" & searchcriteria & "&Page=1"">First Page</A>"
' Previous page:
Response.Write "<A HREF=""search.asp?Keyword=" & searchcriteria & "&Page=" & nPage - 1 & """>Prev. Page</A>"
' Next page
Response.Write "<A HREF=""search.asp?Keyword=" & searchcriteria & "&Page=" & nPage + 1 & """>Next Page</A>"
' Last page
Response.Write "<A HREF=""search.asp?Keyword=" & searchcriteria & "&Page=" & nPageCount & """>Last Page</A>"
' 15th page:
Response.Write "<A HREF=""search.asp?Keyword=" & searchcriteria & "&Page=15"">15th Page</A>"

Do While Not ( RS.EOF Or RS.AbsolutePage <> nPage )
'This is where the are results desplayed

 

[ChrisHirst]

and line 112 would happen to be ????


echo the sql command string to see what is missing before the call to set the recordset, which is more than likely line 112 and not shown here.

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[anon007]

Sorry line 112 is

RS.PageSize = nItemsPerPage

 

[Sheco]

Maybe something like this:[tt]
If IsNumeric(nItemsPerPage) Then
RS.PageSize = CLng(nItemsPerPage)
Else
RS.PageSize = 10 'default to 10 items per page
End If
[/tt]

 

[anon007]

Still the same error

 

[ChrisHirst]

Ok. About the only thing I can see in that code is that you should be setting the RS.pagesize before the call to open the recordset.



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[ChrisHirst]

BTW is nItemsPerPage definitely > 0 ?

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[Sheco]

Oh, hahaha. Good catch Chris.

0 is definately numeric!

 

[Sheco]

So is 3.14159265

 

[anon007]

I am still trying to get my head around this. So I set the RS.PageSize = nItemsPerPage <--- to RS.PageSize = 10. The error goes away but no search results appear?

 

[anon007]

I did try puting the rs.pagesize before the rs.open and it gives the same error.

 

[anon007]

If you guys know and easer way for a paging solution I am all ears

 

[ChrisHirst]

http://www.asp101.com/samples/viewasp.asp?file=db_paging.asp

Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[Sheco]

So there must be some other error.

I didnt see the AbsolutePage property set anywhere...

How do you get the current record to be the first record in the requested page without setting this property?

http://www.w3schools.com/ado/prop_rs_absolute.asp

 

[anon007]

I got it to work I for got to add this code:

RS.CursorLocation = 3 ' adUseClient

 

[anon007]

Ok thanks guys for all your help. I have one more problem when I click the next page button its not showing any results? I think its got to do with these two lines of code but I don't know what could be wrong?

' Set the page size of the recordset
RS.PageSize = 10

' Get the number of pages
nPageCount = RS.PageCount


ID = Request.QueryString("searchcriteria")<----

nPage = CLng(Request.QueryString("Page")) <---These are not captureing the info needed

If nPage < 1 Or nPage > nPageCount Then
nPage = 1
End If

Response.Write("<td width='100%'>" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=1"">First Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPage - 1 & """>Prev. Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPage + 1 & """>Next Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPageCount & """>Last Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=15"">15th Page</A>&nbsp;&nbsp;" & _
"<br><p></td>")

 

[anon007]

Here is where I got the code to build from if anyone can tell me if I missed somthing that would be great.

http://www.codeproject.com/asp/rspaging.asp

 

[Sheco]

You still need to set the position of the current record to the first record on the page to be displayed.

See on that page you linked under step 3 before the Do loop it has this:[tt]
' Position recordset to the correct page
RS.AbsolutePage = nPage[/tt]

 

[anon007]

Thanks guys I did add as you suggested and it works but it scrolls all the records when I click the link below. It seems that its not catching the ID in the query string.

"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPage + 1 & """>Next Page</A>&nbsp;&nbsp;" & _

#######################

Here is the complete code can you see anything that would cause the above


set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=SQLNCLI;Server=SERVER\SQLEXPRESS;Database=Data;Trusted_Connection=yes;"
Conn.Open

sql = "SELECT * FROM [Accounts] WHERE [Accounts].[AccountName] LIKE '%" &Trim(request.form("search")) & "%' " &_
"OR [Accounts].[SSN] LIKE '%" &Trim(request.form("search")) & "%' " &_
"OR [Accounts].[Phone] LIKE '%" &Trim(request.form("search")) & "%' " &_
"ORDER BY [Accounts].[AccountName] ASC"
RS.CursorLocation = 3 ' adUseClient
rs.open sql, conn, strConnectionString


<%

' Set the page size of the recordset
RS.PageSize = 1

' Get the number of pages
nPageCount = RS.PageCount

nPage = CLng(Request.QueryString("Page"))

ID = Request.QueryString("ID")

If nPage < 1 Or nPage > nPageCount Then
nPage = 1
End If

' Position recordset to the correct page
RS.AbsolutePage = nPage
'--

If Not rs.EOF=False Then
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>No Results Found!<br><hr noshade color=#000000 size=1></td>")
Response.Write("</tr>")
Else
Do While Not ( RS.Eof Or RS.AbsolutePage <> nPage )
Response.Write("<tr bgcolor='" & strRowColor & "'>")
Response.Write("<td width='100%'><b>" & rs("AccountName") & "</b>&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;Beginning Date:&nbsp;" & rs("BeginningDate") & _
"&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;<font color='#FF0000'>Balance Due:</font>&nbsp;" & FormatCurrency(rs("BalanceDue")) & _
"<br>Address:&nbsp;" & rs("Address") & "&nbsp;" & rs("City") & ",&nbsp;" & rs("State") & ",&nbsp;" & rs("Zip") & "<br>-----<br><font style='" & _
"font-size: 9pt'><a href='/control/company/agents/Accounts/processing.asp?ID=" & rs("AccountID") & "'>View Account</a>&nbsp;&nbsp;|&nbsp;&nbsp;" & _
"<a href='/control/company/agents/Accounts/processing.asp?ID=" & rs("AccountID") & "'>" & _
"Make Payment</a>&nbsp;&nbsp;|&nbsp;&nbsp;<a href='/control/company/agents/Accounts/processing.asp?ID=" & rs("AccountID") & "'>Note Account</a>" & _
"&nbsp;&nbsp;|&nbsp;&nbsp;<a href='/control/company/agents/Accounts/processing.asp?ID=" & rs("AccountID") & "'>View Notes</a></font>" & _
"<br><hr noshade color=#000000 size=1><p></td>")
Response.Write("</tr>")
rs.MoveNext
Loop
Response.Write("<td width='100%'>" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=1"">First Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPage - 1 & """>Prev. Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPage + 1 & """>Next Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=" & nPageCount & """>Last Page</A>&nbsp;&nbsp;" & _
"<A HREF=""search2.asp?ID=" & ID & "&Page=15"">15th Page</A>&nbsp;&nbsp;" & _
"<br><p></td>")
Response.Write("</tr>")
rs.Close
Conn.Close
End If

 

[anon007]

I got it to work on the query buy changing the form from post to get and modifing some of the code. However I have yet to be able to get the string to hold the info on the second page?