wwcreator
asked on
TRANSACTIONS AND SP
Kind all,
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??
THANX!!
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.Com mand")
' Set up the parameters for the procedure call
With cmd
.ActiveConnection = cnstr
.CommandText = "Sp_UpdateProducts"
.CommandType = adCmdStoredProc
End With
' Execute the stored procedure directly
cmd.Execute , , ADODB.adExecuteNoRecords
Clean_Up:
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
CtxSetComplete
Exit Function
ErrorHandler:
RaiseError MODULE_NAME, "VerifyUser"
GoTo Clean_Up
End Function
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??
THANX!!
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.Com
' Set up the parameters for the procedure call
With cmd
.ActiveConnection = cnstr
.CommandText = "Sp_UpdateProducts"
.CommandType = adCmdStoredProc
End With
' Execute the stored procedure directly
cmd.Execute , , ADODB.adExecuteNoRecords
Clean_Up:
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
CtxSetComplete
Exit Function
ErrorHandler:
RaiseError MODULE_NAME, "VerifyUser"
GoTo Clean_Up
End Function
For the rollback stuff do it in your Stored procedure if possible not in your VB code.
Vin.
Vin.
Had another look
inthedark is correct you don't have a connection object.
You are passing in a string to the procedure for cnstr this should be an ADOSB.Connection object.
Vin.
inthedark is correct you don't have a connection object.
You are passing in a string to the procedure for cnstr this should be an ADOSB.Connection object.
Vin.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Vin,
It is perfectly legal to give a connection string to the ActiveConnection property of the Command object. In other words you do not have to use a Connection object and assign it to the ActiveConnection property.
I am not saying this is good programming practice, using a Connection object gives you access to the Error object, it is just not neccessary (just like the Recordset object).
Anthony
It is perfectly legal to give a connection string to the ActiveConnection property of the Command object. In other words you do not have to use a Connection object and assign it to the ActiveConnection property.
I am not saying this is good programming practice, using a Connection object gives you access to the Error object, it is just not neccessary (just like the Recordset object).
Anthony
Yup your right it is perfetly legal to pass just a string to a connection and command.ActiveConnection
Just never crossed my mind to try it that way.
Vin.
Just never crossed my mind to try it that way.
Vin.
The command object needs a connection.
example:
dim cnn1 as adodb.connection
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
In you example in the With cmd area
set .ActiveConnection=cnn1
Now you can add rollback handling through the connection object e.g.
cnn1.BeginTrans
on error resume next
err.clear
cmd.Execute , , ADODB.adExecuteNoRecords
if err.number<>0 then
' operation failed
cnn.rollback
else
cnn.committrans
end if