TRANSACTIONS AND SP
Posted on 2001-07-17
I am using this function that calls a sp to update a Products table...
(please note I sent a little semplified version of the function..for ex I didn't write any input and/or output params from the SP).
I would like to manage the most of the business logic in my sp.
MY sp do 2 things: 1) first does a select against the product table to see if there are products available..
and then 2) if the products exixts in the magazine..the sp does an update...if not the sp RAISES an error RAISERROR(err number, ect, etc)
MY questions are:
1) HOW CAN I INTERCEPT THIS ERROR IN MY VB CODE?
2) COULD YOUN PLEASE COMPLETE ADN/OR CORRIGE MY EX. FUNCTION I POSTED HERE IN A WAY THAT, IF THE sp raises that MY COMPONENT DOES THE ROLLBACK OF THE TRANSACTION?
where precisely I have to put this code for the rollback??
Public Function VerifyUser(ByVal cnstr As String, ByVal UserID As String, ByVal Password As String, _
ByRef GroupID As Variant, ByRef FullName As Variant) As Boolean
' Verify the WROBA login
Dim cmd As ADODB.Command
On Error GoTo ErrorHandler
' Pessimistic assumption of login failure
VerifyUser = False
' The following code directly runs the stored procedure.
' Set up Command and Connection objects
Set cmd = CtxCreateObject("ADODB.Command")
' Set up the parameters for the procedure call
.ActiveConnection = cnstr
.CommandText = "Sp_UpdateProducts"
.CommandType = adCmdStoredProc
' Execute the stored procedure directly
cmd.Execute , , ADODB.adExecuteNoRecords
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
RaiseError MODULE_NAME, "VerifyUser"