schuyler
asked on
transactions in vb6(sp3) and sql6.5 db
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 is the history for this question, I am still desperate to find a solution, this app was just rolled out on new pc's and I'm getting all sorts of run time errors, "No transaction is active" and unfortunately I have hardly a clue as to why!!!!
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20293723
thanks in advance for your continued support and assistance!!
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(cboStat e.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.chkCustInfoCh g, "'") & "' 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
Here is the history for this question, I am still desperate to find a solution, this app was just rolled out on new pc's and I'm getting all sorts of run time errors, "No transaction is active" and unfortunately I have hardly a clue as to why!!!!
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=visualbasic&qid=20293723
thanks in advance for your continued support and assistance!!
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(cboStat
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
"', CustInfoChg = '" & FixQuotes(Me.chkCustInfoCh
' '"', " & " 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
Could you please post lines of code where you open the gConnection and the way gConnection is declared?
The code is possibly failing on...
gADOrst.Requery
It then goes to the err handler where it tries to rollback a transaction that has already been committed. This would give you the error you are encountering.
Comment out all of the transaction code (Begin, Commit, Rollback) and see where it fails.
It appears you have open questions and this question has been outstanding for quite some time. You need to close out your other questions asap.
gADOrst.Requery
It then goes to the err handler where it tries to rollback a transaction that has already been committed. This would give you the error you are encountering.
Comment out all of the transaction code (Begin, Commit, Rollback) and see where it fails.
It appears you have open questions and this question has been outstanding for quite some time. You need to close out your other questions asap.
ASKER
gencross,
I thought I went through and closed all my open questions, perhaps I am doing something wrong to close them...this particular question was reopened on the advice of moondancer.....I will try your suggestion as well
rpai,
gConnection is a global connection that is connected thru ODBC to a SQL65 database, here is the string
Provider=MSDASQL.1;Passwor d=schuyler ;User ID=scallon;Extended Properties="DSN=plus99;Des cription=p lus99 connection;UID=scallon;PWD =scallon;A PP=Visual Basic;WSID=STANDALO-NTWS;D ATABASE=PL US99"
this is a dev box....
I thought I went through and closed all my open questions, perhaps I am doing something wrong to close them...this particular question was reopened on the advice of moondancer.....I will try your suggestion as well
rpai,
gConnection is a global connection that is connected thru ODBC to a SQL65 database, here is the string
Provider=MSDASQL.1;Passwor
this is a dev box....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please make sure that you close all the objects on successful completion of the code like the way in the ERrorHandler (sp. the gConnection) to avoid memory leaks.
ASKER
rpai,
sorry I saw string and went for the provider string...heres how the connection object is declared
Global gConnection As ADODB.Connection
as you suspected...
S
sorry I saw string and went for the provider string...heres how the connection object is declared
Global gConnection As ADODB.Connection
as you suspected...
S
Did you try commenting out the line gADOrst.ReQuery statement and execute the code again? Did that help?
ASKER
the gADOrst is a global recordset...this is set when I load the customer calls, what I'm doing is collecting calls assigned to a particular ID, then I select one of this records from a list, then I make a change to this customer record,I go to save the record and it checks to see if its new or needs to be updated...the UpdateADOCust procedure
when I commented out the gADOrst.requery I get an error and if I comment out the transaction code I get an error...I will tell you exactly what the error is when I can start testing again someone is using my pc and and i don't recall the exact messages...sorry!
when I commented out the gADOrst.requery I get an error and if I comment out the transaction code I get an error...I will tell you exactly what the error is when I can start testing again someone is using my pc and and i don't recall the exact messages...sorry!
Any progress on this?
ASKER
no, I was pulled into something else, I apologize....I will follow up shortly. S
ASKER
I'm going to get back to this this afternoon...hope you guys are still interested...thanks as always!
S
S
Hi schuyler,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Save as PAQ -- No Refund.
schuyler, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit. Explain why.
==========
DanRollins -- EE database cleanup volunteer
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:
Save as PAQ -- No Refund.
schuyler, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit. Explain why.
==========
DanRollins -- EE database cleanup volunteer
ASKER
i'd like to award the points to rpai for his efforts on my behalf....but not for a particular comment b/c nothing was resolved here...thanks...S
hi schuyler,
Thanks for coming back to finalize this old question. To award points to rpai, click the "Select Comment as Answer" near one of his comments. Thanks.
-- Dan
Thanks for coming back to finalize this old question. To award points to rpai, click the "Select Comment as Answer" near one of his comments. Thanks.
-- Dan
ASKER
these points are being awarded for your efforts but not for a resolution to the problem......I appreciate your efforts but this one got away from me.....