Utility to Build VB Class to Match SQL Stored Procedures

Monday Oct 9th 2000 by Danny Lesandrini
Share:

SP2VB (Stored Proc To VB Class) is a Visual Basic utility that will create the class module code necessary to execute SQL Server Stored Procs with an ADO Command object. In addition to preparing the necessary ADO calls, the utility uses the SQLDMO and SQLNS libraries to query your SQL Server for the names and parameters of all the Stored Procs in your database.


What is SP2VB?

SP2VB (Stored Proc To VB Class) is a Visual Basic utility that will create the class module code necessary to execute SQL Server Stored Procs with an ADO Command object. In addition to preparing the necessary ADO calls, the utility uses the SQLDMO and SQLNS libraries to query your SQL Server for the names and parameters of all the Stored Procs in your database.


Why the Need for this Utility?

  • I hate to type!
  • I make mistakes when I type!
  • Typing long lists of parameters is boring!

What more need be said?

Executing a SQL Server Stored Proc from MS Access or from an ASP page is relatively trivial, but either way, you have to deal with the sometimes long list of input and output parameters. Even when parameters are few, you still want to keep your code consistent accross your entire application, employing a common naming scheme and including at least simple error handling.

Boring? Sometimes! But very necessary and always beneficial.

Accordingly, I came up with a plan to automate the task -- a simple code generator. The pages that follow will explain (at relatively high level) what the utility does and how you might modify the code to meet your specific needs. As usual, the source code is available for download so that you can test out the utility and adapt it as you please.

Before we get into the code itself, here's a screen shot of the main form. When we are finished, the tool will be able to perform the following functions:

  1. Connect to any SQL Server Database
  2. Optionally filter out System Stored Procs (Prefixed with dt_ or sp_)
  3. Optionally process only one user-specified procedure
  4. Assign the Data Type that the Function returns (Long or ADO Recordset)
  5. Display results so user can cut and paste into VB Class module

Future enhancements to this utility could include the following

  1. Automatically output results to VB Class Module
  2. Generate corresponding ASP Class Method execution code
  3. Automatically determine proper Data Type for Function return value


Page 2: Code the Utility



Code the Utility

As usual, I am loathe to trudge through what I call the "housekeeping" code. You know what I mean, code to initalize variables and test for nulls. It is necessary and is included in the source code, but I'll refrain from explaining each line of code here.

Instead, I'll focus on the 2 methods that are the most critical:

  • ConnectToDB()
  • CreateCodeFromStoredProcs()


Function: ConnectToDB()

The connection routine references objects from the SQLDMO (Distributed Manangement Objects) and SQLNS (SQL Namespace) libraries. We will be using Server, Database and Namespace objects in the code behind our main form, so the first thing we need to do is declare some form level variables.

    Private oSQLServer As SQLDMO.SQLServer
    Private oCurDB As SQLDMO.Database
    Private objSQLNSObj As SQLNamespaceObject
    Private objSQLNS As SQLNamespace
    Private hArray(10) As Long

Note: The following code was adapted from the Microsoft Sample code that ships with SQL Server. The code in these examples is an excellent source for developers wishing to learn more about how to manipulate the SQL Server DMO and NS objects from code.

If the user has supplied valid connection variables (Server, Database, User ID and Password), then an attempt is made to connect to a database by executing the following sub routine:

