...

View Full Version : page pagination in asp



mivec
08-22-2005, 06:24 PM
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..... :(

nikkiH
08-22-2005, 07:23 PM
You set rowcount.
http://www.serverwatch.com/tutorials/article.php/1493401

oracleguy
08-22-2005, 08:44 PM
What you are trying to do is commonly called database paging. There are lots of ASP examples on the subject.

mivec
08-22-2005, 08:50 PM
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....

:(

ghell
08-22-2005, 09:41 PM
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"
pageno = 2
recordsperpage = 10

"select top " & recordsperpage & " * from posts where pstid not in (select top " & (recordsperpage * (pageno-1)) & " pstid from posts)"

oracleguy
08-22-2005, 10:21 PM
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.

ghell
08-22-2005, 10:25 PM
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..

oracleguy
08-22-2005, 10:27 PM
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.

ghell
08-22-2005, 10:54 PM
but is there anything for access or mssql that is an equivelant? (hes using access, im just interested in mssql for myself :o )

neocool00
08-23-2005, 02:42 PM
Since I've learned about .GetRows, I've been using it everywhere. Here is the code that I am using to do pagination with:

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.


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.

ghell
12-12-2008, 02:24 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum