?
Solved

ADO / Nested Transactions using SQL Server

Posted on 2000-04-07
6
Medium Priority
?
331 Views
Last Modified: 2013-11-23
We currently migrated from DAO to ADO and I'm having problems when using Nested Transactions with SQL Server.  The recordset (cursor) appears to get closed after submitting a commit.  Why does this happen?  Is there a property setting in the connection string I should be aware of?  My connect string is as follows:

"Provider=sqloledb;Data Source=MARYANNE;Initial Catalog=Labor32;Trusted_Connection=Yes"

The cursor type is set to adOpenKeyset
   Set recsADO.ActiveConnection = UserConn
   recsADO.CursorType = adOpenKeyset
   recsADO.Open inSQL

This also happens when I use the update method... I cannot go back and refer to my recordset.

Any help in this area would be greatly appreciated.  

Thank you.
0
Comment
Question by:scarraggi
[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
  • 2
  • 2
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2695812
The connection property "CLOSE CURSOR ON COMMIT" must be set to false.
Unfortunately, i don't remember the property name in ADO, but you will find it.
This property exists on the server (as default), but can be adjusted per connection.
In SQL 6.5, this value is by default true, in SQL 7 it's false.

Good luck
0
 
LVL 2

Expert Comment

by:sbmc
ID: 2696788
Try using the connection object instead of the recordset object when doing transactions eg

rs.open "Select * from MyTable",cn,adOpenKeySet
conn.BeginTrans
rs.Addnew
....
....
rs.Update
conn.CommitTrans
0
 
LVL 2

Expert Comment

by:sbmc
ID: 2696796
I think you may need to use the requery method on the recordset after the committrans.
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!

 

Author Comment

by:scarraggi
ID: 2701590
I appreciate the replies; However, I'm still getting the same results.  I found the property setting "CLOSE CURSOR ON COMMIT" on the SQL Server... It is not turned on.

If it's any help, I'm running with SQL Server Version 7
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 2702941
Then check your datasource, there this property can be different from Server Settings
0
 

Author Comment

by:scarraggi
ID: 2707795
I figured out a work around this problem.  It seems as though this provider doesn't support nested transactions.  I've added logic in my code (routine which executes the transaction) to check the transaction level and only do the transaction if a certain criteria was true.  So far so good!
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.

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

771 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