I believe the error message is occuring when there is more than one SP
parameter, since all SP's taking a single parameter run just find (i.e.
the 90% of the time I refered to in my previous post).
(E-Mail Removed) wrote:
> ...which has a command object as its source.
>
> Is the error mesage that is currently driving me nutty. Let me explain.
> The function causing the problem is as follows:
>
> Public Type SPDataset
> Name As String 'Name of the stored procedure
> arrParameters() As String
> Source As String 'The calling Form
> bSuccess As Boolean 'If everything went ok
> End Type
>
> Public Function sqlSP(ExecuteSP As SPDataset) As ADODB.Recordset
>
> Dim sqlConn As ADODB.Connection
> Dim sqlCmd As ADODB.Command
> Dim sqlRS As ADODB.Recordset
> Dim N As Integer
>
> ' Establish connection.
> Set sqlRS = New ADODB.Recordset
> Set sqlCmd = New ADODB.Command
> Set sqlConn = New ADODB.Connection
>
> 'Make sure the project is connected to the server
> If (CurrentProject.IsConnected = False) Then
> 'Stop the form from loading
> MsgBox "You must be logged in to perform this action. Please
> login and try again.", vbExclamation, "Database Error"
> ExecuteSP.bSuccess = False
>
> Else
>
> sqlConn.ConnectionString = CurrentProject.Connection
> sqlConn.Open
>
> sqlConn.CursorLocation = adUseClient 'Cache data locally
>
> ' Open recordset.
> With sqlCmd
> .ActiveConnection = sqlConn
> .CommandText = ExecuteSP.Name
> .CommandType = adCmdStoredProc
> .Parameters.Refresh
> 'Load the parameters
> If IsArrayAllocated(ExecuteSP.arrParameters) Then
> For N = LBound(ExecuteSP.arrParameters) To
> UBound(ExecuteSP.arrParameters)
> .Parameters("@" & ExecuteSP.arrParameters(N,
> 0)).Value = ExecuteSP.arrParameters(N, 1)
> Next N
> End If
> Set sqlRS = .Execute()
> End With
>
> 'Set the output parameters if they are requested
> If IsArrayAllocated(ExecuteSP.arrParameters) Then
> For N = LBound(ExecuteSP.arrParameters) To
> UBound(ExecuteSP.arrParameters)
> If Not ExecuteSP.arrParameters(N, 2) = "" Then
> If ExecuteSP.arrParameters(N, 2) = True Then
> ExecuteSP.arrParameters(N, 1) =
> sqlCmd.Parameters("@" & ExecuteSP.arrParameters(N, 0))
> End If
> End If
> Next N
> End If
>
> Set sqlRS.ActiveConnection = Nothing
> sqlConn.Close
> Set sqlConn = Nothing
>
> Set sqlSP = sqlRS
>
> Set sqlRS = Nothing
> Set sqlCmd = Nothing
> ExecuteSP.bSuccess = True
>
> End If
>
> End Function
>
> The idea behind it was that I would simply pass this function the
> required parameters and it would pass back a disconnected recordset. It
> works 90% of the time, with and without passed parameters but sometimes
> I get the aforementioned error message. I am not doing anything
> differently so why is the function behavining so awkwardly?
>
> Kind regards,
> Marc
Similar Threads
