ado+stored procedure+middle tier

DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: ado+stored procedure+middle tier

  1. #1
    Michael C Guest

    ado+stored procedure+middle tier

    Would someone please look at the following middle tier function method and
    tell me why I cannot get the recordset on the client side if I close the
    server connection. Thanks!

    Public Function SQLStoredProc(ByVal sConnection As String, _
    ByVal sSpName As String, Optional ByVal vntArgParams As String,
    Optional ByVal vntRstReturn As Variant) As Recordset

    On Error GoTo ErrorHandler: Const METHOD_NAME = "SQLStoredProc"

    'Declare local variables.
    Dim oConn As ADODB.Connection
    Dim i As Integer
    Dim oCmd As ADODB.Command
    Dim varParams As Variant
    Dim rsResult As Recordset
    Dim asStrings1() As String
    Dim asStrings2() As String

    Dim sValue As String
    Dim sName As String

    'Initialize objects
    Set oCmd = New Command

    'Open the connection to the database.

    Set oConn = ConnectToDb(sConnection)

    With oCmd
    .ActiveConnection = oConn
    .CommandType = adCmdStoredProc
    .CommandText = sSpName
    End With

    'If the stored procedure has parameters, get their name, data type and
    If Not IsMissing(vntArgParams) Then
    'Reset command object.
    Set rsResult = New Recordset
    With rsResult
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    End With

    'Parse the input parameter string. Create array of strings such that
    each occurrence
    'is a name value pair like @PARM1=25
    asStrings1 = Split(vntArgParams, ",")

    'Loop through all input arguments.
    With oCmd


    For i = LBound(asStrings1) To UBound(asStrings1)
    'Parse each parameter into a name and value.
    'Remove @param= prefix
    asStrings2 = Split(asStrings1(i), "=")
    'Remove single quoted text around passed in strings.
    'Assign the name and value to the parameters property of
    ' the command object.
    sValue = Replace(asStrings2(1), "'", "")
    sName = .Parameters(i + 1).Name
    .Parameters(sName) = sValue


    'Execute sp and return values to recordset.
    Set rsResult = .Execute
    'rsResult.ActiveConnection = Nothing

    End With

    End If
    'Configure recordset for values returned from stored procedure.

    'Check for errors.
    If oConn.Errors.Count <> 0 Then GoTo ErrorHandler

    'Only variants can be the argument for IsMissing. If a variable
    'was not passed in to this function then do not return the result set.
    If Not IsMissing(vntRstReturn) Then
    'Cast recordset as a variant.
    'Set vntRstReturn = rsResult
    Set SQLStoredProc = rsResult

    End If

    If (Not oCmd.ActiveConnection Is Nothing) Then Set oCmd.ActiveConnection
    = Nothing

    'If (Not rsResult.ActiveConnection Is Nothing) Then Set rsResult.ActiveConnection
    = Nothing

    'Clean up.
    ' CloseConnection oConn
    ' If (Not oCmd Is Nothing) Then Set oCmd = Nothing
    ' If (Not rsResult Is Nothing) Then Set rsResult = Nothing

    'Vote to commit transaction.
    ' objContext.EnableCommit

  2. #2
    Marko Sink Guest

    Re: ado+stored procedure+middle tier

    I didn't actualy read your code,
    but if you want to close the connection on the server,
    you should return the clone of that recordset
    and not the recordset itself.

    set rs2 = rs1.clone

    than you can close the connection

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center