Solved

TRANSACTIONS AND SP

Posted on 2001-07-17
6
461 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
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…

773 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