ADO and Transactions
Posted: 03.09.2008, 10:14
Hello,
I have a problem with transactions and ADO.
I have two programs one that connects to a MSSQL 2005 server and the other that connected to an MSSQL 2000 server. I was using mssql (ntwdblib.dll) protocol to connect to the servers and that worked fine with the transactions but because it truncated strings to 255 chars, we opted to switch to ADO, which didn't have this problem.
Now when we run the same code that worked fine with mssql protocol I get the error:
An SQL Error has occurred
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1
BEGIN TRANSACTION
When the user starts editing data on our screen we add the above SQL.Text into a TZQuery and then execute the statement, which causes an error. What we want to do is the same as before, so we'd want multiple ExecSQL statements using the same connection:
TZQuery -> Begin Transaction SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Commit Transaction SQL Statement -> ExecSQL
All TZQuery objects use the same TZConnection.
But it seems that with ADO you can only do this within one TZQuery and one execute otherwise you get the above error. Has anyone else had this problem with transactions? I've tried using the StartTransaction / Commit / Rollback on the TZConnection connection itself, but this also didn't work.
From reading other posts I've tried changing the autocommit flag, but again this either produced the same error or new errors. I think its the scope of the transaction that needs to be changed, so the transaction can be over multiple ExecSQL statements, but I have no idea how to do this.
I was using Zeos components v6.5.1, but I upgraded to v6.6.3 - stable, to see if this helped, but unfortunately it didn't
Any help / ideas would be appreciated.
Thanks
Phoenix
I have a problem with transactions and ADO.
I have two programs one that connects to a MSSQL 2005 server and the other that connected to an MSSQL 2000 server. I was using mssql (ntwdblib.dll) protocol to connect to the servers and that worked fine with the transactions but because it truncated strings to 255 chars, we opted to switch to ADO, which didn't have this problem.
Now when we run the same code that worked fine with mssql protocol I get the error:
An SQL Error has occurred
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1
BEGIN TRANSACTION
When the user starts editing data on our screen we add the above SQL.Text into a TZQuery and then execute the statement, which causes an error. What we want to do is the same as before, so we'd want multiple ExecSQL statements using the same connection:
TZQuery -> Begin Transaction SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Insert / Update SQL Statement -> ExecSQL
TZQuery -> Commit Transaction SQL Statement -> ExecSQL
All TZQuery objects use the same TZConnection.
But it seems that with ADO you can only do this within one TZQuery and one execute otherwise you get the above error. Has anyone else had this problem with transactions? I've tried using the StartTransaction / Commit / Rollback on the TZConnection connection itself, but this also didn't work.
From reading other posts I've tried changing the autocommit flag, but again this either produced the same error or new errors. I think its the scope of the transaction that needs to be changed, so the transaction can be over multiple ExecSQL statements, but I have no idea how to do this.
I was using Zeos components v6.5.1, but I upgraded to v6.6.3 - stable, to see if this helped, but unfortunately it didn't
Any help / ideas would be appreciated.
Thanks
Phoenix