• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

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