Re: multi recordset error?

jerry.xuddd wrote on Thu, 30 Jun 2005 06:02:04 -0700:

> I have a stored procedure to return two tables. (so two recordsets)
>
> The thing is that when no records in the first table, I will get
> recordCount -1 for both two returned tables.
> When I added some records in the the first table and then the code works
> fine.
>
> So what is the problem? Is there some configuration values I should set? I
> already had SET NOCOUNT ON in the stored procedure.
>
> I use vb and ado 2.5.
>
> The simple code I use is:
> Set objRecordset = New ADODB.Recordset
>
> objRecordset.CursorType = adOpenStatic
> objRecordset.Open "SP_SYN_AD", objConn, adOpenStatic, adLockReadOnly,
> adCmdStoredProc
>
> read data
>
> move to next recordset
>
> ....
>


Have you tried setting the CursorLocation to adUseClient? A server side
cursor will often result in a -1 for the recordcount (which is why I always
check for BOF and EOF rather than rely on recordcount).

Also there's no need for you to specify the cursortype property and then
repeat it in the open statement (however this won't impact anything, it's
just makes for slightly more manageable code to only use it in one place or
else you might have issues in the future should you decide to change the
cursortype value and forget to change it in both places).

Dan


.

Relevant Pages

  • Re: rs.PageCount cannot function
    ... Before open, using stored procedure executed via dynamic sql, cursortype = 3 ... Before open, using stored procedure executed via Command object, cursortype ... So, even though it's giving us the "right" cursortype, the recordcount is ... This email account is my spam trap so I ...
    (microsoft.public.inetserver.asp.db)
  • Re: Embedded SQL within VBA?
    ... If you can't MoveLast, then the provider you are using probably defaults to using an "adOpenForwardOnly" cursortype (you can use the CursorType parameter of the Open method to try to specify the CursorType, but the provider may change the type if it doesn't support the one you asked for. ... If you have an adOpenForwardONly cursor type, try changing it to any of the others - if you need to be able to update the recordset, use adOpenDynamic or adOpenKeyset, otherwise you may be able to use adOpenStatic. ... figure out why .RecordCount is returning -1 here? ...
    (microsoft.public.word.vba.general)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)
  • Re: RecordCount error
    ... Set cursorType to adOpenStatic and see if that helps. ... RecordCount returns -1 ... Microsoft MVP Scripting and ADSI ... rsRecCount = rsPatient.RecordCount ...
    (microsoft.public.vb.database.ado)
  • Re: Check on empty record set in VBA
    ... But RecordCount is returned from Static as well as KeySet Cursor ... value we set the CursorType, when we open a recordset using ... Dim c As ADODB.Connection ... ..Open "SELECT * FROM FFDBATransactions", ...
    (comp.databases.ms-access)