Show list in groups of 10

[secondreckoned]

Hi,
I have multiple drop down list that allows a user to select categories that will display articles based on their selections. It's possible for the results to list dozens of articles. I am having some logic blockage in getting started with this. I can get the listing to show the article name as an anchor and a short description below it and list it all on one page but because the search results can generate large lists I would like to be able to show the results on a page in groups of 10 for instance. I am using an sql db and asp as my web script Can someone help point me in the right direction as to how I can do this?

<table>
<%
Set keyword1RS = objADOConn.execute("Select * From tb_OCCNetProcessDocs Where Keyword1 in ('" & replace(category, ", ", "','") & "') or Keyword2 in ('" & replace(category, ", ", "','") & "') or Keyword3 in ('" & replace(category, ", ", "','") & "')")
do until keyword1RS.eof
%>
<tr>
<td>
<%
response.Write "<a target='_blank' href='

http://page/Menu/OCC_Documents/"&ke...&"'>"&(keyword1RS("DocumentName"))&"</a><br>"

response.Write(keyword1RS("DocDescription"))&"<br>"
keyword1RS.movenext
loop
%>
</td>
</tr>
</table>

 

[petermeachem]

I couldn't work out how to do this either with an arbitrary sort order. All the examples I found depended on the recordset being sorted by a unique key. You can then do something like, SELECT TOP 10 a,b,c from etc WHERE Key > end of last display page.
If the display isn't in Key order, or TOP 10 doesn't work with your database, this doesn't work at all.

What I ended up with was to use Getrows to fill an array. It is then pretty easy to display a page from that array. I also wanted to have hyperlinks for pages 2,3,4 etc which fits in well with this method.
So long as there aren't too many rows in the recordset this seems a reasonable thing to do.

 

[Tarwn]

The times I have worked with something like this in the past I have used a combination of the built in paging in the recordset object and querystring values in the page links.

I randomly clicked through the (tons of) search results on paging here in the forum and found one of the ones I was thinking: thread333-913410

That thread shows how to set the paging properties on the recordset.


Now, you obviously have added an extra wrinkle by trying to page a category instead of the same SQL statement each time, but I think it's fairly do-able if we extend the code form the linked thread above.


Assumptions:
If no categories are selected, we want to paginate all of them
If the user changes the category selection, we want to start them over on page 1


Now we want to figure out how to fill in the section in the linked thread that I commented as "Complicated code here".

First thing we need to do is figure out how to handle the dropdown box. This is actually fairly easy because we can embed that dropdown in it's own little form. This will purposely lose the current page number so we won't even have to bother resetting the page number to 1:

<form method="POST" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
   <select name="selCategory" multiple="multiple">
      options here
   </select>
   <input type="submit" value="View Selected Categories"/>
</form>

Now we need to work out how to do the SQL query based on this value. If we assume that the links we are going to output later will pass the selected categories also, then all we will need to do to make the form OR links work is a simple if statement while we build the SQL statement:

<%
Dim sqlStr, category
sqlStr = "Select * From tb_OCCNetProcessDocs"
If Request.Form("selCategory") <> "" Then
   category = "'" & Replace(Request.Form("selCategory")," ,","','") & "'"
Else
   category = "'" & Replace(Request.QueryString("selCategory")," ,","','") & "'"
End If
If Len(selCategory) > 0 Then 
   sqlStr = sqlStr & " WHERE Keyword1 IN (" & category & ") OR Keyword2 IN (" & category & ") OR Keyword3 IN (" & category & ")"
End If
%>

Obviously you will need a connection object and recordset before being able to set the paging properties or outputting the records. You will also need this (and thus the previous piece) before the code in the linked thread so you can determine the max page number.

Dim connArt, rsArt
Set connArt = Server.CreateObject("ADODB.Connection")
connArt.Open "your connection string"
rsArt.Open sqlStr, connArt
rsArt.PageSize = RECS_PER_PAGE 'will define this constant later in post when putting it all together

'--- based on code from linked thread above
Dim curPageNumber, MaxPageNumber
[highight][s]Const NUM_NAV_PAGES = 11[/s] 'moving this declaration - see later code[/highlight]

If Request.QueryString("page_num") <> "" Then
    curPageNumber = cInt(Request.QueryString("page_num"))
Else
    curPageNumber = 1
End if
MaxPageNumber = [highlight]rsArt.PageCount[/highlight]
'double check cur against max
If curPageNumber > MaxPageNumber Then curPageNumber = MaxPageNumber
'doublecheck against lowest
If curPageNumber < 1 Then curPagenumber = 1
'----- End of attached code

'set the page number we are currently on
rsArt.AbsolutePage = curPageNumber

Now that we have the recordset queued up to the correct position we could output the data and the page numbers in either order.

------

Putting it together:
I didn't present this in the best order to put it together, and the thread I did find is a little old (and messy) so I'm going to give an example of how to put most of this together in a fairly clean manner.

<%
Option Explicit

'-------- Global Constants
Const RECS_PER_PAGE = 10	'number of records to show on each page
Const NUM_NAV_PAGES = 11	'max number of page numbers in nav links

