Solved

ADO / Nested Transactions using SQL Server

Posted on 2000-04-07
6
330 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 100 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…

687 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