Using Paging with a stored procedure

Results 1 to 2 of 2

Thread: Using Paging with a stored procedure

  1. #1
    Join Date
    Dec 1969

    Default Using Paging with a stored procedure

    I am trying to use the standard ADO PageSize, AbsolutePage, and PageCount methods of the ADODB.Recordset and it works great when sending straight SQL, but when I convert to a SP, it does not seem to page. I also don&#039t get any error messages.<BR><BR>Help - any sample code using SQL 7.0, Win2000, MDAC 2.5 to use SP AND paging?

  2. #2
    Nathen Guest

    Default RE: Using Paging with a stored procedure

    It&#039s possible to do. This sample creates a disconnected recordset with paging capabilities using a stored procedure.<BR><BR>&#039 ====================================<BR>&#039 ========== ADO Constants ===========<BR>&#039 ====================================<BR>Const adOpenForwardOnly = 0<BR>Const adLockReadOnly = 1<BR>Const adUseClient = 3<BR>Const adCmdStoredProc = 4<BR>Const adVarChar = 200<BR>Const adParamInput = 1<BR><BR>&#039 ===========================================<BR>&#0 39 ========== ADO Paging Constants ===========<BR>&#039 ===========================================<BR>Con st NUM_PER_PAGE = 10<BR><BR>Dim sSP, oConn, oRS, oCmd<BR> <BR>Set oConn = Server.CreateObject("ADODB.Connection")<BR>Set oRS = Server.CreateObject("ADODB.Recordset")<BR>Set oCmd = Server.CreateObject("ADODB.Command")<BR><BR>sSP = "sp_StoredProc"<BR> <BR>&#039 Set number of records per page<BR>oRS.CacheSize = NUM_PER_PAGE<BR> <BR>conn.Provider = "sqloledb"<BR>conn.Properties("Data Source").Value = DATABASE_SERVER<BR>conn.Properties("Initial Catalog").Value = DATABASE_NAME<BR>conn.Properties("User ID").Value = DATABASE_LOGIN<BR>conn.Properties("Password").Valu e = DATABASE_PASSWORD<BR>conn.Open<BR><BR>oCmd.ActiveC onnection = oConn<BR>oCmd.CommandText = sSP<BR>oCmd.CommandType = adCmdStoredProc<BR><BR>oCmd.Parameters.Append _<BR>&nbsp;&nbsp;&nbsp;oCmd.CreateParameter("@Para m",adVarChar,adParamInput,50,"Value")<BR><BR>oRS.C ursorLocation = adUseClient<BR><BR>&#039 Execute the query for read-only<BR>oRS.Open oCmd,,adOpenForwardOnly,adLockReadOnly<BR> <BR>&#039 Disconnect Recordset<BR>Set oCmd.ActiveConnection = Nothing<BR>Set oCmd = Nothing<BR>Set oRS.ActiveConnection = Nothing<BR>Set oConn = Nothing<BR> <BR>&#039 Set additional paging properties<BR>oRS.PageSize = NUM_PER_PAGE <BR>TotalPages = oRS.PageCount <BR>oRS.AbsolutePage = CurPage<BR> <BR>If Not oRS.EOF Then <BR>&nbsp;&nbsp;&nbsp;&#039 Process Recordset <BR>End If<BR> <BR>Set oRS = Nothing<BR><BR><BR>

Posting Permissions

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