Solved

ASP Record Paging

Posted on 2011-09-22
22
255 Views
Last Modified: 2012-05-12
Ihave some code below which displays a number if records to a web page, the size of the page has to be constrained so need to introduce pagination.

Basically the area for the records will be able to display 16 records at a time, after that - the user will need to click on a "Next" button to go to the next page of 16 records.

I think its something to do with the looping at this section:

    <%
    FOR strTemp = 1 to intRecordspp
    Do While Not RSdepartments.EOF
    %>

which is causing it to not display.  Its not throwing an error however its also still displaying all of the records.

It might be worth noting that if i remove the For / Next loop from the code then it runs correctly and displays 4 lines of 4 records (16 records total) but then carries on showing more lines of 4 records.

Doe anyone know where im going wrong?


<%
'//checks for a variable for records per page, if not set then use default
If Request.QueryString("rpp") = "" Then
    intRecordspp = 6
Else
    intRecordspp = CInt(Request.QueryString("rpp"))
End If

'//checks for a variable page number, if none then defaults to page 1
If Request.QueryString("PageNumber") = "" Then
    intPageNum = 1
Else
    intPageNum = CInt(Request.QueryString("PageNumber"))
End If
%>

<%
Set RSdepartments = Server.CreateObject("ADODB.Recordset")
SQLdepartments = "SELECT intDepartmentID, txtDepartmentName, txtTillButtonBgColour FROM tblCategoryDepartments WHERE intActive = 1 ORDER BY intOrder, txtDepartmentName"
RSdepartments.Open SQLdepartments,Connection,3,3

'//SET THE AMOUNT OF PRODUCTS PER PAGE
RSdepartments.PageSize = intRecordspp

'//IF ITS NOT THE END OF THE RECORDSET THEN THE PAGE NUMBER IS SET TO QUERYSTRING VALUE
If Not RSdepartments.EOF Then RSdepartments.AbsolutePage = intPageNum
intTotalRecords = RSdepartments.RecordCount
intTotalPages = RSdepartments.PageCount
%>
<table style="width: 100%; border: 4px solid #444; background-color: #FFFFFF;" cellspacing="3" cellpadding="2">
    <tr style="background-color: ##444; width: 100%;">
        <td colspan="4"><span class="HomeScreenHeader">Select Department</span></td>
    </tr>
    <% 
    FOR strTemp = 1 to intRecordspp
    Do While Not RSdepartments.EOF 
    %>
    <tr>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
        <% 
        RSdepartments.MoveNext
        If Not RSdepartments.EOF THEN
        %>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
        <% 
        RSdepartments.MoveNext
        If Not RSdepartments.EOF THEN
        %>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
        <% 
        RSdepartments.MoveNext
        If Not RSdepartments.EOF THEN
        %>
            <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
    </tr>
    <%
    RSdepartments.MoveNext
    If Not RSdepartments.EOF THEN
    End If
    End If
    End If
    End If
    Loop
    Next
    %>
    <tr style="background-color: ##444; width: 100%; vertical-align: top;">
        <td colspan="4">
            <table style="width: 100%;">
                <tr>
                    <td style="width:10%;">
                        <% IF NOT intPageNum = 1 THEN %>
                            <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%=intPageNum -1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_back.png"></a>
                        <% END IF %>
                    </td>
                    <td style="width:90%; text-align: center;"><span class="HomeScreenHeader">Page <%=intPageNum%> of <%=intTotalPages%></span></td>
                    <td style="width:10%;">
                        <% IF intPageNum < intTotalPages THEN %>
	                        <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%=intPageNum +1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_next.png"></a>
                        <% END IF %>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<%
RSdepartments.Close
Set RSdepartments = Nothing
SQLdepartments = " "
%>

Open in new window

0
Comment
Question by:net-workx
  • 11
  • 10
22 Comments
 
LVL 11

Expert Comment

by:G_H
ID: 36585554
Try changing line 36 to IF rather than DO

GH
0
 
LVL 13

Expert Comment

by:khairil
ID: 36585617
Hi,

You should open cursor on client side. Add this line after 19, before 20:

