I have a problem with Visual Basic

[abdullauthan]

I have a problem with Visual Basic Statements while updating multiple tables in SQL Server 7

For Ex: when I issue the below statements,

cnJSA.Execute "Begin Transaction LockRecord"
UpdateStatus = True

cmJSA.CommandText = "sp_JSA_LockJSA"
cmJSA.Parameters("@JSANo&quot.Value = txtJSANo.Text
cmJSA.Parameters("@LockStatus&quot.Value = LockStatus
cmJSA.Execute

cnJSA.Execute "Commit Transaction LockRecord"


I get the error:
[Microsoft][ODBC SQL Server Driver][SQL Server]The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This used to work until recently. Suddently it won't work for some reason. Any help on this?

Recently, I got an Alert with the Error No. 9002. Is it the reason. I Truncated the Transaction Log for tempdb. Still the same problem.

 

[billchris]

ADO has built in transaction management, so you should begin, commit and abort transactions using the ADO Connection object. Like this:

cmJSA.CommandText = "sp_JSA_LockJSA"
cmJSA.Parameters("@JSANo").Value = txtJSANo.Text
cmJSA.Parameters("@LockStatus").Value = LockStatus

cnJSA.BeginTrans
UpdateStatus = True
cmJSA.Execute
cnJSA.CommitTrans

Just one note. It is usually best to start the transaction as late as possible and then commit it as soon as possible, because the longer a transaction remains active the more likely it will be involved in record locking conflicts on the database.

“I have always found that plans are useless, but planning is indispensable.” --Dwight Eisenhower

 

[abdullauthan]

I tried and got the following error.

'Transaction cannot start because more than one ODBC connection is in use'

I use only one connection and that is cnJSA which uses the Domain Account to access SQL Server using Windows Authentication.

 

[billchris]

Sorry, abdullauthan

I can't reproduce your problem. I've tried but I'm using SQL 2000.

Here are some things to look at.

Make sure that the ActiveConnection property of the cmJSA command object is set to the cnJSA connection object.

Make sure that you don't have any open and/or connected recordset objects. (This is probably not it, but I've seen similar problems caused by trying to work with more than one recordset via ODBC."

You said, "This used to work until recently. Suddently it won't work for some reason." What changed? Did you update some other code? Was a service pack installed on SQL server? Did you, or someone, change the ODBC connection settings? Did you install a service pack on the client computer? Did you upgrade to a differenct version of MDAC?
Anything.

Regards,
Bill

“I have always found that plans are useless, but planning is indispensable.” --Dwight Eisenhower