Display limited no. of rows

[ketankshah]

I am creating a discussion forum. In the page which shows the search results, I want to show limited no. of rows on each page (as this forum does), let's say 15 rows per page.
The data is coming from a recordset thru SQL Server database.

How can I do this?

Ketan

 

[Boomerang]

Hi Ketan,

You can set the MaxRecords property of the recordset (Rs):

dim NumRec

Numrec = 15

Rs.MaxRecords = Numrec

Hope this will help,
Erik

 

[Geee]

Alternatively you can select the first 15 using the SQL query in the format SELECT TOP 15 FROM TABLE WHERE CONDITION = TRUE.

Hope this Helps

G -GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[ketankshah]

Thanks for the prompt reply from both of you.

Your suggestion will work for the first page but with the same recordset if I want to provide the 'Next' Button the the page which will show the next 15 records on the next page then how can I use the MaxRecords property?

Suppose the result retrieves 45 rows. So the script should show first 15 rows on the 1st page, 16-30 rows on the 2nd page and 31-45 rows on the last page.

Ketan

 

[Geee]

The way most sites handle this is within the URL pass the primary key info of the first record to display. This works well if your primary key is an autonumber. If it is you can use code like the following...


URL = display.asp?RecordNo=1

SQL = "SELECT TOP 15 * FROM TABLE WHERE CRITERIA=TRUE & PRIMARYKEY > " & REQUEST.QUERYSTRING("RECORDNO&quot

LINK TO NEXT PAGE = <A HREF=&quot;display.asp?RecordNo=<%=REQUEST.QUERYSTRING(&quot;RECORDNO&quot+15%>&quot;>Next</A>


Hope this helps

G -GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[Boomerang]

Hi Ketan.

After submit my first answer I allready thougt that this would be your second question.
There are more recordset propertys like AbsolutePage, PageCount, and PageSize.

I found an example in my MS Visual interdev Help. I hope you can make someting out of it.

Erik

here it is:

This example uses the AbsolutePage, PageCount, and PageSize properties to display names and hire dates from the Employee table five records at a time.

Public Sub AbsolutePageX()

Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer

' Open a recordset using a client cursor
' for the employee table.
strCnn = &quot;Provider=sqloledb;&quot; & _
&quot;Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; &quot;
Set rstEmployees = New ADODB.Recordset
' Use client cursor to enable AbsolutePosition property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.Open &quot;employee&quot;, strCnn, , , adCmdTable

' Display names and hire dates, five records
' at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = &quot;&quot;
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & &quot; &quot; & _
rstEmployees!lname & &quot; &quot; & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close

End Sub

 

[ketankshah]

Thanks to both of you.

Erik's scripts really worked for me. I didn't have fixed set of values to be retrieved like Geee's suggestion.

Thanks a lot again.

Ketan