ADO- Stored Proc Call using Variable SP

[Rock6431]

I am working with SQL2000 using a trusted domain connection. I used the DataEnvironment in VS6 to define and complete the connection. I have successfully connected and passed information via the same SP. The SP was also inserted into my project using the DataEnvironment.

Basically, I am taking the single working function and disassembling it into a connection function, disconnection function, and a variable Stored Procedure call that I can control externally outside of my com.

Here is the single function that worked just fine.
Store Proc Name = <dbo_SDtable_in>

------------------------------------------------------
Public Function TestSQLconnect(strDATABASE, str1, str2, str3) As Boolean

On Error Resume Next

Dim cnn As New ARconnection ' Var 4 Datasource
Dim strconnection As String ' Connection 4 Trusted Source
Dim rs As Long ' Ret checksum from SQL2000

Err.Clear

strconnection = &quot;Driver={SQL Server};Server=AUDREY;Database=&quot; & strDATABASE & &quot;;Trusted_Connection=yes;&quot;

Call cnn.AudreyRampITconnection.Open(strconnection)
If Err <> 0 Then
TestSQLconnect = False
Exit Function
End If

rs = cnn.dbo_SDtable_in(str1,str2,str3)

If rs = 0 Then
TestSQLconnect = True
Else
TestSQLconnect = False
End If

Call cnn.AudreyRampITconnection.Close
End Function
-------------------------------------------------------

Below are the 3 seperate functions I have been working on.
I am still connecting and disconnecting ok. Just the Execute SQL Store Proceedure area is fuzzy...

-------------------------------------------------------
Private cnn As New ARconnection
' <cnn> is now a global definition...


Public Function SQLconnect(strDATABASE) As Boolean
On Error Resume Next

Dim strconnection As String

Err.Clear

strconnection = &quot;Driver={SQL Server};Server=AUDREY;Database=&quot; & strDATABASE & &quot;;Trusted_Connection=yes;&quot;

Call cnn.AudreyRampITconnection.Open(strconnection)
If Err = 0 Then
SQLconnect = True
Else
SQLconnect = False
End If

End Function

Public Function SQLdisconnect()
' Function to Disconnect the Audrey SQL connection
Call cnn.AudreyRampITconnection.Close
End Function


Public Function EXSQLsp(strSTOREDPROC, strPARAMETERS)

Dim sp As ?????????
Dim rs As Long

'rs = cnn.dbo_SDtable_in(strPARAMTERS) ' This works but not
' as a variable SP

sp.ActiveConnection = cnn.Connections
sp.CommandText = strSTOREDPROC
sp.CommandType = adCmdStoredProc
rs = sp.Execute(strPARAMETERS)
If rs = 0 Then
EXSQLsp = True
Else
EXSQLsp = False
End If

End Function

-------------------------------------------------------

Thanks for any help.

Rock6431