|
 |
asp_databases thread: RecordSet Paging while using stored procedure
Message #1 by Aftab Ahmad <aftab.ahmad@k...> on Wed, 18 Apr 2001 11:37:47 +0200
|
|
Hei
Please help me.
I am trying to use recordset paging while using stored procedure. Here is
code:
Set objCmd
Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn '
that I have already declared
objCmd.CommandText = "{CALL
dbo.foretak_liste (" & request("ForetakType") & ")}"
objCmd.CommandType = 1
Set objRS2
Server.CreateObject("ADODB.Recordset")
objRS2.CursorLocation = 3 'adUseClient
objRS2.CursorType = 3
'adOpenStatic
Set objRS2 = objCmd.Execute
Set objCmd= Nothing
objRS2.PageSize = 10
objRS2.CacheSize = objRS2.PageSize
PageCount = objRS2.PageCount
RecordCount = objRS2.RecordCount
response.write RecordCount &"<br>"
response.write PageCount &"<br>"
response.End
The values that returned by response.write RecordCount and response.write
PageCount are -1
Any help, sample code or URL.
Thanks
Aftab
Message #2 by "Charles Feduke" <webmaster@r...> on Wed, 18 Apr 2001 08:38:03 -0400
|
|
Not sure if this will help, but you can reduce the amount of code [/moving
parts] with the following:
Const adOpenStatic = 3
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open "CALL dbo.foretak_liste (" & Request("ForetakType") & ")",
objConn, adOpenStatic
Response.Write objRS2.RecordCount
See if you get something other than -1 (you should unless your stored proc
isn't returning anything!), then work from there. If you need more help,
please provide what RDBMS you are working with.
? Chuck
> -----Original Message-----
> From: Aftab Ahmad [mailto:aftab.ahmad@k...]
> Sent: Wednesday, April 18, 2001 5:38 AM
> To: ASP Databases
> Subject: [asp_databases] RecordSet Paging while using stored procedure
>
>
> Hei
>
> Please help me.
>
> I am trying to use recordset paging while using stored procedure. Here is
> code:
>
> Set objCmd
> Server.CreateObject("ADODB.Command")
> Set objCmd.ActiveConnection = objConn '
> that I have already declared
> objCmd.CommandText = "{CALL
> dbo.foretak_liste (" & request("ForetakType") & ")}"
> objCmd.CommandType = 1
>
>
> Set objRS2
> Server.CreateObject("ADODB.Recordset")
> objRS2.CursorLocation = 3 'adUseClient
> objRS2.CursorType = 3
> 'adOpenStatic
>
> Set objRS2 = objCmd.Execute
> Set objCmd= Nothing
>
> objRS2.PageSize = 10
> objRS2.CacheSize = objRS2.PageSize
> PageCount = objRS2.PageCount
> RecordCount = objRS2.RecordCount
>
> response.write RecordCount &"<br>"
> response.write PageCount &"<br>"
> response.End
>
>
> The values that returned by response.write RecordCount and response.write
> PageCount are -1
> Any help, sample code or URL.
>
> Thanks
> Aftab
>
Message #3 by Jason Greenfeld-Unitek <jason.greenfeld@u...> on Wed, 18 Apr 2001 08:51:42 -0400
|
|
Here is some code that I use on one of my sites:
dim pgSize '--- Number of records per page
dim pgNum '--- Current Page
dim pgCount '--- Total number of pages
pgSize = 2
intAct = Request.QueryString("act")
pgNum = Request("pgNum")
if intAct = "" then intAct = 1
if pgNum = "" then pgNum = 1
if pgNum <= 0 then pgNum = 1
set rs = Server.CreateObject("ADODB.Recordset")
rs.PageSize = pgSize
rs.CacheSize = pgSize
rs.CursorLocation = adUseClient
rs.Open "GetContacts " &
intAct,conn,adOpenStatic,adLockReadOnly,adCmdText 'This calls a SP that gets
all contacts
pgCount = rs.PageCount
rs.ActiveConnection = nothing
if rs.EOF and rs.BOF then
Response.Write "<p align=center font color=red face=arial
size=4><b>No Records Returned from Database</b></font></p>"
else
if cint(pgNum) > cint(pgCount) then pgNum = pgCount
if cint(pgNum) < 1 then cint(pgNum) = 1
rs.AbsolutePage = pgNum
intShow = 0 '
do while cint(intShow) < cint(pgSize) and not rs.eof
' loop through the date you want to display
intShow = cint(intShow) + 1
rs.MoveNext
loop
if cint(pgNum) > 1 then ' if the current page is not the
first page, then display a link to show the previous page
Response.Write "<a href=""view.asp?pgNum=" &
cint(pgNum)-1 & "&act=" & intAct & """><b><< Previous</b></a>
end if
Response.Write "Page <input name=""pgNum"" value=""" & pgNum
& """ size=3> out of " & pgCount
if cint(pgNum) <> cint(pgCount) then 'if the current page is
not the last page, then display a link to show the next page
Response.Write " <a
href=""view.asp?pgNum=" & cint(pgNum)+1 & "&act=" & intAct & """><b>
Next >></b></a> "
end if
I know this may be a little hard to read. If you have any questions, you can
feel free to e-mail me directly.
Good Luck,
----------------------------------------------------
Jason A. Greenfeld
Application Developer
Unitek Technical Services
-----Original Message-----
From: Aftab Ahmad [mailto:aftab.ahmad@k...]
Sent: Wednesday, April 18, 2001 5:38 AM
To: ASP Databases
Subject: [asp_databases] RecordSet Paging while using stored procedure
Hei
Please help me.
I am trying to use recordset paging while using stored procedure. Here is
code:
Set objCmd
Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn '
that I have already declared
objCmd.CommandText = "{CALL
dbo.foretak_liste (" & request("ForetakType") & ")}"
objCmd.CommandType = 1
Set objRS2
Server.CreateObject("ADODB.Recordset")
objRS2.CursorLocation = 3 'adUseClient
objRS2.CursorType = 3
'adOpenStatic
Set objRS2 = objCmd.Execute
Set objCmd= Nothing
objRS2.PageSize = 10
objRS2.CacheSize = objRS2.PageSize
PageCount = objRS2.PageCount
RecordCount = objRS2.RecordCount
response.write RecordCount &"<br>"
response.write PageCount &"<br>"
response.End
The values that returned by response.write RecordCount and response.write
PageCount are -1
Any help, sample code or URL.
Thanks
Aftab
Message #4 by "Ken Schaefer" <ken@a...> on Wed, 18 Apr 2001 23:08:33 +1000
|
|
Are you sure that your recordset contains *any* records at all?
Try this:
If objRS.EOF then
Response.Write("No records")
Else
Response.Write("We have records")
End if
If you get the former written to the screen I think you need to look at your
proc...
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Hei
:
: Please help me.
:
: I am trying to use recordset paging while using stored procedure. Here is
: code:
:
: Set objCmd
: Server.CreateObject("ADODB.Command")
: Set objCmd.ActiveConnection = objConn '
: that I have already declared
: objCmd.CommandText = "{CALL
: dbo.foretak_liste (" & request("ForetakType") & ")}"
: objCmd.CommandType = 1
:
:
: Set objRS2
: Server.CreateObject("ADODB.Recordset")
: objRS2.CursorLocation = 3 'adUseClient
: objRS2.CursorType = 3
: 'adOpenStatic
:
: Set objRS2 = objCmd.Execute
: Set objCmd= Nothing
:
: objRS2.PageSize = 10
: objRS2.CacheSize = objRS2.PageSize
: PageCount = objRS2.PageCount
: RecordCount = objRS2.RecordCount
:
: response.write RecordCount &"<br>"
: response.write PageCount &"<br>"
: response.End
:
:
: The values that returned by response.write RecordCount and response.write
: PageCount are -1
: Any help, sample code or URL.
:
: Thanks
: Aftab
:
Message #5 by "Ken Schaefer" <ken@a...> on Wed, 18 Apr 2001 23:09:32 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: Not sure if this will help, but you can reduce the amount of code [/moving
: parts] with the following:
:
: Const adOpenStatic = 3
: Set objRS2 = Server.CreateObject("ADODB.Recordset")
: objRS2.Open "CALL dbo.foretak_liste (" & Request("ForetakType") & ")",
: objConn, adOpenStatic
: Response.Write objRS2.RecordCount
:
: See if you get something other than -1 (you should unless your stored proc
: isn't returning anything!), then work from there. If you need more help,
: please provide what RDBMS you are working with.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
He's using client-side cursors (adUseClient), which are always
adOpenStatic...
Cheers
Ken
|
|
|
View Cart
