SQL Server Stored Procedure from VBA, recordset issue

dougfosterNYC
dougfosterNYC used Ask the Experts™
on
This is really odd.  I am using a command object to run a stored procedure with an input value.  I think it is working, but in the end, I get a recordset that has no records but isn't being trapped by the "if not (ors.bof and ors.eof) then" statement.  The recordcount is -1, so no records, but both .bof and .eof are false.

The stored procedure is simple, and works if I run it by itself:
SELECT      tng_format
FROM          dbo.tblLookupTime
WHERE      (Time_Period_default = @theInput)
Public Function getUSPSingleRec(sUSP As String, vInput As Variant) As Variant
    Dim sParam As String
    
    Dim oRS As New ADODB.Recordset
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Dim oConn As ADODB.Connection
    
    If Not OpenSQLConn() Then GoTo exitMe
    
    getUSPSingleRec = Null
    
    With cmd
       .ActiveConnection = goConn
       .CommandType = adCmdStoredProc
       .CommandText = sUSP
       .Parameters.Append cmd.CreateParameter("theInput", adVarChar, adParamInput, 50, "08:15A-08:30A MON-SUN")
       Set oRS = .Execute
    End With
 
    'assume there is only one record
    If Not (oRS.BOF And oRS.EOF) Then
        oRS.MoveFirst
        getUSPSingleRec = oRS.Fields(0).Value
    End If
    
 
exitMe:
    If oRS.State = adStateOpen Then oRS.Close
    Call CloseSQLConn
    Set oRS = Nothing
    Set cmd = Nothing
 
 
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
It is not odd at all.  The Command Execute method produces the best (most performant) cursor available: a fire-hose (read-only/forward-only) cursor.  The drawback is that it does not support the RecordCount property.  Solution:  Do not use the RecordCount property.  You do not need it.
Top Expert 2012
Commented:
Also, don't use MoveFirst either (remember this is a Forward Only cursor).  Again you do not need it.

Author

Commented:
I have worked with forward only recordsets, but what I think confused me is that the cursor isn't pointed at bof, it comes back on the first record.  

I took out the movefirst and it works just fine.  Thanks.  

But I didn't expect the recordset cursor to be returned set at the first record.  That is what confused me.

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
He could have explained a little bit more and been a bit less snippy.  But he was correct....
Top Expert 2012

Commented:
That is the way it is designed to function.
Top Expert 2012

Commented:
>>He could have explained a little bit more and been a bit less snippy. But he was correct....<<
Would you care to elaborate?

Author

Commented:
Maybe snippy isn't  the word, I just took it that way when you said "It's not odd at all".  It's hard to judge one's tone in writing.  I kind of translated it to "you idiot".  

But stranger things have happened as far as knowledge gaps on my part on seemingly simple issues.

Thanks again for your quick help.
Top Expert 2012

Commented:
>> It's hard to judge one's tone in writing.<<
Then don't.  This is an imperfect medium at best.   We volunteer our time to help you.  That is the important part to remember here.  Just remember that next time you need help, I may think twice about helping you.

It is your call.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial