asked on

calling a vb6 record set in a function

I'm using VB6 and sql server 2008.

I created a sample app using a function call.
It works fine and looks like this. Gets the some of some integers.
So the function i'm using returns an integer.

This is my working code

Option Explicit

'declare variables
Dim varfirstnumber As Integer
Dim varsecondnumber As Integer
Dim varanswer As Integer
Dim theanswer As Integer

' the click event for the Get Sum textbox
Private Sub btnGetSum_Click()

    'get value of the 1st number text box and store it in a variable
    varfirstnumber = txtNumber1.Text

    'get value of the 2nd number text box and store it in a variable
    varsecondnumber = txtNumber2.Text

    ' sum numbers and store into variable
    'varanswer = varfirstnumber + varsecondnumber

    'Call the Function - pass the 2 arguments to the function called GetAnswer
    'the result is stored in a variable called the answer
    theanswer = GetAnswer(varfirstnumber, varsecondnumber)

    'display answer in label
    lblAnswerTextValue.Caption = theanswer

    'display answer in message box
    MsgBox "The answer is: " & theanswer
    Exit Sub

End Sub

' A function named FutureValue that requires 2 arguments
Private Function GetAnswer(Param1Number1 As Integer, Param2Number2 As Integer) As Integer

    GetAnswer = (Param1Number1 + Param2Number2)

End Function

' On Form Load event
Private Sub Form_Load()

    ' set label on form load - good
    'lblAnswerTextValue.Caption = 1

End Sub

Open in new window

In my example above the function returns an integer.

If I setup a function that calls a sql server stored procedure and than puts the table returned by the stored procedure into a record set.

What it the syntax for my function so that it returns the recordset?

Then what is the syntaxt to call that function which is returning the recordset?

Anyone know?

Norie

Perhaps something like this dummy function which takes ID as an argument.

Function GetRecordsByID(lngID As Long) As ADODB.Recordset
Dim tmp As ADODB.Recordset
Dim strSQL As String

    Set tmp = New ADODB.Recordset
    strSQL = "SELECT * FROM [MyTable] WHERE [ID]=" & lngID

    ' code to connect to database and return recordset tmp
    
    Set GetRecordsByID = tmp

End Function

Open in new window

You would call that something like this.

Dim rstData As ADODB.Recordset

    Set rstData = GetRecordsByID(133)

    ' do stuff with recordset, e.g. populate userform

Open in new window

ASKER CERTIFIED SOLUTION

Chris Watson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maqskywalker

ASKER

Hi experts,

I created 2 simple examples to illustrate my issue. I'll just call them Example 1 and Example 2.
Both of these examples use the Employees Table from the Northwind Sql Server database.

Both of these examples do the exact same thing.
The difference is that in example 2 I put the code to call the stored procedure inside a function.
You type an EmployeeID in the textbox and then the button click event feeds that employeeid to a stored procedure which returns the records for that employee.
Then the name of the employee is displayed in a message box.

Here is my code for the Stored Procedure called GetEmployeeByID

USE [Northwind]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetEmployeeByID]
	-- parameter
	@EmployeeID as varchar(50)

AS
BEGIN					

SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
FROM [Northwind].[dbo].[Employees]
WHERE [EmployeeID] = @EmployeeID 

END

GO

Open in new window

When you feed the stored procedure an EmployeeID of 1 it returns this:

When you feed the stored procedure an EmployeeID of 2 it returns this:

Example 1

Example 1 works just fine. There is no issue with this example.

This is my code for example 1:

Option Explicit
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconnect As String
Dim VarEmployeeID As Long

Private Sub btnGetEmployee_Click()

    ' get value in EmployeeID textbox and save in variable
    VarEmployeeID = txtEmployeeID.Text

    cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    ' Name of Stored Procedure
    cmd.CommandText = "GetEmployeeByID"
    ' Sproc parameter name - Name of variable containing value passed to sproct parameter
    cmd.Parameters.Append cmd.CreateParameter("EmployeeID", adVarChar, adParamInput, 6, VarEmployeeID)

    rs = cmd.Execute

    'Reference the record set fields directly rs field (2) is last name rs field (1) is FirstName
    MsgBox("Employee First Name is: " & rs.Fields(2) _
        & vbCrLf & "Employee Last Name is: " & rs.Fields(1), vbOKOnly, "Northwind Traders")

    cmd.ActiveConnection = Nothing

End Sub

' on form load event
Private Sub Form_Load()

    '******* Connection 1 *******
    ' Connection String
    ' provider - sql server - database name
    strconnect = "Provider=SQLOLEDB;Data Source=johnsSqlServer;Initial Catalog=Northwind"
    ' Open Connection
    ' username and password
    con.Open(strconnect, "myusername", "mypassword")
    '****************************

End Sub

Open in new window

