Solved

TRANSACTIONS AND SP

Posted on 2001-07-17
6
459 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now