Jim Horn
asked on
SQL2K, Excel VBA using ADO: When command.ActiveConnection = gcn, it always contains a parameter with it. How to find and remove?
In Excel VBA I am using ADO to connect to a SQL2K db, and set a recordset object = SP with one parameter
The SP works fine in QA
cmdPLCY.CommandType = adCmdStoredProc
cmdPLCY.CommandText = "spm_POLICY_INFO"
cmdPLCY.ActiveConnection = gcnCASES '<-- Looky Here
After execution of the third line above, when I eyeball the Locals window in cmdPLCY.Parameters, it contains one prarameter of @RETURN_VALUE. I use this in SP's alot, but I did not set it anywhere in VBA code, and have no idea how it could be attaching itself to my connection object.
The sub that sets my connection object (below), is working fine:
Public Function scnCASES() As Boolean
'some stuff omitted
Dim sConnect As String
sConnect = "PROVIDER=SERVER;Driver=SQ LServer;Se rver=SERVE R_NAME;UID =USER_NAME ;PWD=PASSW ORD;Databa se=DB_NAME ;"
If gcnCASES Is Nothing Then
Set gcnCASES = CreateObject("ADODB.Connec tion")
gcnCASES.ConnectionString = sConnect
gcnCASES.Open
End If
If gcnCASES.State <> adStateClosed Then
scnCASES = True
Else
scnCASES = False
End If
End Function
TIA
Jim
The SP works fine in QA
cmdPLCY.CommandType = adCmdStoredProc
cmdPLCY.CommandText = "spm_POLICY_INFO"
cmdPLCY.ActiveConnection = gcnCASES '<-- Looky Here
After execution of the third line above, when I eyeball the Locals window in cmdPLCY.Parameters, it contains one prarameter of @RETURN_VALUE. I use this in SP's alot, but I did not set it anywhere in VBA code, and have no idea how it could be attaching itself to my connection object.
The sub that sets my connection object (below), is working fine:
Public Function scnCASES() As Boolean
'some stuff omitted
Dim sConnect As String
sConnect = "PROVIDER=SERVER;Driver=SQ
If gcnCASES Is Nothing Then
Set gcnCASES = CreateObject("ADODB.Connec
gcnCASES.ConnectionString = sConnect
gcnCASES.Open
End If
If gcnCASES.State <> adStateClosed Then
scnCASES = True
Else
scnCASES = False
End If
End Function
TIA
Jim
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>Didn't you made a typo?
Nope.
'Global variable
Dim gcnCases as adodb.connection
'In every sub/function
If gcnCases Is Nothing then call scnCASES 'which sets the gcnCases global variable.
>and have no idea how it could be attaching itself to my connection object.
>you mean, the command object.
Correct, the command object. My typo.
The SP in question returns a SELECT * FROM #tmp, with 30ish columns.
It also uses a bunch of variables, but @RETURN_VALUE is not one of them, and nowhere before the final SELECT * FROM #tmp is there a SELECT @anything.
Nope.
'Global variable
Dim gcnCases as adodb.connection
'In every sub/function
If gcnCases Is Nothing then call scnCASES 'which sets the gcnCases global variable.
>and have no idea how it could be attaching itself to my connection object.
>you mean, the command object.
Correct, the command object. My typo.
The SP in question returns a SELECT * FROM #tmp, with 30ish columns.
It also uses a bunch of variables, but @RETURN_VALUE is not one of them, and nowhere before the final SELECT * FROM #tmp is there a SELECT @anything.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Okay, that makes sense.
I discovered that after I had SET NOCOUNT ON in all the SP's they worked, regardless of the fact that my command objects had a parameter of @RETURN_VALUE.
Thanks guys.
Jim
I discovered that after I had SET NOCOUNT ON in all the SP's they worked, regardless of the fact that my command objects had a parameter of @RETURN_VALUE.
Thanks guys.
Jim
you try to set the connection using gcnCASES
though the function is called: scnCASES
or should you use:
cmdPLCY.ActiveConnection = gcnCASES(scnCASES)