RSdepartments. CursorLocation = 3 ' adUseClient

Open in new window

0
 
LVL 13

Expert Comment

by:khairil
ID: 36585668
Futher more, it is better fix some bit of your code.

You can change your code from line 34 - 77 with this:

<%
' Position recordset to the correct page
For strTemp = 1 to intRecordspp
	RSdepartments.AbsolutePage = strTemp

	' Display the page of results
	Do While Not ( RSdepartments.Eof Or RSdepartments.AbsolutePage <> strTemp )
%>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>		
<%		
		RS.MoveNext
	Loop
	
	RSdepartments.Close
Next
%>

Open in new window

0
 

Author Comment

by:net-workx
ID: 36585974
G_H:

With your suggestion the first page shows 22 records (the entire recordset) but has page 1 of 3 on it, the second page shows all of the records after the intRecordspp variable, ie the second page shows all records from the nineth one onwards (my intRecordspp is set to 8 now), page three is showing the records from record 17 onwards.

Basically, its showing the intRecordspp and the reminder of the recordset, rather than cutting the RS off for display on the next page.

khairil - ill try your suggestion now....
0
 

Author Comment

by:net-workx
ID: 36585984
khairil:
RSdepartments. CursorLocation = 3 ' adUseClient : this does not change anything on the page
0
 

Author Comment

by:net-workx
ID: 36585989
khairil:
Ive change the code for line 34-77 as you suggested and this does show the first 8 records however it doesnt show them in two rows of 4.

The display table can have a maximum of 4 rows and 4 lines and then a "next page" option showing the next 8 records of the recordset.
0
 

Author Comment

by:net-workx
ID: 36595776
Any more suggestions on how to get this to display correctly?  Thank you!
0
 
LVL 13

Expert Comment

by:khairil
ID: 36597124
Hi,

To fix the row issue, try this:

<%
' Position recordset to the correct page
For strTemp = 1 to intRecordspp
	RSdepartments.AbsolutePage = strTemp
%>
<tr>
	' Display the page of results
	Do While Not ( RSdepartments.Eof Or RSdepartments.AbsolutePage <> strTemp )
%>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>		
<%		
		RS.MoveNext
	Loop
%>
</tr>
<%
	RSdepartments.Close
Next
%>

Open in new window

0
 
LVL 13

Expert Comment

by:khairil
ID: 36597127
Oppps.. missing syntax opening.. insert this "<%" after like 6 in code above.
0
 

Author Comment

by:net-workx
ID: 36599056
This throws an error - further more - i dont see anywhere that it limits the number of columns in the table to 4?
0
 
LVL 13

Expert Comment

by:khairil
ID: 36685987
There is missing openging <% in the code. I have rewrote back you code. Few thing that you might want to change, like over here I just use ADODB.Connection object as Connection - you have to open the connection object.

<%
' Declare our vars
Dim intPageSize       'How big our pages are
Dim intPageCount      'The number of pages we get back
Dim intPageCurrent    'The page we want to show
Dim SQLdepartments          'SQL command to execute
Dim RSdepartments     'The ADODB recordset object
Dim intRecordsShown   'Loop controller for displaying just intPageSize records
Dim intCount               'Standard looping var
Dim intQuoteID    'Not really know what it is

	' Get parameters
	intPageSize = 4 ' You could easily allow users to change this
	
	' Retrieve page to show or default to 1
	If Request.QueryString("PageNumber") = "" Then
		intPageCurrent = 1
	Else
		intPageCurrent = CInt(Request.QueryString("PageNumber"))
	End If
	
	
	'Not really now what it is
	If Request.QueryString("QuoteID") = "" Then
		intQuoteID = 1
	Else
		intQuoteID = CInt(Request.QueryString("QuoteID"))
	End If
	
		
	' Create recordset and set the page size
	Set RSdepartments = Server.CreateObject("ADODB.Recordset")
	RSdepartments.PageSize = intPageSize
	
	' You can change other settings as with any RS
	'RSdepartments.CursorLocation = adUseClient
	RSdepartments.CacheSize = intPageSize
	
	' Open RS
	RSdepartments.Open SQLdepartments, Connection,  3,  1,  1 'open with this option (adOpenStatic, adLockReadOnly, adCmdText)
	
	' Get the count of the pages using the given page size
	intPageCount = RSdepartments.PageCount
	
	' If the request page falls outside the acceptable range,
	' give them the closest match (1 or max)
	If intPageCurrent > intPageCount Then intPageCurrent = intPageCount
	If intPageCurrent < 1 Then intPageCurrent = 1
	
	' Check page count to prevent bombing when zero results are returned!
	If intPageCount = 0 Then
		Response.Write "No records found!"
	Else
		' Move to the selected page
		RSdepartments.AbsolutePage = intPageCurrent