When I run Example 1 and I type 1 in the textbox and press the button I get this:

When I run Example 1 and I type 2 in the textbox and press the button I get this:

Example 2

Example 2 is the example I'm getting an error on.

This is my code for example 2. Notice how it's essentially the same code.
Except I just put the working connection string and stored procedure call from example 1 inside a function.

Option Explicit
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconnect As String
Dim VarEmployeeID As Long

Private Sub btnGetEmployee_Click()

    ' get value in EmployeeID textbox and save in variable
    VarEmployeeID = txtEmployeeID.Text

    'Call the Function - pass the argument to the function called GetAnswer
    'the result is stored in a variable called the answer
    rs = GetEmployeeByID(VarEmployeeID)

    'Set rs = cmd.Execute

    'Reference the record set fields directly rs field (2) is last name rs field (1) is FirstName
    MsgBox("Employee First Name is: " & rs.Fields(2) _
        & vbCrLf & "Employee Last Name is: " & rs.Fields(1), vbOKOnly, "Northwind Traders")

    cmd.ActiveConnection = Nothing

End Sub

' A function named GetEmployeeByID that requires 1 argument
Private Function GetEmployeeByID(TheEmployeeID As Long) As ADODB.Recordset

    '******* Connection 1 *******
    ' Connection String
    ' provider - sql server - database name
    strconnect = "Provider=SQLOLEDB;Data Source=johnsSqlServer;Initial Catalog=Northwind"
    ' Open Connection
    ' username and password
    con.Open(strconnect, "myusername", "mypassword")
    '****************************

    cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    ' Name of Stored Procedure
    cmd.CommandText = "GetEmployeeByID"
    ' Sproc parameter name - Name of variable containing value passed to sproct parameter
    cmd.Parameters.Append cmd.CreateParameter("EmployeeID", adVarChar, adParamInput, 6, VarEmployeeID)

    rs = cmd.Execute

    'GetEmployeeByID = rs

End Function

' on form load event
Private Sub Form_Load()


End Sub

Open in new window

When I run Example 2 it runs, but then after I enter an EmployeeID in the textbox and click the button I get this error:

I stops on this line of code

I think I'm making an error in the way i'm calling the record set inside the function or the way i'm calling the function and recordset in the button click event.

Anyone know what's wrong with the way I setup my stored procedure call and recordset call inside my function?

Norie

Why are you using a stored procedure?

Chris Watson

Uncomment line 49.

maqskywalker

ASKER

I'm using a stored procedure because I'm trying to learn how to do it with a stored procedure.

When I uncomment line 49 it gives this error on that line

Chris Watson

Oh, you're missing a Set keyword.

Set GetEmployeeByID = rs

maqskywalker

ASKER

Chris I added Set and that worked! Thanks!

So this is my working code.

Option Explicit
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim strconnect As String
Dim VarEmployeeID As Long

Private Sub btnGetEmployee_Click()

    ' get value in EmployeeID textbox and save in variable
    VarEmployeeID = txtEmployeeID.Text

    'Call the Function - pass the argument to the function called GetAnswer
    'the result is stored in a variable called the answer
    Set rs = GetEmployeeByID(VarEmployeeID)
     
    'Reference the record set fields directly rs field (2) is last name rs field (1) is FirstName
    MsgBox "Employee First Name is: " & rs.Fields(2) _
        & vbCrLf & "Employee Last Name is: " & rs.Fields(1), vbOKOnly, "Northwind Traders"
    
    'Set cmd.ActiveConnection = Nothing

End Sub

' A function named GetEmployeeByID that requires 1 argument
Private Function GetEmployeeByID(TheEmployeeID As Long) As ADODB.Recordset

    '******* Connection 1 *******
    ' Connection String
    ' provider - sql server - database name
    strconnect = "Provider=SQLOLEDB;Data Source=johnssqlserver;Initial Catalog=Northwind"
    ' Open Connection
    ' username and password
    con.Open strconnect, "myusername", "mypassword"
    '****************************

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = con
    cmd.CommandType = adCmdStoredProc
    ' Name of Stored Procedure
    cmd.CommandText = "GetEmployeeByID"
    ' Sproc parameter name - Name of variable containing value passed to sproct parameter
    cmd.Parameters.Append cmd.CreateParameter("EmployeeID", adVarChar, adParamInput, 6, VarEmployeeID)

    Set rs = cmd.Execute

    Set GetEmployeeByID = rs

End Function

' on form load event
Private Sub Form_Load()
    
End Sub

Open in new window

But I noticed an issue.

When I first run the program when I type in the EmployeeID in the textbox and click the button it works fine.
But if I type in another EmployeeID and then click again I get an error on this line:

con.Open(strconnect, "myusername", "mypassword")

Do i have to close the connection after each lookup somehow?

maqskywalker

ASKER

thanks that worked