Solved

transactions

Posted on 2002-04-25
17
367 Views
Last Modified: 2008-02-26
I have an application that I support, I didn't write it, but I need to make some enhancements to it...for the following procedure I needed to update the SQL statement to update the database because I added some fields to the table in the SQL 6.5 db (this isn't the problem) the following code is to update the customer table when the user makes a change (the user pulls this record up from a list of records that he has pending, he selects this record and it populates the tracking form) let's say the user changes the phone number on this record, he then selects the save button at the top of the form....as you look at this code notice the 'comments because I have been trying to correct this all day....first error I got says that there is no active record open, when I debug, it points me to the "gConnection.RollbackTrans" statement, tellin me that there is not active transaction...it did this for a while and then I got a different error, that there was already an ODBC connection open.....so I closed out got back me and now I'm getting the first error again....can anyone look at my code and explain to me what is happening and what I can do to fix the problem, I am not that familiar with transactions so any advice along the way would be appreciated (explain to me the 2nd problem as well)...thanks in advance for your help...Schuyler

here's the code

Private Sub UpdateCustADO()

    Dim strSQL As String, strState As String, strPhone As String
    'Dim strCustInfoChg As String
    'Dim bolTrans As Boolean
   
    Screen.MousePointer = vbHourglass
    On Error GoTo SQLError
   
    If cboState.Tag = "DB" Then
        strState = cboState.Text
    Else
        strState = cboState.Tag
        'strState = TranslateStateCode(cboState.ItemData(cboState.ListIndex))
    End If
   
    strPhone = Trim(LCase(txtPhone))
   
    If strPhone = "declined" Or strPhone = "refused" Then
        strPhone = txtPhone
    Else
        strPhone = StripNonDigits(txtPhone)
    End If
    'bolTrans = True
    'gConnection.BeginTrans
    strSQL = "update customer set lastname = '" & FixQuotes(txtLast, "'") & _
             "', firstname = '" & FixQuotes(txtFirst, "'") & "', mi = '" & txtMI & _
             "', address1 = '" & FixQuotes(txtAddress(0), "'") & _
             "', address2 = '" & FixQuotes(txtAddress(1), "'") & _
             "', city = '" & FixQuotes(txtCity, "'") & _
             "', state = '" & strState & _
             "', zip = '" & txtZip & _
             "', phone = '" & strPhone & _
             "', datetimestamp = '" & Date & " " & Format(Time, "hh:nn:ss") & _
             "', email = '" & FixQuotes(txtEMail, "'") & _
             "', BDOptIn = '" & FixQuotes(Left$(CStr(optBD), 1), "'") & "', DWOptIn = '" & FixQuotes(Left$(CStr(optDW), 1), "'") & _
            "', CustInfoChg = '" & FixQuotes(Me.chkCustInfoChg, "'") & "' where enduserid = " & txtCustID
           
           '  '"', " & " where enduserid = " & txtCustID
          '  ", Country = '" & strCountry & "'" & " where contactid = " & txtCallID
   'original loc
    gConnection.BeginTrans
    gConnection.Execute strSQL
    gConnection.CommitTrans
    'bolTrans = False

    gADOrst.Requery
    Screen.MousePointer = vbDefault
    Exit Sub

SQLError:
    'made change to code to reverse the next two lines of code in both update routines,
    'need to rollback the transaction, free up locks first, before user gets error msg
    'gConnection.RollbackTrans
   
    HandleError Err
    'MsgBox "Could not update. Error:" & Err.Number & " " & Err.Description & " Source: " & _
    'Err.Source
    'If bolTrans Then
    'gConnection.RollbackTrans
    'bolTrans = False
    'End If
    gConnection.RollbackTrans
    MsgBox "Sorry, the server was unable to save the customer.  Please try again.", vbOKOnly, "SQL Error"
   
    On Error Resume Next
    Err.Clear
    On Error GoTo 0
    'gConnection.Close
End Sub
0
Comment
Question by:schuyler
  • 7
  • 5
  • 4
  • +1
17 Comments
 

Expert Comment

by:Death-Speak
ID: 6969968
Is there something in there that kicks you out to SQLError: before the transaction has began, if it points you to the .RollBack thats probably what is happening there.

This is the code you posted with the comments removed. If your taken here before the .BeginTrans would the .RollbackTrans not give you an error?
...
SQLError:
   
   HandleError Err
     gConnection.RollbackTrans
     MsgBox "Sorry, the server was unable to save the customer.  Please try again.", vbOKOnly, "SQL Error"
   
     On Error Resume Next
     Err.Clear
     On Error GoTo 0
  End Sub

Don't know about the second error.
0
 

Author Comment

by:schuyler
ID: 6971013
Death-Speak,
?????, I don't understand what your saying....it seems to kick me right to the .rollbacktrans...I don't understand what your saying other than that....please explain a bit further...thanks, S
0
 

Expert Comment

by:Death-Speak
ID: 6971385
I'm saying that you *could* be getting kicked to that .rollbacktrans in the SQLError section before the .begintrans in the upper part of the procedure. If you haven't began the transaction and you hit that rollback in the SQLError section at the end, you might get that error.

Heres modified code:



