?
Solved

TRANSACTIONS AND SP

Posted on 2001-07-17
6
Medium Priority
?
466 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
Independent Software Vendors: 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 495 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
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…
Suggested Courses
Course of the Month13 days, 1 hour left to enroll

777 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