'-------- Functions
' Modified NavLink from original thread to include categories
Function NavLink(aPageNum,linkText,categoryList)
	NavLink = "<a href=""sample.asp?page_num=" & aPageNum & "&selCategory=" & categoryList & """>" & linkText & "</a>"
End Function

'-------- Database Query
Dim connArt, rsArt, strSql, category, tempcats
Set connArt = Server.CreateObject("ADODB.Connection")
connArt.Open "your connection string"

'build basic SQL string
strSql = "Select * From tb_OCCNetProcessDocs"
'try to pull in post or querystring value
If Request.Form("selCategory") <> "" Then
	category = Request.Form("selCategory")
Else
	category = Replace(Request.QueryString("selCategory")
End If
'if something is in the selection, add to query
If Len(selCategory) > 0 Then 
	'replace all single quotes already in string with escaped single quotes (SQL Injection protection)
	tempcats = Replace(category,"'","''")
	'add quotes around the values
	tempcats = "'" + Replace(tempcats,", ","','") & "'"
	'finish building the SQL string
	strSql = strSql & " WHERE Keyword1 IN (" & tempcats & ") OR Keyword2 IN (" & tempcats & ") OR Keyword3 IN (" & tempcats & ")"
End If

'open the recordset
rsArt.CursorLocation = 3	'client cursor
rsArt.CursorType = 3		'static recordset
rsArt.Open sqlStr, connArt
'set the page size from our constant
rsArt.PageSize = RECS_PER_PAGE
rsArt.CacheSize = rsArt.PageSize

'-------- Queue Current Page
' Set current page in recordset, set max for later use, etc
Dim curPageNumber, MaxPageNumber

'try to get page number from querystring, otherwise default to page 1
If Request.QueryString("page_num") <> "" Then
    curPageNumber = cInt(Request.QueryString("page_num"))
Else
    curPageNumber = 1
End If
'Set max based upon recordset page count
MaxPageNumber = rsArt.PageCount
'double check cur against max
If curPageNumber > MaxPageNumber Then curPageNumber = MaxPageNumber
'doublecheck against lowest
If curPageNumber < 1 Then curPagenumber = 1

'-------- HTML Presentation
%>
<?xml version="1.0" encoding="ISO-8859-1"?>
	
<!doctype html public "-//W3C//DTD XHTML 1.0 Strict//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xml:lang="en-US" lang="en-US">
<head>
	<title> My Articles </title>
	<style></style>
</head>
<body>
<div>
	<form method="POST" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
		<select name="selCategory" multiple="multiple">
			<!-- options here -->
		</select>
		<input type="submit" value="Select Categories" />
	</form>
</div>
<table>
	<tr>
		<td>
<%

'-------- Build navigation
'back arrow
If curPageNumber > 1 Then
   Response.Write NavLink(curPageNumber-1,"Prev",category) & " "
Else
   Response.Write "Prev "
End If

'show up to NUM_NAV_PAGES page links
Dim page_ctr, page_start, page_end
'assume we're in the middle
page_start = curPageNumber - (NUM_NAV_PAGES-1)/2
page_end = page_start + NUM_NAV_PAGES - 1
'determine where to really start and end page number output
If NUM_NAV_PAGES >= MaxPageNumber Then
   page_start = 1
   page_end = MaxPageNumber
ElseIf page_start < 1 Then
   page_end = page_end + (1 - page_start)
   page_start = 1
ElseIf page_end > MaxPageNumber Then
   page_start = page_start - (page_end - MaxPageNumber)
   page_end = MaxPageNumber
End If

'output beginning ellipses if needed
If page_start > 1 Then Response.Write "... "

'output page links
For page_ctr = page_start to page_end
   If page_ctr = curPageNumber Then
      Response.Write "<strong>" & curPageNumber & "</strong> "
   Else
      Response.Write Navlink(page_ctr,page_ctr,category) & " "
   End If
Next

'output some more ellipses
If page_end < MaxPageNumber Then Response.Write "... "

'output the Next
If curPageNumber < MaxPageNumber Then
   Response.Write NavLink(curPageNumber+1,"Next",category) & " "
Else
   Response.Write "Next"
End If
%>
		</td>
	</tr>

<%
'-------- Output Search Results
'loop through recordset (starts at first record for the absolute page set above)
Do Until rsArt.EOF
	Response.Write "<tr><td>"

	'Output a link
	response.Write "<a target='_blank' href='[URL unfurl="true"]http://page/Menu/OCC_Documents/"&rsArt("DocumentName")&"'>"&(rsArt("DocumentName"))&"</a><br>"[/URL]
	response.Write(rsArt("DocDescription"))&"<br>"

	Response.Write "</td></tr>"

	'move to next record
	rsArt.MoveNext
	'if we have gone beyond the current page, we're done
	If rsArt.AbsolutePage <> curPageNumber Then Exit Loop
Loop

'-------- Cleanup
rsArt.Close
Set rsArt = Nothing
connArt.Close
Set connArt = Nothing

%>
</table>
</body>
</html>

That code is not at all tested, having been written on the fly. I added in a few extras, such as the CacheSize and a few other things here and there. Additionally there are still places that it could be further optimized (such as the way the loop works, this could be altered a little to take better advantage of the CacheSize of the recordset).

There may be some minor syntax issues as well. It should serve well enough for it's intended purpose though (as an example).

-T

 

[petermeachem]

Pagesize doesn't work for me. I am using MySQL ODBC 3.51 Driver for a connection here at home. Do you know if that is why pagesize is ignored?

 

[Tarwn]

I don't know, i haven't used that specific driver in the past. Are you getting any kind of error?
Did you include the Recordset.CursorLocation = 3 line? This causes the cursor to be managed client-side (ie, by your ASP script) rather than by the server and should allow you to page even if the server doesn't innately support it.