%>

<table style="width: 100%; border: 4px solid #444; background-color: #FFFFFF;" cellspacing="3" cellpadding="2">
    <tr style="background-color: ##444; width: 100%;">
        <td colspan="4"><span class="HomeScreenHeader">Select Department</span></td>
    </tr>
<%

		' Loop through our records and ouput 1 row per record
		intRecordsShown = 0
		Response.Write "<tr>" 
		Do While intRecordsShown < intPageSize And Not RSdepartments.EOF
%>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
<%
			' Increment the number of records we've shown
			intRecordsShown = intRecordsShown + 1

			' Can't forget to move to the next record!
			RSdepartments.MoveNext
		Loop
		Response.Write "</tr>"
		
	' Show "previous" and "next" page links which pass the page to view
	' and any parameters needed to rebuild the query.  You could just as
	' easily use a form but you'll need to change the lines that read
	' the info back in at the top of the script.
		
%>
    <tr style="background-color: ##444; width: 100%; vertical-align: top;">
        <td colspan="4">
            <table style="width: 100%;">
                <tr>
                    <td style="width:10%;">
                        <% If intPageCurrent > 1 Then %>
                            <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%= intPageCurrent - 1 %>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_back.png"></a>
                        <% End If %>
                    </td>
                    <td style="width:90%; text-align: center;"><span class="HomeScreenHeader">Page <%=iPageCurrent%> of <%=iPageCount%></span></td>
                    <td style="width:10%;">
                        <% If intPageCurrent < intPageCount Then %>
	                        <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%= intPageCurrent + 1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_next.png"></a>
                        <% End If %>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<%
	End If
	
	' Close DB objects and free variables
	RSdepartments.Close
	Set RSdepartments = Nothing
	Connection.Close
	Set Connection = Nothing
%>

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:net-workx
ID: 36717409
Ive had to change the code to this to get it to run, but it is running now.  However, when i change the number of records displayed, it then comes up in one row of cells.

I want 16 records to be shown, 4 rows of 4 cells, however with that code it shows 16 cells next to each tother, running off the edge of the screen.
<%
' Declare our vars
Dim intPageSize       'How big our pages are
Dim intPageCount      'The number of pages we get back
Dim intPageCurrent    'The page we want to show
Dim SQLdepartments          'SQL command to execute
Dim RSdepartments     'The ADODB recordset object
Dim intRecordsShown   'Loop controller for displaying just intPageSize records
Dim intCount               'Standard looping var
Dim intQuoteID    'Not really know what it is

	' Get parameters
	intPageSize = 16 ' You could easily allow users to change this
	
	' Retrieve page to show or default to 1
	If Request.QueryString("PageNumber") = "" Then
		intPageCurrent = 1
	Else
		intPageCurrent = CInt(Request.QueryString("PageNumber"))
	End If
	
	
	'Not really now what it is
	If Request.QueryString("QuoteID") = "" Then
		intQuoteID = 1
	Else
		intQuoteID = CInt(Request.QueryString("QuoteID"))
	End If
	
		
	' Create recordset and set the page size
	Set RSdepartments = Server.CreateObject("ADODB.Recordset")
	RSdepartments.PageSize = intPageSize
	
	' You can change other settings as with any RS
	'RSdepartments.CursorLocation = adUseClient
	RSdepartments.CacheSize = intPageSize
	
	' Open RS
