SQL stored procedure returning unusual ado recordset 
Author Message
 SQL stored procedure returning unusual ado recordset

I'm running a SQL Stored procedure which "maufactures" a return value, which
I then put into a recordset.

Problem is, when I do this the recordset has unusual PageCount and
RecordCount values; -1, to be exact.  This is hurting me later in my VB 6
program.

The stored procedure (SQL 2000) reads like this:

--------------------------------------------------------------------




/*is this field editable?*/


GO
----------------------------------------------------------------------

I call the stored procedure and store the data in a recordset like so:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset

objRecordset.Open _
        "EXEC www_get_fld_restrictable " &
objInbound("lgItemID").FieldValue, _
        m_Connect, adOpenStatic
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

When I do this, "objRecordset" has the unusual properties I mentioned
earlier.  Obviously, because I'm returning manufactured data, and not
actual, "queried" values from a table, the recordset has some trouble
digesting it.

Is there anything I can do in the stored procedure to out accurate values
into the recordset's RecordCount and other properties?   Thanks for the
advice.



Sun, 04 Sep 2005 02:28:25 GMT  
 SQL stored procedure returning unusual ado recordset

Quote:
> Problem is, when I do this the recordset has unusual PageCount and
> RecordCount values; -1, to be exact.

http://www.aspfaq.com/2193

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.



Sun, 04 Sep 2005 03:08:18 GMT  
 SQL stored procedure returning unusual ado recordset
Thanks for the tip-- even though the article mentions forward-only cursors,
I assume the same problem is present with static cursors, such as I'm using
in the code snippet?



Quote:
> > Problem is, when I do this the recordset has unusual PageCount and
> > RecordCount values; -1, to be exact.

> http://www.aspfaq.com/2193

> --
> Aaron Bertrand, SQL Server MVP
> http://www.aspfaq.com/

> Please reply in the newsgroups, but if you absolutely
> must reply via e-mail, please take out the TRASH.



Sun, 04 Sep 2005 05:55:18 GMT  
 SQL stored procedure returning unusual ado recordset
Hi, Jim. HTH,
Luis Alonso

Private Sub Command1_Click()
Dim cnn1 As ADODB.Connection
Dim rsTemp As ADODB.Recordset
Dim stSelectSQL As String

    Set cnn1 = New ADODB.Connection
    cnn1.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;Initial Catalog=Pubs;Data Source=."

    Set rsTemp = New ADODB.Recordset

    rsTemp.CursorLocation = adUseClient
    stSelectSQL = "SELECT * FROM Sales"
    rsTemp.Open stSelectSQL, cnn1, adOpenDynamic, adLockOptimistic
    MsgBox "With adUseClient + adOpenDynamic, RecordCount = " &
CStr(rsTemp.RecordCount)

    rsTemp.Close
    rsTemp.CursorLocation = adUseClient
    stSelectSQL = "SELECT * FROM Sales"
    rsTemp.Open stSelectSQL, cnn1, adOpenStatic, adLockOptimistic
    MsgBox "With adUseClient + adOpenStatic, RecordCount = " &
CStr(rsTemp.RecordCount)

    rsTemp.Close
    rsTemp.CursorLocation = adUseServer
    stSelectSQL = "SELECT * FROM Sales"
    rsTemp.Open stSelectSQL, cnn1, adOpenDynamic, adLockOptimistic
    MsgBox "With adUseServer + adOpenDynamic, RecordCount = " &
CStr(rsTemp.RecordCount)

    Set rsTemp = Nothing
    Set cnn1 = Nothing
End Sub

"Jim Bancroft" <bobbygambles_at_nospam.msn.com> escribi en el mensaje

Quote:

> I'm running a SQL Stored procedure which "maufactures" a return value,
which
> I then put into a recordset.

> Problem is, when I do this the recordset has unusual PageCount and
> RecordCount values; -1, to be exact.  This is hurting me later in my VB 6
> program.

> The stored procedure (SQL 2000) reads like this:

> --------------------------------------------------------------------




yEditRights=1)
> /*is this field editable?*/


> GO
> ----------------------------------------------------------------------

> I call the stored procedure and store the data in a recordset like so:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Quote:
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
> Dim objRecordset As ADODB.Recordset
> Set objRecordset = New ADODB.Recordset

> objRecordset.Open _
>         "EXEC www_get_fld_restrictable " &
> objInbound("lgItemID").FieldValue, _
>         m_Connect, adOpenStatic

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

- Show quoted text -

Quote:
> '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

> When I do this, "objRecordset" has the unusual properties I mentioned
> earlier.  Obviously, because I'm returning manufactured data, and not
> actual, "queried" values from a table, the recordset has some trouble
> digesting it.

> Is there anything I can do in the stored procedure to out accurate values
> into the recordset's RecordCount and other properties?   Thanks for the
> advice.



