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.
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.