Set RSdepartments = Server.CreateObject("ADODB.Recordset")
SQLdepartments = "SELECT intDepartmentID, txtDepartmentName, txtTillButtonBgColour FROM tblCategoryDepartments WHERE intActive = 1 ORDER BY intOrder, txtDepartmentName"
	RSdepartments.Open SQLdepartments, Connection,  3,  1,  1 'open with this option (adOpenStatic, adLockReadOnly, adCmdText)
	
	' Get the count of the pages using the given page size
	intPageCount = RSdepartments.PageCount
	
	' If the request page falls outside the acceptable range,
	' give them the closest match (1 or max)
	If intPageCurrent > intPageCount Then intPageCurrent = intPageCount
	If intPageCurrent < 1 Then intPageCurrent = 1
	
	' Check page count to prevent bombing when zero results are returned!
	If intPageCount = 0 Then
		Response.Write "No records found!"
	Else
		' Move to the selected page
		RSdepartments.AbsolutePage = intPageCurrent
%>

<table style="width: 100%; border: 4px solid #444; background-color: #FFFFFF;" cellspacing="3" cellpadding="2">
    <tr style="background-color: ##444; width: 100%;">
        <td colspan="4"><span class="HomeScreenHeader">Select Department</span></td>
    </tr>
<%

		' Loop through our records and ouput 1 row per record
		intRecordsShown = 0
		Response.Write "<tr>" 
		Do While intRecordsShown < intPageSize And Not RSdepartments.EOF
%>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
<%
			' Increment the number of records we've shown
			intRecordsShown = intRecordsShown + 1

			' Can't forget to move to the next record!
			RSdepartments.MoveNext
		Loop
		Response.Write "</tr>"
		
	' Show "previous" and "next" page links which pass the page to view
	' and any parameters needed to rebuild the query.  You could just as
	' easily use a form but you'll need to change the lines that read
	' the info back in at the top of the script.
		
%>
    <tr style="background-color: ##444; width: 100%; vertical-align: top;">
        <td colspan="4">
            <table style="width: 100%;">
                <tr>
                    <td style="width:10%;">
                        <% If intPageCurrent > 1 Then %>
                            <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%= intPageCurrent - 1 %>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_back.png"></a>
                        <% End If %>
                    </td>
                    <td style="width:90%; text-align: center;"><span class="HomeScreenHeader">Page <%=iPageCurrent%> of <%=iPageCount%></span></td>
                    <td style="width:10%;">
                        <% If intPageCurrent < intPageCount Then %>
	                        <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%= intPageCurrent + 1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_next.png"></a>
                        <% End If %>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<%
	End If
	
	' Close DB objects and free variables
	RSdepartments.Close
	Set RSdepartments = Nothing
	Connection.Close
	Set Connection = Nothing
%>

Open in new window

0
 
LVL 13

Expert Comment

by:khairil
ID: 36812668
Hi,

Try this, my comment later:
<%
' Declare our vars
Dim intPageSize       'How big our pages are
Dim intPageCount      'The number of pages we get back
Dim intPageCurrent    'The page we want to show
Dim SQLdepartments          'SQL command to execute
Dim RSdepartments     'The ADODB recordset object
Dim intRecordsShown   'Loop controller for displaying just intPageSize records
Dim intCount               'Standard looping var
Dim intQuoteID    'Not really know what it is
Dim blnNeedTREOR	'FOR NEW TR AT END OF RECORDSET IF X MOD 4 = 0

	' Get parameters
	intPageSize = 16 ' You could easily allow users to change this
	
	' Retrieve page to show or default to 1
	If Request.QueryString("PageNumber") = "" Then
		intPageCurrent = 1
	Else
		intPageCurrent = CInt(Request.QueryString("PageNumber"))
	End If
	
	
	'Not really now what it is
	If Request.QueryString("QuoteID") = "" Then
		intQuoteID = 1
	Else
		intQuoteID = CInt(Request.QueryString("QuoteID"))
	End If
	
		
	' Create recordset and set the page size
	Set RSdepartments = Server.CreateObject("ADODB.Recordset")
	RSdepartments.PageSize = intPageSize
	
	' You can change other settings as with any RS
	'RSdepartments.CursorLocation = adUseClient
	RSdepartments.CacheSize = intPageSize
	
	' Open RS
