• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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.
0
samic400
Asked:
samic400
  • 3
  • 3
1 Solution
 
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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now