' **************** BEGIN CODE HERE ****************
Private Sub ConnectToDB()
    On Error Resume Next

    Dim i As Integer
    Dim strConnect As String
    
    Err.Clear
    Screen.MousePointer = vbHourglass
    
    ' Assume success
    fConnected = True

    ' Set SQLDMO object

    Set oSQLServer = New SQLDMO.SQLServer
    oSQLServer.LoginTimeout = 10
    oSQLServer.ODBCPrefix = False
    
    'Attempt a connection, then fill the properties stuff
    oSQLServer.ApplicationName = "SQL-DMO Explorer"
    oSQLServer.Connect strServer, strUID, strPWD
    oSQLServer.Application.GroupRegistrationServer = ""
    
    ' Set Current SQLDMO Database object
    Set oCurDB = oSQLServer.Databases(strDatabase)
        
    ' Create SQL Namespace Object
    Set objSQLNS = New SQLNamespace
    objSQLNS.Initialize "EMS Admin Namespace", SQLNSRootType_Server, _
                            "Server=" & strServer _
                          & ";UID=" & strUID _
                          & ";pwd=" & strPWD & ";", hWnd
    
    ' get a root object of type Server and walk down the hierarchy from there

    hArray(0) = objSQLNS.GetRootItem
    ' get first level server->databases
    hArray(1) = objSQLNS.GetFirstChildItem(hArray(0), SQLNSOBJECTTYPE_DATABASES)
    ' get second level server->databases->database('pubs')
    hArray(2) = objSQLNS.GetFirstChildItem(hArray(1), SQLNSOBJECTTYPE_DATABASE, strDatabase)
    ' get a SQLNamespaceObject to execute commands against on the wanted level
    Set objSQLNSObj = objSQLNS.GetSQLNamespaceObject(hArray(2))

    Screen.MousePointer = vbDefault

    If Err.Number > 0 Then fConnected = False

End Sub
' ***************** End CODE HERE *****************

If the connection is made successfully, then a flag, fConnected, is set to TRUE. This flag is then used to toggle the form command buttons accordingly so that the user may continue. (See screen shot)




Page 3: Function: CreateCodeFromStoredProcs()



Function: CreateCodeFromStoredProcs()

It's been my experience that it quickly becomes very confusing when writing code that generates other code. The full text of the code generation function is listed below and comments are interspersed to help the reader understand what's happening. Some of the key lines of code will be described in more detail below.

First, alter creating a StoredProcedure object from SQL DMO, you can loop through all procs by using a For Each loop with the Database object we created earlier. The For Each loop ensures that we will examine each stored proc of the database exactly once.

    Dim oSP As SQLDMO.StoredProcedure
    For Each oSP In oCurDB.StoredProcedures
       ' Do stuff

    Next

Next, the name of the Stored Proc is put into a variable. This variable is then examined to see if the current proc should or shouldn't be processed. Remember that the user may select one specific proc to process, or may filter out all System Stored Procs (those prefixed with "dt_" and "sp_").

If the current procedure should be processed, then it's necessary to query the StoredProcedure object to determine the names and types of Input/Output Parameters, if any exist. This is done by setting an SQLDMO QueryResults object to the return value of the EnumParameters method.

    Dim qryResults As SQLDMO.QueryResults
    Set qryResults = oSP.EnumParameters()

Now we have a SQLDMO QueryResults recordset populated with all the information we need to enumerate the parameters of the Stored Proc. If we printed the results of the recordset, it would look like this:
	name        name         length  colid   output
	---------   ---------    -----   -----   ----- 
	@TableName  varchar        75      1       0     
	@HowMany    int             4      2       0     
	@NextCount  int             4      3       1     

From this table, we can determine the Name of each parameter, its Data Type, Length and we can determine if it's an Input or Output variable. With that information in hand, we can create our VB Class method, which will ultimately take this form:
Public Function Exec_ems_GetNextCounters(ByVal strConnect As String,  _ 
        ByVal strTableName As String, ByVal lngHowMany As Long,  _ 
        ByRef lngNextCounter As Long ) As ADODB.Recordset


You will notice that I named the Function with the prefix "Exec_" and the name of the proc. This is my personal naming system. The code below can be edited to match your naming scheme. Variables are always passed ByVal and the first argument, strConnect, is always required (it's needed for the ADO Connection Object). You will notice that the remaining arguments match the parameters of the stored proc with one small difference. All VB arguments have a 3-letter prefix to indicate what data type is represented by the variable. This was done to avoid any potential problems in the VB Class where arguments are named with VB Keywords. (Since SQL Server prefixes parameters with "@" character, it is possible for users to create parameters that will cause a Keyword conflict in Visual Basic).

As we loop through each parameter and extract it's name into our local variable, we are building the various pieces of our output code. When we are all done processing parameters, the end result will look something like this:


    cnn.Open strConnect
    Set com.ActiveConnection = cnn

    With com

        .CommandText = "ems_GetNextCounters"
        .CommandType = adCmdStoredProc

        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , 0)
        .Parameters.Append .CreateParameter("@TableName", adVarChar, adParamInput, 75, strTableName)
        .Parameters.Append .CreateParameter("@HowMany", adInteger, adParamInput, 8, lngHowMany)
        .Parameters.Append .CreateParameter("@NextCounter", adInteger, adParamOutput, 8, lngNextCounter)

        Set rsLocal = .Execute(lngRecordsAffected)
        
        ' In order to return OUTPUT params, the recordset must be closed
        ' So, first save rsLocal to an XML File to reload later
        rsLocal.Save "Temp.xml", adPersistXML
        Set rsLocal = Nothing
        
        lngNextCounter = .Parameters("@NextCounter")

        Set Exec_ems_GetNextCounters = New ADODB.Recordset
        Exec_ems_GetNextCounters.Open "Temp.xml"
        Kill "Temp.xml"

        strSQL7Error = Err.Description

    End With

    Set com.ActiveConnection = Nothing
    Set cnn = Nothing


The function opens an ADO Connection and creates an ADO Command object. The RETURN_VALUE parameter is always included and maps to the SQL Server RETURN value (surprise!). Each of the other 3 parameters are appended to the ADO Command object, along with their direction (INPUT or OUTPUT). Next, the Command object is executed. The @@ROWCOUNT (or number of rows affected) is placed in the lngRecordsAffected variable and any error is placed in the strSQL7Error variable. These values are exposed as properties of the class.

