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

 

[Douglas J. Steele]

I'm not sure, but I think the problem is that your SP doesn't actually
return anything: it strictly prints to the client.

Look up "stored procedures, returning data" in Book Online. Sounds as though
you'll want to use an Output parameter.

 

[Graham R Seach]

Rob,

Use this syntax:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command

Dim strOutput As String

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

With cmd
.ActiveConnection = cnn
.CommandText = "SellingPriceImport"
.CommandType = adCmdStoredProcedure
.Parameters.Refresh
.Execute
strOutput = .Parameters("@SuccessFail")
End With

cnn.Close
Set cmd = Nothing
Set cnn = Nothing

By the way, I assume you have the following somewhere in your sp:
DECLARE @ErrorCode INT
--
SET @ErrorCode = @@Error

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia