Trouble with a rather simple function

G

Guest

Hi all!

I have a problem while trying to use a rather simple ADO function.
Here is the situation:
Server: SQL Server 2005. I have all permissions on this server and its
objects, databases...
Database: TEST_ADO containing one table as follows:
Table: Table_1 with 2 columns:
COL_1(primary key, integer, not null)
COL_2(nvarchar(50), null)

The database also contains a stored procedure:
USE TEST_ADO
GO
CREATE PROCEDURE Test
(
@Address int,
@Value char(50) output
)
AS
SELECT @Value = Table_1.COL_2
FROM Table_1 WHERE (Table_1.COL_1=@Address)
GO

Now, within MS-Access (2003), I try to call this stored procedure with this
function:

Function TestADO()

Dim cnnTest As New ADODB.Connection
Dim cmdTest As New ADODB.Command
Dim prmAddress As ADODB.Parameter
Dim prmValue As ADODB.Parameter
Dim strCnn As String
Dim strValue As String

strCnn = "driver={SQL Server};SERVER=BRAVO-N;DATABASE=TEST_ADO"
cnnTest.Open strCnn
cmdTest.CommandText = "Test"
cmdTest.CommandType = adCmdStoredProc
Set prmAddress = cmdTest.CreateParameter("Address", adUnsignedInt,
adParamInput, 4, CLng(1))
cmdTest.Parameters.Append prmAddress
Set prmValue = cmdTest.CreateParameter("Value", adChar, adParamOutput,
50, strValue)
cmdTest.Parameters.Append prmValue
cmdTest.Execute
cnnTest.Close
Set prmAddress = Nothing
Set prmValue = Nothing
Set cmdTest = Nothing
Set cnnTest = Nothing
Debug.Print strValue
Stop

End Function

At the line cmdTest.Execute, I receive the following error:

Execution error '3709':
"The connection cannot be used to perform this operation. It is either
closed or invalid in this context"

Can someone help me?

Thanks in advance.
 
D

Douglas J Steele

You seem to have forgotten to set the ActiveConnection property for the
Command object:

Set cmdTest.ActiveConnection = cnnTest
 
G

Guest

Thanks Douglas!

It works now (stupid me).
Douglas J Steele said:
You seem to have forgotten to set the ActiveConnection property for the
Command object:

Set cmdTest.ActiveConnection = cnnTest
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top