Because of a problem with the ADO Command object, it is necessary to close the resultant recordset object prior to attempting to access a value for OUTPUT parameters. Accordingly, the recordset is persisted in a temporary XML file while the recordset is closed and the OUTPUT parameters populated. Once that operation is finished, the recordset is reloaded as the Return object of the function. (This doesn't apply if the user elected to return a Long variable from the function.)


Conclusion

As you can imagine, there are many details to the formation of the above code. As time and space do not permit me to give a detailed explanation of each line of code, I leave it to the reader to pursue the details to the extent you desire. The sample code will work for you so long as you have the SQL DMO and SQL NS libraries installed on your computer. According to Microsoft, this can be done by registering the redistributable libraries, but I've never been able to get that to work. The safest approach is to simply install SQL Server tools on your development machine. The odds are that if you are developing an application requiring SQL Server Stored Procs, you probably already have the tools installed and these libraries properly registered.

Utilities like this can be a great time saver. I thoroughly expect that some readers will immediately see ways to improve upon the above design. As I stated before, I welcome suggestions and ideas that will make this utility even more powerful and useful.

View Output

Full Source Code for CreateCodeFromStoredProcs() Function

' ****************** BEGIN CODE HERE ******************
Public Function CreateCodeFromStoredProcs() As String
On Error Resume Next

Dim oSP As SQLDMO.StoredProcedure
Dim qryResults As SQLDMO.QueryResults

Dim sUserRequestedSP As String
Dim sProcName As String
Dim fContinue As Boolean
Dim sPrefix As String

Dim sGenDeclare As String
Dim sInstrForUse As String

Dim sOpenADO As String
Dim sCloseADO As String
Dim sComment As String
Dim sDeclare As String
Dim sCreateRecordset As String
Dim sVBArguments As String
Dim sByValRef As String
Dim sAssignOUTPUTParam As String
Dim sAppendParams As String
Dim sFnReturnType As String
Dim sExecute As String
Dim sCodeText As String
Dim sReturn As String

Dim i As Integer
Const q = """"
Dim strMsg As String
Dim iLastLineContinuation As Integer

Dim sSQLParamName As String
Dim sVBVariableName As String

Dim sSQLParamType As String
Dim sVBVariableType As String
Dim sADOParamType As String

Dim sSQLParamSize As String
Dim sVBVariableSize As String
Dim sADOParamSize As String

Dim sSQLOutput As String
Dim sADOOutput As String
    
    ' Reset variables
    sComment = ""
    iProcsProcessed = 0
    fFoundProc = False
    
    sInstrForUse = "' ----- BEGIN ASP/ADO CODE USE EXAMPLE FOR THIS CLASS-----" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'  This code may be tweaked and pasted into your ASP Page to instantiate" & vbCrLf _
                 & "'  the class that calls your Stored Proc." & vbCrLf _
                 & "'  You will need to change the names of the DLL, Class and functions as " & vbCrLf _
                 & "'  well as the parameters for the connection string." & vbCrLf _
                 & "'" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'    Dim objDataCls" & vbCrLf _
                 & "'    Dim rstReturn" & vbCrLf _
                 & "'    Dim sConnect" & vbCrLf _
                 & "'    Dim lRecCount" & vbCrLf _
                 & "'    Dim sError" & vbCrLf & vbCrLf _
                 & "'    Set objDataClass = Server.CreateObject(" & q & "YourDLL.YourClass" & q & ")" & vbCrLf _
                 & "'    Set rstReturn = Server.CreateObject(" & q & "ADODB.Recordset" & q & ")" & vbCrLf _
                 & "'    sConnect = " & q & "Provider=SQLOLEDB.1;User ID=sa;PWD=;Initial Catalog=YourDB;Data Source=YourServer" & q & vbCrLf _
                 & "'    Set rstReturn = objDataCls.Exec_ems_MyProc(sConnect, Param_1, ... Param_n)" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'    lRecCount = objDataCls.RecordsAffected" & vbCrLf _
                 & "'    sError = objDataCls.SQLError" & vbCrLf _
                 & "'" & vbCrLf _
                 & "'    If Not rstReturn.BOF Then" & vbCrLf _
                 & "'        Do Until rstReturn.EOF ..." & vbCrLf _
                 & "'" & vbCrLf _
                 & "' ------------------- END CODE SAMPLE ------------------- " & vbCrLf & vbCrLf


    ' Set the variables that will determine what is returned

    ' by the function (based on user selection)
    If chkReturnRecordset = Checked Then
        sFnReturnType = "ADODB.Recordset"
        sCreateRecordset = vbCrLf & "Dim rsLocal As ADODB.Recordset"
    Else
        sFnReturnType = "Long"
        sCreateRecordset = ""
    End If

    
    ' Declare the RecordsAffected and SQL Error variables and create
    ' Property Get functions for them (They are Read-Only)
    sGenDeclare = "Option Explicit" & vbCrLf & vbCrLf & sInstrForUse _
                & "Private lngRecordsAffected As Long" & vbCrLf _
                & "Private strSQL7Error As String" & vbCrLf & vbCrLf _
                & "Property Get RecordsAffected() As String" & vbCrLf _
                & "    RecordsAffected = lngRecordsAffected" & vbCrLf _
                & "End Property" & vbCrLf & vbCrLf _
                & "Property Get SQLError() As String" & vbCrLf _
                & "    SQLError = strSQL7Error" & vbCrLf _
                & "End Property" & vbCrLf & vbCrLf

    ' Declare and Open ADO Connection and Command Objects

    sOpenADO = "On Error Resume Next" & vbCrLf & vbCrLf _
             & "Dim cnn As New ADODB.Connection" & vbCrLf _
             & "Dim com As New ADODB.Command" & sCreateRecordset & vbCrLf & vbCrLf _
             & "    cnn.Open strConnect" & vbCrLf _
             & "    Set com.ActiveConnection = cnn" & vbCrLf & vbCrLf _
             & "    With com" & vbCrLf & vbCrLf

    ' Close the ADO Connection and Command Objects

    sCloseADO = "        strSQL7Error = Err.Description" & vbCrLf & vbCrLf _
              & "    End With" & vbCrLf & vbCrLf _
              & "    Set com.ActiveConnection = Nothing" & vbCrLf _
              & "    Set cnn = Nothing" & vbCrLf & vbCrLf _
              & "End Function" & vbCrLf & vbCrLf
              
   
    ' Check to see if user has entered name of specific

    ' Stored Proc to process.  If so, save to variable
    If chkProcessOnlyOneSP = Checked Then
        sUserRequestedSP = txtStoredProcToProcess
    End If
    
    ' Loop through all Stored Procs, processing only
    ' those matching user defined filter.
    For Each oSP In oCurDB.StoredProcedures
        ' Reset flag   Assume Success
        fContinue = True
        
        ' Capture name of Current Stored Procedure
        sProcName = oSP.Name
        
        ' If the user has not entered the name of a specific
        ' Proc to process, AND has asked that System Procs be
        ' excluded, then test the Proc Name for Sys Prefix.

        If sUserRequestedSP = "" And chkFilter = Checked Then
            ' Capture the Proc 3-letter prefix
            sPrefix = Left(sProcName, 3)
            
            ' Toggle the Continue Flag OFF for Sys Prefix
            If sPrefix = "dt_" Or sPrefix = "sp_" Then fContinue = False
        End If
        
        ' Now toggle Continue Flag OFF is user selected specific proc ...
        If Len(sUserRequestedSP) Then
            ' ... but this one isn't it!
            If LCase(sUserRequestedSP) <> LCase(sProcName) Then fContinue = False
        End If
        
        If fContinue Then
            ' Set counter to indicate that number of procs
            ' processed.  (Will determine finish message.)
            fFoundProc = True
            iProcsProcessed = iProcsProcessed + 1
            lGetTickCountEnd = GetTickCount
            lTics = (lGetTickCountEnd - lGetTickCountStart) / 1000

            lblMsg.Caption = "Processing Proc Number " & iProcsProcessed & "  (Total of " & lTics & " seconds)"
            Me.Refresh
            
            sComment = "'//////////// Code for:  " & sProcName & " ////////////" & vbCrLf
            sComment = sComment & "'  Created on " & Date & vbCrLf
            sComment = sComment & "'  Created by Danny Lesandrini" & vbCrLf
            sComment = sComment & "'  Created for Dean Evans and Associates, Inc." & vbCrLf & "'" & vbCrLf
            sComment = sComment & "'  Parameters expected by this Stored Proc" & vbCrLf & "'  " & String(40, "-") & vbCrLf
            
            sDeclare = "Public Function Exec_" & sProcName & "(ByVal strConnect As String, "
                     
            sCodeText = "        .CommandText = " & q & sProcName & q & vbCrLf _
                      & "        .CommandType = adCmdStoredProc" & vbCrLf & vbCrLf _
                      & "        .Parameters.Append .CreateParameter(" & q & "RETURN_VALUE" & q & ", adInteger, adParamReturnValue, , 0)" & vbCrLf


            
            Set qryResults = oSP.EnumParameters()
            With qryResults
                For i = 1 To qryResults.Rows
                    ' Get Parameter and Variable Name

                    sSQLParamName = .GetColumnString(i, 1)
                    sSQLParamType = " " & .GetColumnString(i, 2)
                    sVBVariableName = GetVBPrefix(Trim(sSQLParamType)) & Mid(sSQLParamName, 2)
                    
                    ' Get Parameter and Variable (VB and ADO) Type
                    sVBVariableType = GetVBVariableType(sSQLParamType)
                    sADOParamType = GetADOParamType(sSQLParamType)
                    
                    ' Get Parameter and Variable (VB and ADO) Size
                    sSQLParamSize = ""
                    sVBVariableSize = .GetColumnString(i, 3)
                    If InStr(1, sSQLParamType, "char") Then sSQLParamSize = "(" & sVBVariableSize & ")"
                    sADOParamSize = GetADOParamSize(sSQLParamType, sVBVariableSize)
                    
                    ' Initialize Variables effected by INPUT/OUTPUT status
                    sSQLOutput = ""
                    sByValRef = "ByVal "
                    sADOOutput = ", adParamInput"
                    
                    ' Determine if Param Type is OUTPUT and reset variables accordingly

                    If .GetColumnString(i, 5) = 1 Then
                        sSQLOutput = " OUTPUT"
                        sADOOutput = ", adParamOutput"
                        sByValRef = "ByRef "
                        sAssignOUTPUTParam = sAssignOUTPUTParam & Space(8) & sVBVariableName & " = .Parameters(" & q & sSQLParamName & q & ")" & vbCrLf

                    End If
                    
                    sComment = sComment & "'    " & sSQLParamName & sSQLParamType & sSQLParamSize & sSQLOutput & vbCrLf
                    
                    ' To avoid the need to scroll right to read the entire Function Declaration

                    ' and Argument List, line continuations will be added ever 60 characters
                    iLastLineContinuation = LastInStr(sVBArguments, "_")
                    If Len(Mid(sVBArguments, iLastLineContinuation)) > 60 Then sVBArguments = sVBArguments & " _ " & vbCrLf & Space(8)
                    
                    sVBArguments = sVBArguments & sByValRef & sVBVariableName & sVBVariableType
                    sAppendParams = sAppendParams & "        .Parameters.Append .CreateParameter(" & q _
                                          & sSQLParamName & q & sADOParamType & sADOOutput _
                                          & sADOParamSize & sVBVariableName & ")" & vbCrLf
                Next
            End With
            
            sDeclare = sDeclare & sVBArguments
            sDeclare = Left(sDeclare, (Len(sDeclare) - 2)) & " ) As " & sFnReturnType & vbCrLf & vbCrLf
            

            ' There is a bug with ADO that requires a recordset be closed prior to
            ' attempting to read OUTPUT parameters.  Since it's not known if the SP
            ' contains a populated recordset, simply persist rsLocal as XML, read the
            ' OUTPUT params, and then reload the XML file into the Return Recordset
            
            If chkReturnRecordset = Checked Then
                sExecute = Space(8) & "Set rsLocal = .Execute(lngRecordsAffected)" & vbCrLf _
                         & Space(8) & "' In order to return OUTPUT params, the recordset must be closed" & vbCrLf _
                         & Space(8) & "' So, first save rsLocal to an XML File to reload later" & vbCrLf _
                         & Space(8) & "rsLocal.Save " & q & "Temp.xml" & q & ", adPersistXML" & vbCrLf _
                         & Space(8) & "Set rsLocal = Nothing" & vbCrLf & vbCrLf _
                         & Space(8) & "Set Exec_" & sProcName & " = New ADODB.Recordset" & vbCrLf & vbCrLf _
                         & Space(8) & "Exec_" & sProcName & ".Open " & q & "Temp.xml" & q & vbCrLf _
                         & Space(8) & "Kill " & q & "Temp.xml" & q & vbCrLf & vbCrLf
            Else
            ' If Return Type is not Recordset, then there are no problems accessing OUTPUT Params

                sExecute = "        .Execute(lngRecordsAffected)" & vbCrLf _
                         & "        Exec_" & sProcName & " = lngRecordsAffected" & vbCrLf
            End If
            
            ' Prepare strings for concatonation
            sCodeText = sCodeText & sAppendParams & vbCrLf & sExecute
            sComment = sComment & "'  " & String(40, "-") & vbCrLf & vbCrLf
            sAssignOUTPUTParam = sAssignOUTPUTParam & vbCrLf
            
            ' Return neatly formatted code text

            sReturn = sReturn & vbCrLf & sComment & sDeclare & sOpenADO & sCodeText & sAssignOUTPUTParam & sCloseADO
            
            ' Reset Variables
            sComment = ""
            sDeclare = ""
            sCodeText = ""
            sAppendParams = ""
            sExecute = ""
            sVBArguments = ""
            sAssignOUTPUTParam = vbCrLf
        End If
    Next
    
    Set oSP = Nothing
    
    Select Case iProcsProcessed
        Case 0
            strMsg = "Error.  No Procs Processed"
        Case 1
            strMsg = "Finished!  Procedure " & sUserRequestedSP & " was processed."
        Case Else
            strMsg = "Finished!  All " & iProcsProcessed & " have been processed."
    End Select
    
    lblMsg.Caption = strMsg
    Me.Refresh

    sReturn = sGenDeclare & sReturn
    CreateCodeFromStoredProcs = sReturn

End Function

' ****************** END CODE HERE ******************

Page 4: Utility Output for SP ems_GetNextCounters



Utility Output for SP ems_GetNextCounters

Here is a screen shot of the form that displays the results.
(The full text of the code follows.)



Click to Enlarge


Option Explicit


' ----- BEGIN ASP/ADO CODE USE EXAMPLE FOR THIS CLASS-----
'
'  This code may be tweaked and pasted into your ASP Page to instantiate
'  the class that calls your Stored Proc.
'  You will need to change the names of the DLL, Class and functions as 
'  well as the parameters for the connection string.
'
'
'    Dim objDataCls
'    Dim rstReturn
'    Dim sConnect
'    Dim lRecCount
'    Dim sError

'    Set objDataClass = Server.CreateObject("YourDLL.YourClass")
'    Set rstReturn = Server.CreateObject("ADODB.Recordset")
'    sConnect = "Provider=SQLOLEDB.1;User ID=sa;PWD=;Initial Catalog=YourDB;Data Source=YourServer"
'    Set rstReturn = objDataCls.Exec_ems_MyProc(sConnect, Param_1, ... Param_n)
'
'    lRecCount = objDataCls.RecordsAffected
'    sError = objDataCls.SQLError
'
'    If Not rstReturn.BOF Then
'        Do Until rstReturn.EOF ...
'
' ------------------- END CODE SAMPLE ------------------- 


Private lngRecordsAffected As Long
Private strSQL7Error As String

Property Get RecordsAffected() As String
    RecordsAffected = lngRecordsAffected
End Property

Property Get SQLError() As String
    SQLError = strSQL7Error
End Property



'//////////// Code for:  ems_GetNextCounters ////////////
'  Created on 10/6/2000
'  Created by Danny Lesandrini
'  Created for Dean Evans and Associates, Inc.
'
'  Parameters expected by this Stored Proc
'  ----------------------------------------
'    @TableName varchar(75)
'    @HowMany int
'    @NextCounter int OUTPUT
'  ----------------------------------------


Public Function Exec_ems_GetNextCounters( _ 
    ByVal strConnect As String, _ 
    ByVal TableName As String, _ 
    ByVal HowMany As Long, _ 
    ByVal  As Long ) As ADODB.Recordset

On Error Resume Next

Dim cnn As New ADODB.Connection
Dim com As New ADODB.Command

    cnn.Open strConnect
    Set com.ActiveConnection = cnn

    With com

        .CommandText = "ems_GetNextCounters"
        .CommandType = adCmdStoredProc

        .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, , 0)
        .Parameters.Append .CreateParameter("@TableName", adVarChar, adParamInput, 75, TableName)
        .Parameters.Append .CreateParameter("@HowMany", adInteger, adParamInput, 8, HowMany)
        .Parameters.Append .CreateParameter("@NextCounter", adInteger, adParamOutput)

        Set Exec_ems_GetNextCounters = .Execute(lngRecordsAffected)
        strSQL7Error = Err.Description

    End With

    Set com.ActiveConnection = Nothing
    Set cnn = Nothing

End Function



See All Articles by Columnist Danny Lesandrini


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved