harmmeijer
Programmer
In the query analyzer it works perfect but the sproc will not return its value to my vb program.
Here is the sproc:
CREATE PROCEDURE [mysproc]
@number int,
@@outnum int OUTPUT
AS
insert into test ([testid]) values (@number)
select @@outnum = @number
GO
Here is the vb code:
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim intInteger As Integer
conn.ConnectionString = "Provider=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=****;Initial Catalog=test;Data Source=****"
conn.Open
With comm
.ActiveConnection = conn
.CommandText = "mysproc"
.CommandType = adCmdStoredProc
'Input parameters
.Parameters.Append .CreateParameter("@number", adInteger, adParamInput, , 22)
'Output parameters
.Parameters.Append .CreateParameter("@@outnum", adInteger, adParamOutput, , intInteger)
.Execute , , ADODB.adExecuteNoRecords
' sproc has been executed and the value 22 has been inserted
' intInteger is still 0 WHY????
End With
When I run the sproc in the query analyzer I get the output value:
declare @outnum int
exec mysproc 22, @outnum output
select @outnum as test
output:
(1 row(s) affected)
test
-----------
22
(1 row(s) affected)
Here is the sproc:
CREATE PROCEDURE [mysproc]
@number int,
@@outnum int OUTPUT
AS
insert into test ([testid]) values (@number)
select @@outnum = @number
GO
Here is the vb code:
Dim conn As New ADODB.Connection
Dim comm As New ADODB.Command
Dim intInteger As Integer
conn.ConnectionString = "Provider=SQLOLEDB.1;Password=****;Persist Security Info=True;User ID=****;Initial Catalog=test;Data Source=****"
conn.Open
With comm
.ActiveConnection = conn
.CommandText = "mysproc"
.CommandType = adCmdStoredProc
'Input parameters
.Parameters.Append .CreateParameter("@number", adInteger, adParamInput, , 22)
'Output parameters
.Parameters.Append .CreateParameter("@@outnum", adInteger, adParamOutput, , intInteger)
.Execute , , ADODB.adExecuteNoRecords
' sproc has been executed and the value 22 has been inserted
' intInteger is still 0 WHY????
End With
When I run the sproc in the query analyzer I get the output value:
declare @outnum int
exec mysproc 22, @outnum output
select @outnum as test
output:
(1 row(s) affected)
test
-----------
22
(1 row(s) affected)