Set RSdepartments = Server.CreateObject("ADODB.Recordset")
SQLdepartments = "SELECT intDepartmentID, txtDepartmentName, txtTillButtonBgColour FROM tblCategoryDepartments WHERE intActive = 1 ORDER BY intOrder, txtDepartmentName"
	RSdepartments.Open SQLdepartments, Connection,  3,  1,  1 'open with this option (adOpenStatic, adLockReadOnly, adCmdText)
	
	' Get the count of the pages using the given page size
	intPageCount = RSdepartments.PageCount
	
	' If the request page falls outside the acceptable range,
	' give them the closest match (1 or max)
	If intPageCurrent > intPageCount Then intPageCurrent = intPageCount
	If intPageCurrent < 1 Then intPageCurrent = 1
	
	' Check page count to prevent bombing when zero results are returned!
	If intPageCount = 0 Then
		Response.Write "No records found!"
	Else
		' Move to the selected page
		RSdepartments.AbsolutePage = intPageCurrent
%>

<table style="width: 100%; border: 4px solid #444; background-color: #FFFFFF;" cellspacing="3" cellpadding="2">
    <tr style="background-color: ##444; width: 100%;">
        <td colspan="4"><span class="HomeScreenHeader">Select Department</span></td>
    </tr>
<%

		' Loop through our records and ouput 1 column per record
		intRecordsShown = 1 'CHANGED
		
		Do While intRecordsShown <= intPageSize And Not RSdepartments.EOF 'CHANGED
			If (intRecordsShown Mod 4) = 1 Then
				blnNeedTREOR = True
				Response.Write "<tr>" 
			End If
%>
        <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
            <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
            <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
        </td>
<%
			' Increment the number of records we've shown
			intRecordsShown = intRecordsShown + 1
			
			If (intRecordsShown Mod 4 = 0) Then
				blnNeedTREOR = False
				Response.Write "</tr>" 
			End If

			' Can't forget to move to the next record!
			RSdepartments.MoveNext
		Loop
		
		If blnNeedTREOR Then
			Response.Write "</tr>"
		End If
		
	' Show "previous" and "next" page links which pass the page to view
	' and any parameters needed to rebuild the query.  You could just as
	' easily use a form but you'll need to change the lines that read
	' the info back in at the top of the script.
		
%>
    <tr style="background-color: ##444; width: 100%; vertical-align: top;">
        <td colspan="4">
            <table style="width: 100%;">
                <tr>
                    <td style="width:10%;">
                        <% If intPageCurrent > 1 Then %>
                            <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%= intPageCurrent - 1 %>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_back.png"></a>
                        <% End If %>
                    </td>
                    <td style="width:90%; text-align: center;"><span class="HomeScreenHeader">Page <%=iPageCurrent%> of <%=iPageCount%></span></td>
                    <td style="width:10%;">
                        <% If intPageCurrent < intPageCount Then %>
	                        <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%= intPageCurrent + 1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_next.png"></a>
                        <% End If %>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<%
	End If
	
	' Close DB objects and free variables
	RSdepartments.Close
	Set RSdepartments = Nothing
	Connection.Close
	Set Connection = Nothing
%>

Open in new window

0
 
LVL 13

Expert Comment

by:khairil
ID: 36812679
Changes:

1. Line 11: New variables for creating new <TR>
2. Line 68: Change start counter to 1 instead of 0 before
3. Line 71 - 74: Creating <TR> dynamically for beginning every 4 records
4. Line 84 - 87: Creating closing </TR> dynamically after ending every 4 records
5. Line 93 - 95: Create closing </TR> when reach recordset EOF but record not yet in group of 4
0
 

Author Comment

by:net-workx
ID: 36814775
hi khairil,

Thanks for your help but we dont seem to be getting this resolved.  Ive written some code which is very basic to get the iterations correct, i have the following code so far.

