Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Feb 2004
    Location
    Malaysia
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    page pagination in asp

    hi all,
    is it possible if someone can guide me on the logic on HOW to create a pagination in asp where it loops the db to retrieve the records but not all in one page where it shows only 5-6 records in one page.........really lost here.....
    Warm Regards,
    Mivec

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    What you are trying to do is commonly called database paging. There are lots of ASP examples on the subject.
    OracleGuy

  • #4
    Regular Coder
    Join Date
    Feb 2004
    Location
    Malaysia
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oracleguy
    What you are trying to do is commonly called database paging. There are lots of ASP examples on the subject.
    thanks for the reply oracle guy....but could u show me some?....i hv searched but i totally dun understand squat from it....sigh....

    Warm Regards,
    Mivec

  • #5
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    is there any way to use something similar to TOP 10 in sql that gets for example records 20 - 30 that match?

    this is all i can suggest at the moment but it wud be better if it was something like "select top(20, 30) from posts"
    Code:
    pageno = 2
    recordsperpage = 10
    
    "select top " & recordsperpage  & " * from posts where pstid not in (select top " & (recordsperpage * (pageno-1)) & " pstid from posts)"
    Last edited by ghell; 08-22-2005 at 11:00 PM.

  • #6
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Here is one example: http://www.asp101.com/samples/db_paging.asp

    There are others but that is the first one I could think of.
    OracleGuy

  • #7
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    i was just trying to return less data but i have a feeling that the where clause probably makes it a worse method .. someone said something about mysql uses limit (10, 20) or whatever to me but i duno if this has an equivilent..

  • #8
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Yeah, I've done paging using MySQL and using the LIMIT command it works really well and only return the rows you are actually going to display.
    OracleGuy

  • #9
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    but is there anything for access or mssql that is an equivelant? (hes using access, im just interested in mssql for myself )

  • #10
    Regular Coder
    Join Date
    Sep 2004
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Since I've learned about .GetRows, I've been using it everywhere. Here is the code that I am using to do pagination with:
    Code:
    IF UCASE(Request.ServerVariables("REQUEST_METHOD")) = "POST" Then
    	Dim qryCounter
    	Dim startPos
    	Dim endPos
    	Dim showCount	    
    	Dim sortCol
    	Dim intMax
    	    
    	'Number of records to show
    	showCount = Request.QueryString("count")
    	IF cint(showCount) < 20 Then
    		showCount = 20
    	END IF
    		
    	'Start with the record number
    	startPos = Request.QueryString("start")
    	IF cint(startPos) < 1 Then
    		startPos = 1
    	END IF
    		
    	'End with this record number
    	endPos = startPos + showCount - 1
    
    	'our sort column
    	sortCol = LCase(Trim(Request.QueryString("sort")))
    	        
    	'Initial query string
    	strSQL = "SELECT " & tblRx_Benefit_xref & ".SRC_PLATFORM_CD, " & tblRx_Benefit_xref & ".SRC_RX_PLAN_ID, " & _
    					tblRx_Benefit_xref & ".RX_BENEFIT_KEY, " & tblRx_Benefit_xref & ".ISSUE_STATE_CD, " & tblRx_Benefit_xref & _
    					".ORACLE_FIN_MKT_NBR, " & tblRx_Benefit & ".COVERAGE_CD, " & tblRx_Benefit & ".DEDUCTIBLE_ID, " & _
    					tblRx_Benefit & ".BENEFIT_PLAN_YR_CD FROM " & tblRx_Benefit_xref & " INNER JOIN " & tblRx_Benefit & " ON " & _
    					tblRx_Benefit_xref & ".RX_BENEFIT_KEY = " & tblRx_Benefit & ".RX_BENEFIT_KEY WHERE 1=1 "
    
    	'Define column constants
    	CONST cSRC_PLATFORM_CD = 0
    	CONST cSRC_RX_PLAN_ID = 1
    	CONST cRX_BENEFIT_KEY = 2
    	CONST cISSUE_STATE_CD = 3
    	CONST cORACLE_FIN_MKT_NBR = 4
    	CONST cCOVERAGE_CD = 5
    	CONST cDEDUCTIBLE_ID = 6
    	CONST cBENEFIT_PLAN_YR_CD = 7
    
    	'Add search
    	IF src_platform_cd <> "" Then
    		strSQL = strSQL & "AND " & tblRx_Benefit_xref & ".src_platform_cd = '" & src_platform_cd & "' "
    	END IF
    	IF src_rx_plan_id <> "" Then
    		If bolDB2 Then
    			strSQL = strSQL & "AND UCASE(" & tblRx_Benefit_xref & ".src_rx_plan_id) LIKE '" & UCase(src_rx_plan_id) & "%' "
    		Else
    			strSQL = strSQL & "AND " & tblRx_Benefit_xref & ".src_rx_plan_id LIKE '" & src_rx_plan_id & "%' "
    		End If
    	END IF
    	IF issue_state_cd <> "" Then
    		If Len(issue_state_cd) < 2 Then
    			strSQL = strSQL & "AND (" & tblRx_Benefit_xref & ".issue_state_cd) = '" & issue_state_cd & "' OR  " & tblRx_Benefit_xref & ".issue_state_cd = '0" & issue_state_cd & "') "
    		Else
    			strSQL = strSQL & "AND " & tblRx_Benefit_xref & ".issue_state_cd = '" & issue_state_cd & "' "
    		End If
    	END IF
    	IF oracle_fin_mkt_nbr <> "" Then
    		strSQL = strSQL & "AND " & tblRx_Benefit_xref & ".oracle_fin_mkt_nbr = " & oracle_fin_mkt_nbr & " "
    	END IF
    	IF deductible_id <> "" Then
    		If bolDB2 Then
    			strSQL = strSQL & "AND UCASE(" & tblRx_Benefit & ".deductible_id) = '" & UCase(deductible_id) & "' "
    		Else
    			strSQL = strSQL & "AND " & tblRx_Benefit & ".deductible_id = '" & deductible_id & "' "
    		End If
    	END IF
    	IF coverage_cd1 <> "" and coverage_cd2 <> "" Then
    		If bolDB2 Then
    			strSQL = strSQL & "AND UCASE(" & tblRx_Benefit & ".coverage_cd) = '" & UCase(coverage_cd) & "' "
    		Else
    			strSQL = strSQL & "AND " & tblRx_Benefit & ".coverage_cd = '" & coverage_cd & "' "
    		End If
    	Elseif coverage_cd1 <> "" and coverage_cd2 = "" Then
    		If bolDB2 Then
    			strSQL = strSQL & "AND UCASE(" & tblRx_Benefit & ".coverage_cd) LIKE '" & UCase(coverage_cd1) & "%' "
    		Else
    			strSQL = strSQL & "AND " & tblRx_Benefit & ".coverage_cd LIKE '" & coverage_cd1 & "%' "
    		End If
    	Elseif coverage_cd2 <> "" and coverage_cd1 = "" Then
    		If bolDB2 Then
    			strSQL = strSQL & "AND UCASE(" & tblRx_Benefit & ".coverage_cd) LIKE '%" & UCase(coverage_cd2) & "' "
    		Else
    			strSQL = strSQL & "AND " & tblRx_Benefit & ".coverage_cd LIKE '%" & coverage_cd2 & "' "
    		End If
    	END IF
    		
    	Dim qryCount
    	qryCount = Replace(strSQL, "" & tblRx_Benefit_xref & ".SRC_PLATFORM_CD, " & tblRx_Benefit_xref & ".SRC_RX_PLAN_ID, " & tblRx_Benefit_xref & ".RX_BENEFIT_KEY, " & tblRx_Benefit_xref & ".ISSUE_STATE_CD, " & tblRx_Benefit_xref & ".ORACLE_FIN_MKT_NBR, " & tblRx_Benefit & ".COVERAGE_CD, " & tblRx_Benefit & ".DEDUCTIBLE_ID, " & tblRx_Benefit & ".BENEFIT_PLAN_YR_CD", "COUNT(" & tblRx_Benefit_xref & ".SRC_PLATFORM_CD)")
    
    	'Add sorting to query
    	IF sortCol = "platform" Then
    		If bolDB2 Then
    			strSQL = strSQL & " ORDER BY LCASE(" & tblRx_Benefit_xref & ".SRC_PLATFORM_CD)"
    		Else
    			strSQL = strSQL & " ORDER BY " & tblRx_Benefit_xref & ".SRC_PLATFORM_CD"
    		End If
    	ELSEIF sortCol = "srcrxplan" Then
    		If bolDB2 Then
    			strSQL = strSQL & " ORDER BY LCASE(" & tblRx_Benefit_xref & ".SRC_RX_PLAN_ID)"
    		Else
    			strSQL = strSQL & " ORDER BY " & tblRx_Benefit_xref & ".SRC_RX_PLAN_ID"
    		End If
    	ELSEIF sortCol = "coverage" Then
    		If bolDB2 Then
    			strSQL = strSQL & " ORDER BY LCASE(" & tblRx_Benefit & ".COVERAGE_CD)"
    		Else
    			strSQL = strSQL & " ORDER BY " & tblRx_Benefit & ".COVERAGE_CD"
    		End If
    	ELSEIF sortCol = "deductible" Then
    		If bolDB2 Then
    			strSQL = strSQL & " ORDER BY LCASE(" & tblRx_Benefit & ".DEDUCTIBLE_ID)"
    		Else
    			strSQL = strSQL & " ORDER BY " & tblRx_Benefit & ".DEDUCTIBLE_ID"
    		End If
    	ELSE
    		' DEFAULT SORT ORDER
    		If bolDB2 Then
    			strSQL = strSQL & " ORDER BY LCASE(" & tblRx_Benefit_xref & ".SRC_PLATFORM_CD)"
    		Else
    			strSQL = strSQL & " ORDER BY " & tblRx_Benefit_xref & ".SRC_PLATFORM_CD"
    		End If
    	END IF
    
    	'set how many records to return
    	If bolDB2 Then
    		strSQL = strSQL & " FETCH FIRST " & endPos & " ROWS ONLY"
    	Else
    		strSQL = Replace(strSQL, "SELECT", "SELECT TOP " & endPos)
    	End If
    
    	'Fetch data
    	Dim aryResults
    	aryResults = ReturnData(strSQL)
    	If NOT IsArray(aryResults) Then
    		Response.Write "<br><br><div align=center><b>No records found</b><br><br>  Enter new search criteria above"
    		If intSecurityLevel < intSecurityViewer Then Response.Write " or <a href=""benefitadd.asp"">Add New Link</a>"
    		Response.Write ".</div>"
    	Else 
    		Dim aryCounter
    		aryCounter = ReturnData(qryCount)
    		If IsArray(aryCounter) Then
    			intMax = aryCounter(0,0)
    		Else
    			intMax = 0
    		End If
    		IF endPos >= intMax Then
    			endPos = intMax
    		END IF
    ...Table header code....
    			For iRow = (startPos-1) To (endPos-1)
    ...Display table rows of results...
    			Next
    Functions used on an include page.
    Code:
    Function ReturnData(strQuery)
    	Dim aryRS
    	OpenConnection
    	If bolDB2 Then
    		Set objRS = objDB2.Execute(CStr(strQuery))
    	Else
    		Set objRS = objDataUtil.ExecuteSQL(strSQLConnection, CStr(strQuery))
    	End If
    	If NOT objRS.EOF AND NOT objRS.BOF Then aryRS = objRS.GetRows
    	CloseConnection
    	ReturnData = aryRS
    End Function
    *Note, bolDB2 is a boolean to determine if I am hitting a DB2 database (which is our production & qa instance) or SQL server (test/development). Due to the nature of our enviornment, we have a custom COM+ object to handle sql interaction (hence the objDataUtil object).

    I rewrote someone else's code and they had original done what was nikki had pointed to. But if the user doesn't search on anything, 11,000+ records would be returned, only to have the first 20 displayed. So I changed it to use GetRows and just pulled the top x rows. I then make an additional sql call to retieve the max number of records. Obviously, as users page through the results, the page takes longer to load because you start pulling back more records, but with the sorting ability, I believe they will find what they are looking for upfront instead of having to loop through numerous pages.

  • #11
    Senior Coder
    Join Date
    Apr 2003
    Location
    England
    Posts
    1,192
    Thanks
    5
    Thanked 13 Times in 13 Posts
    Quote Originally Posted by adrevol View Post
    check out this URL,

    No need to worry about the programming, it is simple if you know how to query the resultset


    link removed

    1. get the total number of row count
    2. execute the required page ( check the URL for the query )
    3. process the result set
    4. put page navigation in your style
    I don't know why you are giving out help for a thread that is 3 years old but it's not good to use the (broken) query given in that link. Creating a whole temporary table just to add a row number to a result set is ridiculous and would have serious performance issues on a large database. MSSQL 2005 and above (since you are restricting it to MSSQL 2005 and above anyway with that query) could do better with the "WITH" statement in SQL but really generating an entire temporary table is not a good idea for such a simple problem and even if you were going to do it that way with row_number() there are better ways to do it.
    Last edited by JohnDubya; 12-12-2008 at 05:27 PM. Reason: link removed


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •