Solved

TRANSACTIONS AND SP

Posted on 2001-07-17
6
462 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
  • 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

830 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