Solved

TRANSACTIONS AND SP

Posted on 2001-07-17
6
464 Views
Last Modified: 2008-03-04
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.Command")
 
 
  ' 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
0
Comment
Question by:wwcreator
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
6 Comments
 
LVL 17

Expert Comment

by:inthedark
ID: 6289015
I think you may have a problem with you code.  The way you handle the activeconnectionm looks iffy.

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
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6289020
For the rollback stuff do it in your Stored procedure if possible not in your VB code.

Vin.
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6289024
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
VincentLawlor earned 165 total points
ID: 6289035
On the subject or rollback it is not good practice to rollback transactions like this in VB. They should be done in your Stored Procedure.

In SQL:

BEGIN TRAN

    Do something.....

    Errors
       ROLLBACK TRAN
       RAISEERROR
       RETURN 1 --Failure

No Errors

COMMIT TRAN
RETURN 0 --Success

Vin.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6291837
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
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6292921
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.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question