Stored Procedures using Command Object

[Jay]

Hi

I am using a command object to call a stored procedure
and pass in parameters. The SP is inserting into an
orders table, and if no errors happen, into a orderdetail
table. If an error occurs a message is returned to the
user. The stored procdure works a treat when called from
Query Analyser and if an incorrect Customer ID is entered
a message is returned in an output paramater, if no error
occurs the rest of the order details are entered.

Using the ADO Command object, it all works fine if the
customerid and order details are OK, but if the
customerid is wrong (i.e. the customer doesn't exist) an
error is raised but nothing is passed back to the output
paramater in the command object. Any ideas how to get
the message would be great.

Thanks

Jay

 

[Graham R Seach]

Jay,

Show me you VBA call.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Graham R Seach]

Jay,

I received the code you sent me, and your problems seems to stem from your
use of RETURN_VALUE in the following lines:
If myCmd.Parameters("RETURN_VALUE") = 1 Then
MsgBox "Code 1: " & strReturnMsg
ElseIf myCmd.Parameters("RETURN_VALUE") = 0 Then
MsgBox "Code 0: " & strReturnMsg
Else
MsgBox "Code 2: " & strReturnMsg
End If

There is no such parameter as RETURN_VALUE. What you want to use is
@RETURN_VALUE.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html