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
InsertData() ' --> data is written to the database
' this function works properly
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?
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.