This has to create a table which is no more than 4 cells wide, and only shows the amount of records upto intTotalRecordsShow value, i have also posted the output of the code so i need to make some adjustments.  Once this is done i can then use the same principle and add the other code back into it.


<%
intMaxRecordsPerRow = 4
intTotalRecordsShown = 16

Set RSdepartments = Server.CreateObject("ADODB.Recordset")
SQLdepartments = "SELECT intDepartmentID, txtDepartmentName, txtTillButtonBgColour FROM tblCategoryDepartments WHERE intActive = 1 ORDER BY intOrder, txtDepartmentName"
RSdepartments.Open SQLdepartments,Connection,3,3
%>

<table>
    <tr>
    <% 
    intRecordsPerRow = 0
    Do While Not RSdepartments.EOF
    IF intRecordsPerRow = intMaxRecordsPerRow THEN
    %>
    <tr>
    <% END IF %>
        <td style="border: 1px solid #000000;"><% =RSdepartments("txtDepartmentName") %></td>
    <% IF intRecordsPerRow = intMaxRecordsPerRow THEN %>
    </tr>
    <% 
    END IF

    intRecordsPerRow = intRecordsPerRow + 1
    RSdepartments.MoveNext
    Loop
    %>
</table>

<%
RSdepartments.Close
Set RSdepartments = Nothing
SQLdepartments = " "
%>

Open in new window

<table>
    <tr>
    
        <td style="border: 1px solid #000000;">All In One</td>
    
        <td style="border: 1px solid #000000;">Amino Acids</td>
    
        <td style="border: 1px solid #000000;">Creatine</td>
    
        <td style="border: 1px solid #000000;">Energy & Endurance</td>
    
    <tr>
    
        <td style="border: 1px solid #000000;">Joint Support</td>
    
    </tr>
    
        <td style="border: 1px solid #000000;">Meal Replacements</td>
    
        <td style="border: 1px solid #000000;">Mens Health</td>
    
        <td style="border: 1px solid #000000;">Nitric Oxide</td>
    
        <td style="border: 1px solid #000000;">Nutrition Bars</td>
    
        <td style="border: 1px solid #000000;">Post-Workout</td>
    
        <td style="border: 1px solid #000000;">Pre-Workout</td>
    
        <td style="border: 1px solid #000000;">Proteins</td>
    
        <td style="border: 1px solid #000000;">Ready-To-Drink</td>
    
        <td style="border: 1px solid #000000;">Testosterone Support</td>
    
        <td style="border: 1px solid #000000;">Training Stacks</td>
    
        <td style="border: 1px solid #000000;">Vitamins & Minerals</td>
    
        <td style="border: 1px solid #000000;">Weight Gain</td>
    
        <td style="border: 1px solid #000000;">Weight Loss</td>
    
        <td style="border: 1px solid #000000;">Womens Health</td>
    
        <td style="border: 1px solid #000000;">Test Department</td>
    
        <td style="border: 1px solid #000000;">x_Products</td>
    
        <td style="border: 1px solid #000000;">Department</td>
    
</table>

Open in new window

0
 
LVL 13

Expert Comment

by:khairil
ID: 36815920
Hi,

1. On line 13: You have to move "intRecordsPerRow = 0" to be between line 14 and 15.

2. On line 20: <% IF intRecordsPerRow = intMaxRecordsPerRow THEN %>, you also need to consider if  intRecordsPerRow not yet equal to 4 but the recordset is EOF when you trigger .MoveNext. Your record will be hanging without </tr> closing. You also cannot have written additional </tr> if intRecordsPerRow = 4 and recordset is EOF.

So you need to have flag, to acknowledge if </tr> already written or not. So just put one more variables between line 14 and 15, let say:
blnTRFlag = False

and improve line 20 so it be:

<% IF intRecordsPerRow = intMaxRecordsPerRow THEN
      blnTRFlag = True
%>

and add this line after "Loop" in line 27:

If Not blnTRFlag Then
    Reponse.write "</tr>"
End If
0
 

Author Comment

by:net-workx
ID: 36900845
Hi this is still not working - we dont seem to be getting anywhere, i have reverted to code below which is the one that gets me closest to what i need to get to.

This shows the records in the right format and in the correct width but it doesnt page properly, it should only have 6  records to look thorugh.
<%
'//checks for a variable for records per page, if not set then use default
If Request.QueryString("rpp") = "" Then
    intRecordspp = 6
Else
    intRecordspp = CInt(Request.QueryString("rpp"))
End If

'//checks for a variable page number, if none then defaults to page 1
If Request.QueryString("PageNumber") = "" Then
    intPageNum = 1
Else
    intPageNum = CInt(Request.QueryString("PageNumber"))
End If
%>

<%
Set RSdepartments = Server.CreateObject("ADODB.Recordset")
SQLdepartments = "SELECT intDepartmentID, txtDepartmentName, txtTillButtonBgColour FROM tblCategoryDepartments WHERE intActive = 1 ORDER BY intOrder, txtDepartmentName"
RSdepartments.Open SQLdepartments,Connection,3,3

'//SET THE AMOUNT OF PRODUCTS PER PAGE
RSdepartments.PageSize = intRecordspp

'//IF ITS NOT THE END OF THE RECORDSET THEN THE PAGE NUMBER IS SET TO QUERYSTRING VALUE
If Not RSdepartments.EOF Then RSdepartments.AbsolutePage = intPageNum
intTotalRecords = RSdepartments.RecordCount
intTotalPages = RSdepartments.PageCount
%>
<table style="width: 100%; border: 4px solid #444; background-color: #FFFFFF;" cellspacing="3" cellpadding="2">
    <tr style="background-color: ##444; width: 100%;">
        <td colspan="4"><span class="HomeScreenHeader">Select Department</span></td>
    </tr>
    <% 
    FOR strTemp = 1 to intRecordspp
        Do While Not RSdepartments.EOF
        %>
        <tr>
            <%
            intColumns = 4
            FOR i = 1 to intColumns
            %>
            <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
                <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
                <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
            </td>
            <%
            RSdepartments.MoveNext
            Next
            %>
        </tr>
        <%
        Loop
    Next
    %>
    <tr style="background-color: ##444; width: 100%; vertical-align: top;">
        <td colspan="4">
            <table style="width: 100%;">
                <tr>
                    <td style="width:10%;">
                        <% IF NOT intPageNum = 1 THEN %>
                            <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%=intPageNum -1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_back.png"></a>
                        <% END IF %>
                    </td>
                    <td style="width:90%; text-align: center;"><span class="HomeScreenHeader">Page <%=intPageNum%> of <%=intTotalPages%></span></td>
                    <td style="width:10%;">
                        <% IF intPageNum < intTotalPages THEN %>
	                        <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%=intPageNum +1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_next.png"></a>
                        <% END IF %>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<%
RSdepartments.Close
Set RSdepartments = Nothing
SQLdepartments = " "
%>

Open in new window

0
 
LVL 13

Expert Comment

by:khairil
ID: 36901666
Emm... I see you have slight different in requirement here.

You SHOULD NOT put

intTotalRecords = RSdepartments.RecordCount
intTotalPages = RSdepartments.PageCount

in the loop.

Futhermore RSdepartments.RecordCount will not always return true number of recordcount, you need to populate it first using recordset .MoveLast and .MoveFirst command before calling .Recordcount.

How many record you need to display right now? The upper code is set to 6 but the intColumns = 4. This will cause page formatting to displace. May be you should change intColumns = 6 too
0
 

Author Comment

by:net-workx
ID: 36952506
Sorry but the requirement has not changed at all.

The requirement is 4 ROWS OF 4 RECORDS = MAXIMUM 16 RECORDS.

This format absolutely cannot be changed.

It should look like this (where x is a record):
|X|X|X|X|
|X|X|X|X|
|X|X|X|X|
|X|X|X|X|

The requirement hasnt changed.
0
 
LVL 13

Accepted Solution

by:
khairil earned 500 total points
ID: 36953422
try this:

<%
'//checks for a variable for records per page, if not set then use default
If Request.QueryString("rpp") = "" Then
    intRecordspp = 16
Else
    intRecordspp = CInt(Request.QueryString("rpp"))
End If

'//checks for a variable page number, if none then defaults to page 1
If Request.QueryString("PageNumber") = "" Then
    intPageNum = 1
Else
    intPageNum = CInt(Request.QueryString("PageNumber"))
End If
%>

<%
Set RSdepartments = Server.CreateObject("ADODB.Recordset")
SQLdepartments = "SELECT intDepartmentID, txtDepartmentName, txtTillButtonBgColour FROM tblCategoryDepartments WHERE intActive = 1 ORDER BY intOrder, txtDepartmentName"
RSdepartments.Open SQLdepartments,Connection,3,3

'//SET THE AMOUNT OF PRODUCTS PER PAGE
RSdepartments.PageSize = intRecordspp

'//IF ITS NOT THE END OF THE RECORDSET THEN THE PAGE NUMBER IS SET TO QUERYSTRING VALUE
If Not RSdepartments.EOF Then RSdepartments.AbsolutePage = intPageNum
intTotalRecords = RSdepartments.RecordCount
intTotalPages = RSdepartments.PageCount
%>
<table style="width: 100%; border: 4px solid #444; background-color: #FFFFFF;" cellspacing="3" cellpadding="2">
    <tr style="background-color: ##444; width: 100%;">
        <td colspan="4"><span class="HomeScreenHeader">Select Department</span></td>
    </tr>
    <% 
    FOR strTemp = 1 to intRecordspp
        Do While Not RSdepartments.EOF
        %>
        <tr>
            <%
            IF strTemp Mod 4 = 1 Then
            	Response.write "<TR>"
            End If
            
            FOR i = 1 to intColumns
            %>
            <td class="ProductListing" style="background-color: #<%=RSdepartments("txtTillButtonBgColour") %>;">
                <%=GetDepartmentThumbnail(RSdepartments("intDepartmentID"),75,0,1,intQuoteID) %><br />
                <a class="ProductListing" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&DepartmentID=<% =RSdepartments("intDepartmentID") %>"><% =RSdepartments("txtDepartmentName") %></a>
            </td>
            <%
            RSdepartments.MoveNext
            Next

            IF strTemp Mod 4 = 0 Then
            	Response.write "</TR>"
            End If
            
            %>
        </tr>
        <%
        Loop
    Next
    %>
    <tr style="background-color: ##444; width: 100%; vertical-align: top;">
        <td colspan="4">
            <table style="width: 100%;">
                <tr>
                    <td style="width:10%;">
                        <% IF NOT intPageNum = 1 THEN %>
                            <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%=intPageNum -1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_back.png"></a>
                        <% END IF %>
                    </td>
                    <td style="width:90%; text-align: center;"><span class="HomeScreenHeader">Page <%=intPageNum%> of <%=intTotalPages%></span></td>
                    <td style="width:10%;">
                        <% IF intPageNum < intTotalPages THEN %>
	                        <a style="border: 0;" href="/admin/epos/till.asp?QuoteID=<%=intQuoteID%>&PageNumber=<%=intPageNum +1%>&rpp=<%= Request.QueryString("rpp")%>"><img src="/admin/epos/images/buttons/btn_next.png"></a>
                        <% END IF %>
                    </td>
                </tr>
            </table>
        </td>
    </tr>
</table>
<%
RSdepartments.Close
Set RSdepartments = Nothing
SQLdepartments = " "
%>

Open in new window

0
 

Author Comment

by:net-workx
ID: 37041360
This code causes the error:

Execution of the ASP page caused the Response Buffer to exceed its configured limit.
0
 
LVL 13

Expert Comment

by:khairil
ID: 37042394
at what line?
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Uploading files to the web server has become common part of almost any kind of web application. People use different technologies to solve this, but regardless of the technology used, it is always useful to have some kind of progress indicator shown…
Envision that you are chipping away at another e-business site with a team of pundit developers and designers. Everything seems, by all accounts, to be going easily.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to count occurrences of each item in an array.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now