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.

--
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.

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

    , 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.

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.

--
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.

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.

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.