problem returning a ADODB.Recordset from my DLL to VB app

I have a function in my VB DLL that works fine and is coded as such:

Public Function RetrieveStaffMember() As ADODB.Recordset

    Dim mRS As ADODB.Recordset

    mCmd.CommandText = "spab_RetrieveStaff"
    mCmd.CommandType = adCmdStoredProc
    mCmd.ActiveConnection = mConn
   
    mCmd.Parameters.Append mCmd.CreateParameter("@return", adInteger, adParamReturnValue)
    mCmd.Parameters.Append mCmd.CreateParameter("@staffmember", adInteger, adParamInput, 4)
   
    mCmd.Parameters("@staffmember") = mStaffFlag
   
    Set mRS = New ADODB.Recordset
    Set mRS = mCmd.Execute
    Set RetrieveStaffMember = mRS
       
    mRS.Close
    Set mRS = Nothing
   
    Set mCmd = Nothing
   
End Function


My code to call this in my VB code is here:

Set y = New samicsoft.Staff

y.StaffFlag = 0
Set mRS = New ADODB.Recordset
set mRS = y.RetrieveStaffMember

Do While Not mRS.EOF
    List1.AddItem mRS.Fields("lastname")
    mRS.MoveNext
Loop

mRS.Close
Set mRS = Nothing

set y = nothing

When I execute my code - it fails on the line:
set mRS = y.RetrieveStaffMember

With the error - object variable or with block variable not set. I cannot figure out. Can someone help me figure this out?

Thanks in advance.
LVL 13
samic400Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
{Very wild guess}
I'm assuming that mCmd is a module-level parameter, else there would be a 'Dim mCmd as ADODB.Command' in your code.
It is possible that when you execute this, any parameters associated with mCmd are still attached to mCmd, such that when your code adds the two parameters it is actually adding two MORE parameters.

Step through your code, and once it hits RetrieveStaffMember/before it adds parameters do a ?mCmd.Parameters.Count.  
If it's zero, you're okay.  If it's not, you'll have to wipe out the old parameters  (air code)

Dim x as Integer
For x = mCmd.Parameters.Count to 1 step -1
   mCmd.Parameters(x).Delete
Next

'The rest of your code goes here
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Also, you do have dimmed somewhere mCmd, mCon, and y?

>mCmd.Parameters.Append mCmd.CreateParameter("@return", adInteger, adParamReturnValue)
>mCmd.Parameters.Append mCmd.CreateParameter("@staffmember", adInteger, adParamInput, 4)
Also, not sure if this is a problem, but normally Input parameters are added before any output parameters, so perhaps these two lines should be reversed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Might not be a bad idea to make sure Option Explicit is at the top of this code module, then compile, to see if any object/variable names are misspelled.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

samic400Author Commented:
i purposely left out a lot of code - i have the option explicit at the top of both the DLL and client exe code. I have mCmd defined at the top of my DLL.

0
samic400Author Commented:
This is the client side:
Option Explicit


Private Sub Form_Load()

Dim x As samicsoft.Configuration
Dim y As samicsoft.Staff
Dim mRS As ADODB.Recordset

Set x = New samicsoft.Configuration

If x.ErrorByte = 1 Then
    MsgBox "Fatal error connecting to software: " & x.ErrorMessage, vbCritical, "Critical Error"
    Set x = Nothing
    Exit Sub
End If

Set y = New samicsoft.Staff

y.ConnectStaff

y.StaffFlag = 0
Set mRS = New ADODB.Recordset
Set mRS = y.RetrieveStaffMember

Do While Not mRS.EOF
    List1.AddItem mRS.Fields("lastname")
    mRS.MoveNext
Loop

mRS.Close
Set mRS = Nothing

y.DisconnectStaff

Set x = Nothing
Set y = Nothing

End Sub

This is the DLL side:
Option Explicit

Private mStaffFlag As Integer
Private mDSN As String
Private mUserid As String
Private mPassword As String

Private mConn As ADODB.Connection
Private mCmd As ADODB.Command


Public Function RetrieveStaffMember() As ADODB.Recordset

    Dim mRS As ADODB.Recordset

    mCmd.CommandText = "spab_RetrieveStaff"
    mCmd.CommandType = adCmdStoredProc
    mCmd.ActiveConnection = mConn
   
    mCmd.Parameters.Append mCmd.CreateParameter("@return", adInteger, adParamReturnValue)
    mCmd.Parameters.Append mCmd.CreateParameter("@staffmember", adInteger, adParamInput, 4)
   
    mCmd.Parameters("@staffmember") = mStaffFlag
   
    Set mRS = New ADODB.Recordset
    Set mRS = mCmd.Execute
    Set RetrieveStaffMember = mRS
       
    mRS.Close
    Set mRS = Nothing
   
    Set mCmd = Nothing
   
End Function


Public Property Get StaffFlag() As Variant

    StaffFlag = mStaffFlag

End Property

Public Property Let StaffFlag(ByVal vNewValue As Variant)

    mStaffFlag = vNewValue

End Property

Public Sub ConnectStaff()

   Set mConn = New ADODB.Connection
   mConn.Open DSN, Userid, Password

   
   
End Sub

Public Sub DisconnectStaff()

   Set mCmd = Nothing

   mConn.Close
   Set mConn = Nothing

End Sub
0
samic400Author Commented:
problem was I didn't have a mCmd = new ADODB.Command - your suggestion helped me find the problem. thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.