[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

How do i know if the connection object has any active transactions?

Hi,
   i want to find out if a connection object has any active transactions to commit or rollback.i am diong something like this
sub X()
on error goto errorhandle
conn.begintrans
set rs conn.open "select........"
................
.................
sql = "update......."
conn.execute sql
conn.committrans
exit sub
errorhandle:
 if conn.errors.count > 0 then
    conn.rollbacktrans
   msgbox("Update failed")
   exit sub

 end if
end sub

I am getting this error
  "Run time error no active transactions"
Please some one help me.Thanks
0
Sireesha
Asked:
Sireesha
1 Solution
 
wensinkgCommented:
What database provider are you using? According to Microsoft if the provider doesn't support transactions you will get an error for trying to call BeginTrans.  This could be your problem.  Try checking the "Transaction DDL" property in the connection object.  If this value is adPropNotSupported (0) that means that the property is not supported and you will have to find some other way to manage your transaction.

HTH

Gary
0
 
SireeshaAuthor Commented:
wensinkg,
          I am using sql server.There are no errors in no of modules.it works fine.But,if there is a different database error and i try to rollback the transaction that is when it is giving me an error that says no active transaction mode.Please let me know your thoughts on this.I can post the exact errors tomorrow.I will try to debug and get more info.Thanks

0
 
AmitabhCommented:
well i think since there is nothing to update, there is no transaction and hence you are facing this error.

one possible way can be to use connectionobject.errors and check for this error and subsequently take an appropriate action

or to use @@trancount global variable in sql server to find out number of active transactions. like using
rs.open "select @@trancount" and then accessing rs(0) to see if there is any active transaction.

hope this should work though frankly i have not tried this at my end :-)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SireeshaAuthor Commented:
OOPS!I am using db2 not dql server
0
 
SireeshaAuthor Commented:
sorry,sql server misspelled it.
0
 
DanRollinsCommented:
Hi Sireesha,
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:

    Refund points and save as a 0-pt PAQ.

Sireesha, 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
0
 
SpideyModCommented:
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now