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.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|||thanks.

I'm sorry but I am not familiar with the SQL Server environment. Where can I look at the value of @.@.TRANCOUNT??|||Open a recordset, or use the connection's execute method to see the contents of @.@.trancount.

-PatP