cursor type

Hi there,
Can anyone tell me what's happening here?
I have the following code snippet that opens a recordset using some stored
procedure that returns some rows.
Dim rst as new ADODB.recordset
Dim Count as long
'rst.CursorType = adOpenStatic
rst.Open "Execute my_storedprocedure" & ItemID, _
m_con, adOpenStatic, adLockReadOnly
Count =rst.Recordcount
I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
out that after opening the recordset, rst.CursorType = 0 (adOpenForwardOnly)
again. I tried setting rst.CursorType = adOpenStatic before specifically
before opening the recordset but it didn't help. It will still be reset to
adOpenForwardOnly after it's open. I think that's why RecordCount
returns -1.
Many thanks.
SusanPerhaps this will help:
http://www.sqlteam.com/item.asp?ItemID=11842
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||I found out that this only happens when I use "EXEC my_storedprocedure" to
open a recordset. If I use embedded sql to open a recordset, e.g.
rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
then it will returns the RecordCount fine.
But how do I work around that? I still like to use stored procedure though.
Thanks,
Susan
"Susan" <xxx> wrote in message news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> Can anyone tell me what's happening here?
> I have the following code snippet that opens a recordset using some stored
> procedure that returns some rows.
>
> Dim rst as new ADODB.recordset
> Dim Count as long
> 'rst.CursorType = adOpenStatic
> rst.Open "Execute my_storedprocedure" & ItemID, _
> m_con, adOpenStatic, adLockReadOnly
> Count =rst.Recordcount
> I check that rst.EOF = false. Yet rst.Recordcount returns -1. I also found
> out that after opening the recordset, rst.CursorType = 0
> (adOpenForwardOnly) again. I tried setting rst.CursorType = adOpenStatic
> before specifically before opening the recordset but it didn't help. It
> will still be reset to adOpenForwardOnly after it's open. I think that's
> why RecordCount returns -1.
> Many thanks.
> Susan
>|||Could you clarify that you are using SET NOCOUNT ON within you stored
procedure?
Do you have any PRINT statetments withing your stored procedure?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
2000 - www.ciquery.com/articles/useofindexes.asp
"Susan" <xxx> wrote in message
news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> I found out that this only happens when I use "EXEC my_storedprocedure" to
> open a recordset. If I use embedded sql to open a recordset, e.g.
> rst.Open "SELECT * FROM Products", m_con, adOpenStatic, adLockReadOnly
> then it will returns the RecordCount fine.
> But how do I work around that? I still like to use stored procedure
though.
> Thanks,
> Susan
> "Susan" <xxx> wrote in message
news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
stored
found
adOpenStatic
>|||I suspected that too but no, I don't have SET NOCOUNT ON or Print statement.
Actually I found out sortly that if I set the connection's cursor location
to adUseClinet
m_con.CursorLocation = adUseClient
then it will return the RecordCount just fine. Does this mean that if I use
a stored procedure to open a recordset, then I need to specifically set
adUseClient to get a recordset other than a firehose forward only recordset?
But adUseServer is fine if I use embedded sql statement to open a recordset?
Susan
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
> Could you clarify that you are using SET NOCOUNT ON within you stored
> procedure?
> Do you have any PRINT statetments withing your stored procedure?
>
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> New article by Jack Vamvas - Improper Use of indexes on MS SQL: Server
> 2000 - www.ciquery.com/articles/useofindexes.asp
> "Susan" <xxx> wrote in message
> news:uy0B%232bMGHA.2580@.TK2MSFTNGP14.phx.gbl...
> though.
> news:u7JCuPbMGHA.140@.TK2MSFTNGP12.phx.gbl...
> stored
> found
> adOpenStatic
>|||I thought that all ADO recordsets returned from a stored procedure are
client side.
"Susan" <xxx> wrote in message news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
>I suspected that too but no, I don't have SET NOCOUNT ON or Print
>statement.
> Actually I found out sortly that if I set the connection's cursor location
> to adUseClinet
> m_con.CursorLocation = adUseClient
> then it will return the RecordCount just fine. Does this mean that if I
> use a stored procedure to open a recordset, then I need to specifically
> set adUseClient to get a recordset other than a firehose forward only
> recordset? But adUseServer is fine if I use embedded sql statement to open
> a recordset?
> Susan
>
> "Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
> news:dsv9cr$p82$1@.nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...
>|||No, you can open a server-side cursor on a stored procedure. What's
difficult is opening a server-side scrollable cursor that supports
RecordCount on a stored procedure:
http://groups.google.com/group/micr.../>
aef2?hl=en&
To the OP, I hope you take to heart the advice in that thread to use a less
expensive way to count your records.
Bob Barrows
JT wrote:
> I thought that all ADO recordsets returned from a stored procedure are
> client side.
> "Susan" <xxx> wrote in message
> news:OXjHa8lMGHA.3708@.TK2MSFTNGP09.phx.gbl...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.