Private Sub UpdateCustADO()

   Dim strSQL As String, strState As String, strPhone As String
   
   Screen.MousePointer = vbHourglass
   On Error GoTo SQLError
   
   'Try .begintrans before any errors could possibly be introduced
   gConnection.BeginTrans '<Added by Death-Speak
   
   If cboState.Tag = "DB" Then
       strState = cboState.Text
   Else
       strState = cboState.Tag
   End If
   
   strPhone = Trim(LCase(txtPhone))
   
   If strPhone = "declined" Or strPhone = "refused" Then
       strPhone = txtPhone
   Else
       strPhone = StripNonDigits(txtPhone)
   End If
   strSQL = "update customer set lastname = '" & FixQuotes(txtLast, "'") & _
            "', firstname = '" & FixQuotes(txtFirst, "'") & "', mi = '" & txtMI & _
            "', address1 = '" & FixQuotes(txtAddress(0), "'") & _
            "', address2 = '" & FixQuotes(txtAddress(1), "'") & _
            "', city = '" & FixQuotes(txtCity, "'") & _
            "', state = '" & strState & _
            "', zip = '" & txtZip & _
            "', phone = '" & strPhone & _
            "', datetimestamp = '" & Date & " " & Format(Time, "hh:nn:ss") & _
            "', email = '" & FixQuotes(txtEMail, "'") & _
            "', BDOptIn = '" & FixQuotes(Left$(CStr(optBD), 1), "'") & _
            "', DWOptIn = '" & FixQuotes(Left$(CStr(optDW), 1), "'") & _
            "', CustInfoChg = '" & FixQuotes(Me.chkCustInfoChg, "'") & _
            "' where enduserid = " & txtCustID
           
   'if you errored out before this point (before I changed the code) you might have got an error at the .rollbacktrans in SQLError
   'gConnection.BeginTrans    <commented by Death-Speak
   gConnection.Execute strSQL
   gConnection.CommitTrans

   gADOrst.Requery
   Screen.MousePointer = vbDefault
   Exit Sub

SQLError:
   
   HandleError Err
   'You might even do a check here for .begintrans
   gConnection.RollbackTrans
   MsgBox "Sorry, the server was unable to save the customer.  Please try again.", vbOKOnly, "SQL Error"
   
   On Error Resume Next
   Err.Clear
   On Error GoTo 0
End Sub
0
 

Author Comment

by:schuyler
ID: 6971852
I see...I'll test that...thanks! Schuyler
0
 

Author Comment

by:schuyler
ID: 6972342
now I get the error, "Transaction cannot be started because more than one ODBC connection is in use. -2147168227" (b/c I added the Err.Number in my error handler)...

when I debug it goes from the gConnection.BeginTrans to HandleError Err

how do I close this connection so i don't get the ODBC error...I'm assuming once I can do that I'll be able to see what is really happening here....
0
 

Expert Comment

by:Death-Speak
ID: 6972358
Do you have another transaction pending somewhere? I'm pretty stuck at this point, I can't find anything helpful on the error you posted, I'll keep looking and post back here when I find somehting.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7018676
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20168321.html
http://www.experts-exchange.com/questions/Q.20235732.html
http://www.experts-exchange.com/questions/Q.20245009.html
http://www.experts-exchange.com/questions/Q.20253649.html
http://www.experts-exchange.com/questions/Q.20261819.html
http://www.experts-exchange.com/questions/Q.20293723.html
http://www.experts-exchange.com/questions/Q.20298099.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 

Author Comment

by:schuyler
ID: 7071509
Can anyone add anything here...I am still having trouble updating this database and I know that the SQL statement is good and technically that is the only change I made...I've gotten an error msg that says....ODBC connection already in use...etc....can anyone Help!!!
I'll increase the points!
thanks...schuyler
0
Free Trending Threat Insights Every Day

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.

 

Expert Comment

by:Death-Speak
ID: 7071513
Sorry, I wish I could help you more!
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7071533
Sorry you haven't been helped here, perhaps you'd like a refund so that you can ask this again to draw current attention, your choice.

Also, perhaps something here will get you started (global google.com search), in the interim.
http://support.dafweb.com/html/troubleshoot/Troubleshooting.htm

Moondancer - EE Moderator
0
 

Author Comment

by:schuyler
ID: 7073646
thanks moondancer, I will check out the link...how do I go about re-introducing the question to the experts.....
schuyler
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7073674
Here's what we can do.... I can refund your 50 points to you for this, since your goal wasn't achieved, and move it to our PAQ at zero points.

You then open a new question with all the pertinent information (cut/paste, delete line breaks), to draw current attention.  Then paste the link to this in the question so others can see the work done so far.

Let me know.

Moondancer - EE Moderator
0
 

Author Comment

by:schuyler
ID: 7073736
sorry but i don't know how to paste the link to this Q...I've seen it done...how do you do it?
0
 

Expert Comment

by:Death-Speak
ID: 7073763
Highlight the address in your browser, and hit ctrl + c to copy the link, open the new question and use ctrl + v to paste it. Or you can use the Edit > Copy and Edit > Paste functions of your browser.
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 7073864
Or once highlighted, right click with mouse and choose Copy from source, and right click mouse to Paste to new target.

Just stopping by.

":0) Asta
0
 

Author Comment

by:schuyler
ID: 7116385
Moondancer, I posted the question again, it said I needed to include points so I did 50 again....if I could get my first 50 refunded that would be cool....Thanks, Schuyler
0
 
LVL 1

Accepted Solution

by:
Moondancer earned 0 total points
ID: 7117272
I have refunded these 50 points to you now, and closed this for you (moved to our PAQ where others will see it).  I see that you've already included this link in your new question (great).  This is the link to your new question, so that the participants can jump to it to help you further.

http://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20317388

Moondancer - EE Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…

758 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

19 Experts available now in Live!

Get 1:1 Help Now