Sun, 04 Sep 2005 07:24:02 GMT  
 SQL stored procedure returning unusual ado recordset
Did you try using

    , 1, 1

instead of

    , adOpenStatic

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:
> Thanks for the tip-- even though the article mentions forward-only
cursors,
> I assume the same problem is present with static cursors, such as I'm
using
> in the code snippet?



> > > Problem is, when I do this the recordset has unusual PageCount and
> > > RecordCount values; -1, to be exact.

> > http://www.aspfaq.com/2193

> > --
> > Aaron Bertrand, SQL Server MVP
> > http://www.aspfaq.com/

> > Please reply in the newsgroups, but if you absolutely
> > must reply via e-mail, please take out the TRASH.



Sun, 04 Sep 2005 22:26:04 GMT  
 SQL stored procedure returning unusual ado recordset


Quote:
> Did you try using

>     , 1, 1

> instead of

>     , adOpenStatic

Yes, I did.  Unfortunately, I still got a RecordCount value of -1.

I think the problem lies in my stored procedure.  The fact that I'm
manufacturing my return value, bPermission, instead of pulling it from a
table via a query is probably hurting me.



Mon, 05 Sep 2005 00:24:22 GMT  
 SQL stored procedure returning unusual ado recordset
That shouldn't be the problem.  However, why do you need a recordcount?  The
definition of your stored procedure makes it hard to imagine this will ever
be anything but exactly 1.  Just get the value and move on.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:


> > Did you try using

> >     , 1, 1

> > instead of

> >     , adOpenStatic

> Yes, I did.  Unfortunately, I still got a RecordCount value of -1.

> I think the problem lies in my stored procedure.  The fact that I'm
> manufacturing my return value, bPermission, instead of pulling it from a
> table via a query is probably hurting me.



Mon, 05 Sep 2005 00:29:25 GMT  
 SQL stored procedure returning unusual ado recordset



Quote:
> That shouldn't be the problem.  However, why do you need a recordcount?
The
> definition of your stored procedure makes it hard to imagine this will
ever
> be anything but exactly 1.  Just get the value and move on.

I appreciate your help, Aaron.

The reason I need the record count is because I have a component which
encapsulates an ADO recordset and generates XML from its contents.  If the
recordset's count is -1, my component won't run through the recordset
properly.  I can code around this problem, but I'd like to understand why
I'm getting a -1, and try to prevent it if possible.

Things work fine when my recordset opens stored procedures which run a query
against a database.  However, when I run a stored proc like the one outlined
in my original post, the recordset count isn't accurate, and it's strange
why that would be, since I am using a static cursor.



Mon, 05 Sep 2005 01:34:26 GMT  
 SQL stored procedure returning unusual ado recordset
Why do you need to rely on the recordcount property?  Define your own local
variable, set it to 1, and reference that.

I'd like to understand why you're getting -1 too, but I can't reproduce your
scenario, so I'm doing my best to help you overcome this.  I'm just having a
hard time getting past "I *HAVE* to use the recordcount property."  It makes
little sense when the answer is *ALWAYS* going to be the same.

--
Aaron Bertrand, SQL Server MVP
http://www.aspfaq.com/

Please reply in the newsgroups, but if you absolutely
must reply via e-mail, please take out the TRASH.


Quote:



> > That shouldn't be the problem.  However, why do you need a recordcount?
> The
> > definition of your stored procedure makes it hard to imagine this will
> ever
> > be anything but exactly 1.  Just get the value and move on.

> I appreciate your help, Aaron.

> The reason I need the record count is because I have a component which
> encapsulates an ADO recordset and generates XML from its contents.  If the
> recordset's count is -1, my component won't run through the recordset
> properly.  I can code around this problem, but I'd like to understand why
> I'm getting a -1, and try to prevent it if possible.

> Things work fine when my recordset opens stored procedures which run a
query
> against a database.  However, when I run a stored proc like the one
outlined
> in my original post, the recordset count isn't accurate, and it's strange
> why that would be, since I am using a static cursor.



Mon, 05 Sep 2005 02:34:15 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Opening an ADO recordset against a dataset returned from a SQL Server Stored Procedure

2. ado recordsets from stored procedures, stored procedures have input parameters

3. ADO or DataEnvironment COMMAND Using Store procedure can't return recordset

4. ADO or DataEnvironment COMMAND Using Store procedure can't return recordset

5. How to return Parameters and Recordset from Oracle Stored Procedure with ADO

6. Sql server stored procedures not returning a recordset...?

7. Returning a recordset from SQL Server stored procedure

8. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

9. HELP!!!!, using ADO + SQL 2000 + Stored procedures does not return a value

10. MS ADO / Sybase SQL Anywhere ASA 7 Stored Procedure Return Value Problem

11. Return recordset from Oracle Stored Procedure to VB.

12. Recordsets not returned from stored procedures

 

 
Powered by phpBB® Forum Software