Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    7

    Question Unanswered: Generic Function for Passing Recordsets

    I've been searching for days in various forums and references for a way to do this, but just can't get it to work. I've read some very insightful posts in this forum, so I'm hoping someone here can at least point me in the right direction.

    What I'm trying to do is create one central Function that calls a SQL stored procedure (with and without parameters) and returns a recordset. I want to be able to call this function from any form and only specify the call statement (hence general). In this way, a user can click a button, which in turn calls the Function with a parameter that includes the stored procedure name and stored procedure parameters, then returns the recordset.

    I've been able to do this with 'Action' queries that don't return recordsets, using the following function:

    Code:
    Public Function ExecuteQuery(vParam) As Boolean
    On Error GoTo ErrorHandler
    
    Dim SP_Conn As New ADODB.Connection, cmdExecQry As New ADODB.Command
    Dim vCTCall As String
    
    SP_Conn.ConnectionString = sqlConnect
    SP_Conn.Open sqlConn, sqlUID, sqlPWD
    
    vCTCall = vParam
    
    cmdExecQry.ActiveConnection = SP_Conn
    cmdExecQry.CommandText = vCTCall
    cmdExecQry.CommandType = adCmdText
    cmdExecQry.CommandTimeout = 15
    
    cmdExecQry.Execute
    SP_Conn.Close
    ExecuteQuery = True
    
    ErrorHandler:
        If Not SP_Conn Is Nothing Then
            If SP_Conn.State = adStateOpen Then SP_Conn.Close
        End If
        
        If Err <> 0 Then
            ExecuteQuery = False
            MsgBox Err.Source & "-->" & Err.Description, , "Error"
        End If
    End Function
    In this piece of code, sqlConnect, sqlConn, sqlUID, and sqlPWD are global variables and are set at Startup. They contain the connection information.

    vParam is a string built and passed by the form to the function. vParam includes a stored procedure call and any parameters for the stored procedure. It would look something like this for a stored procedure called 'stp_Add_Employee' and an employee with a Badge Number of '12345':

    Code:
    {call stp_Add_Employee(12345)}
    Unfortunately, I haven't had any luck with this kind of strategy when the stored procedure returns a recordset. I did read one post from pootle flump in http://www.dbforums.com/showthread.php?t=1205361 that does something similar to populate a drop down list, but it didn't include the code for the function that returns the recordset.

    Can anyone give me a hand or point me in the right direction? Any help is much appreciated!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You'll probably kick yourself when you see example code.

    BTW -
    Quote Originally Posted by Akenmaat
    Code:
    {call stp_Add_Employee(12345)}
    Shouldn't that be more like:
    Code:
    EXEC stp_Add_Employee 12345
    ??

    Anyway - some code (obviously you can add arguments to make it more flexible):
    Code:
    Public Enum DBType
        SQLServer = 0
        MSAccess = 1
    End Enum
     
    Public Enum ADORstState
        RstHasNoRecords = 0
        RstHasRecords = 1
    End Enum
     
    Public Function GetADORst(DatabaseType As DBType, strSQl As String, DatabaseName As String, ByRef RecordsetReturns As ADORstState, Optional Server As String, _
        Optional Uid As String, Optional Pwd As String) As ADODB.Recordset
    On Error GoTo GetADORst_Error
     
        Dim adoCon As ADODB.Connection
     
        If DatabaseType = SQLServer Then
     
            Set adoCon = OpenAdoSQLServerConn(Server, DatabaseName, Uid, Pwd)
     
        ElseIf DatabaseType = MSAccess Then
     
            Set adoCon = OpenAdoMSAccessConn(DatabaseName)
     
        Else
     
            MsgBox "Invalid DatbaseType enumerate", vbInformation, "Invalid argument"
            Exit Function
     
        End If
     
        Set GetADORst = New ADODB.Recordset
     
        GetADORst.CursorLocation = adUseClient
     
        GetADORst.Open strSQl, adoCon, adOpenStatic, adLockReadOnly
     
        GetADORst.ActiveConnection = Nothing
     
    On Error Resume Next
     
        GetADORst.MoveFirst
        GetADORst.MoveNext
        GetADORst.MoveFirst
     
        If GetADORst.EOF And GetADORst.BOF Then
     
            RecordsetReturns = RstHasNoRecords
     
        Else
     
            RecordsetReturns = RstHasRecords
     
        End If
     
    GetADORst_Exit:
    On Error Resume Next
        adoCon.Close
        Set adoCon = Nothing
     
        Exit Function
     
    GetADORst_Error:
     
        MsgBox "There has been an error creating the recordset.", vbInformation, "Recordset Error"
        Resume GetADORst_Exit
     
    End Function
    The test for EOF and BOF is very clumsy - I seem to recall I had some problem or other and didn't have time to really nail it. Forgot about my bodge till now...

    Anyway - HTH (it certainly contributes to my ambitions for a giga post count )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    7
    Quote Originally Posted by pootle flump
    You'll probably kick yourself when you see example code.
    You're dead right on that one!

    I've used just about every combination but that one. But it's now working marvelously.

    Thanks a ton!

    Quote Originally Posted by pootle flump
    BTW -

    Shouldn't that be more like:
    Code:
    EXEC stp_Add_Employee 12345
    ??
    Actually, I started out in that direction, but ran accross this statement in my searching...

    Quote Originally Posted by M$ OLE DB Provider for SQL Server
    Stored Procedures
    When executing a SQL Server stored procedure using a SQLOLEDB command, use the ODBC procedure call escape sequence in the command text. SQLOLEDB then uses the remote procedure call mechanism of SQL Server to optimize command processing. For example, the following ODBC SQL statement is the preferred command text over the Transact-SQL form:

    ODBC SQL
    {call SalesByCategory('Produce', '1995')}
    Transact-SQL
    EXECUTE SalesByCategory 'Produce', '1995'
    So since I was having so much trouble with everything else I used it and it stuck. But if Exec is better, I'll use it.

    Anyway, thanks again for changing my [banghead] into [kickbutt]!!

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - that SQLOLEDB thing you found is interesting. I didn't know that and, as such, I wouldn't advocate the T-SQL over what they recommend. Is this your reference?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2006
    Posts
    7
    Quote Originally Posted by pootle flump
    Coo - that SQLOLEDB thing you found is interesting. I didn't know that and, as such, I wouldn't advocate the T-SQL over what they recommend. Is this your reference?
    That looks like it. I got to it through the VBA code editor in Access, but this looks identical.

    I ran across it while piecing together code bits for my ExecuteQuery function and didn't pay much attention. Then, after some trouble and closing out the help window, I remembered it and spent the next half hour trying to find it again. Go figure.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •