?
Solved

ADO / Nested Transactions using SQL Server

Posted on 2000-04-07
6
Medium Priority
?
333 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
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…
Suggested Courses

650 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