Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: problem inserting data to a database using ADODB

    Hi! I have a vb.net program that writes data to an SQL Server database using ADODB. The problem is if I put the code that writes data to the database in between BeginTrans() and CommitTrans(), the database is not updated. Here is the flow of my program:

    Dim connection as ADODB.Connection
    'setup the connection to the SQL Server database here

    connection.BeginTrans()

    InsertData() ' --> data is written to the database
    ' this function works properly

    connection.CommitTrans()



    If I comment out the BeginTrans() and CommitTrans() functions, the data is properly inserted into the database.

    Does an SQL Server database requires special settings to support transactions?

    Please help.
    And thank you in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess is that your BEGIN TRAN and COMMIT TRAN counts are out of sequence, meaning that you've probably got at least one more BEGIN TRAN than you've got COMMIT operations, which leaves the INSERT in a pending state until you close the connection, after which the INSERT rolls back because there is still a pending transaction.

    Try selecting the value of @@TRANCOUNT to see where your transactions stand at different points in the process.

    -PatP

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    thanks.

    I'm sorry but I am not familiar with the SQL Server environment. Where can I look at the value of @@TRANCOUNT??

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Open a recordset, or use the connection's execute method to see the contents of @@trancount.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •