How to get Output from stored proc

[Rob]

I am trying to run a stored procedure from an Access MDB database and have
the stored procedure return an Output value. The procedure runs, but no
value is returned.

I am using the code below. I want strOutput to be either Process
Successful or Process Failed !



' run stored proc
Dim cnn As ADODB.Connection
Dim Param As ADODB.Parameter
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set Param = New ADODB.Parameter

Dim strOutput As String

cnn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=(local);Initial Catalog=TestDB;" & _
"Integrated Security=SSPI"
cnn.Mode = adModeReadWrite
cnn.Open

With cmd
.ActiveConnection = cnn
.CommandText = "EXEC SellingPriceImport null"
.CommandType = adCmdText
Set Param = cmd.CreateParameter(Name:="SuccessFail", Type:=adVarChar,
Direction:=adParamOutput, Size:=100)
.Execute

End With

'The statement below returns an error
'strOutput = cmd.Parameters("SuccessFail").Value




CREATE PROCEDURE SellingPriceImport @SuccessFail as varchar(100) OUTPUT AS

-- Do some processing here


If @ErrorCode = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION

If @ErrorCode = 0
Set @SuccessFail = 'Process Successful !'
Else
Set @SuccessFail = 'Process Failed ! '
print @SuccessFail
GO

 

[Dirk Goldgar]

I am trying to run a stored procedure from an Access MDB database and
have the stored procedure return an Output value. The procedure
runs, but no value is returned.

I am using the code below. I want strOutput to be either Process
Successful or Process Failed !



' run stored proc
Dim cnn As ADODB.Connection
Dim Param As ADODB.Parameter
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set Param = New ADODB.Parameter

Dim strOutput As String

cnn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Data Source=(local);Initial Catalog=TestDB;" & _
"Integrated Security=SSPI"
cnn.Mode = adModeReadWrite
cnn.Open

With cmd
.ActiveConnection = cnn
.CommandText = "EXEC SellingPriceImport null"
.CommandType = adCmdText
Set Param = cmd.CreateParameter(Name:="SuccessFail",
Type:=adVarChar, Direction:=adParamOutput, Size:=100)
.Execute

End With

'The statement below returns an error
'strOutput = cmd.Parameters("SuccessFail").Value




CREATE PROCEDURE SellingPriceImport @SuccessFail as varchar(100)
OUTPUT AS

-- Do some processing here


If @ErrorCode = 0
COMMIT TRANSACTION
Else
ROLLBACK TRANSACTION

If @ErrorCode = 0
Set @SuccessFail = 'Process Successful !'
Else
Set @SuccessFail = 'Process Failed ! '
print @SuccessFail
GO

I'm not an expert on ADO, but I'd expect your calling code to be more
like this:

With cmd
.ActiveConnection = cnn
.CommandText = "SellingPriceImport"
.CommandType = adCmdStoredProc
Set Param = cmd.CreateParameter(Name:="@SuccessFail", _
Type:=adVarChar, Direction:=adParamOutput, Size:=100)
.Parameters.Append Param
.Execute, , adExecuteNoRecords
strOutput = .Parameters("@SuccessFail").Value
End With

I'm not sure about the "@" signs -- you may be able to leave them off.