Improve company productivity with a Business Account.Sign Up

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

ADO / Nested Transactions using SQL Server

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
scarraggi
Asked:
scarraggi
  • 2
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sbmcCommented:
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
 
sbmcCommented:
I think you may need to use the requery method on the recordset after the committrans.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
scarraggiAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Then check your datasource, there this property can be different from Server Settings
0
 
scarraggiAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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