I love to use transactions(on Oracle) but last night I had the worst time

trying to figure out why I couldn't begin a transaction, run a few

statements, then do a commit or rollback on SQL server 2000.  I kept getting

an error saying the server could not begin the transaction because the

connection was in fire mode. 



Maybe we can open some discussion on this so I can understand it better.  I

did manage to get my transactions working but only by creating a new

connection just for the transactions.  Oracle doesn't seem to have this

problem but it seems SQL Server does. I wasn't thrilled about opening two

connections on a single asp page. 



Below is an example of how I got the transactions to work.  I run the

function dbConnect at the start of each page.  You'll notice that the

function DML is opening a new connection just for the transaction.



The problem was... If I took the oCon1 connection out of the DML function

and used the oCon object instead, the transactions would not work.



I do run some regular SQL statements before opening the transaction.  So,

maybe by creating regular recordset objects with the oCon object it wouldn't

allow me to use the same oCon object for the transactions.



Any thoughts on this??



-------------some.asp page--------------------------------

Call dbConnect()

sql = "update profile set "

sql = sql & "address = '" & addr1 & "',"

sql = sql & "city = '" & city & "',"

sql = sql & "state = '" & state & "',"

sql = sql & "zip = '" & zip & "',"

sql = sql & "area_code = '" & areacode & "',"

sql = sql & "hphone = '" & phone & "',"

sql = sql & "semail = '" & email2 & "',"

sql = sql & "reference = '" & raccount & "',"

sql = sql & "rname = '" & rname & "' "

sql = sql & " where account = '" & account & "'"

		

sql1 = "update account set "

sql1 = sql1 & "fname = '" & fname & "',"

sql1 = sql1 & "lname = '" & lname & "',"

sql1 = sql1 & "email = '" & email & "'"

sql1 = sql1 & " where account = '" & account & "'"

success = DML(2)

-----------------------------------------------------------------------



---------------inc_ado.asp page------------------------------------

Public Function dbConnect()

	'On error resume Next

	Set oCon=Server.CreateObject("ADODB.Connection")

	sConnectString = "DSN=network1singles;" 

	oCon.CursorLocation = 3

	oCon.Errors.Clear

	oCon.open sConnectString

		if oCon.Errors.Count > 0 then

			ADOErrors()

			if sError <> "" then



				response.redirect "dberror.asp?serror=" &

sError

			end if

		end if	

	on error goto 0

End Function





Public Function DML(fsql)

	Dim bSuccess

	Set oCon1=Server.CreateObject("ADODB.Connection")

	sConnectString = "DSN=network1singles;" 

	oCon1.CursorLocation = 3

	oCon1.Errors.Clear

	oCon1.open sConnectString

			With oCon1

				.BeginTrans

				Select Case fsql

					Case 1

						.Execute (SQL)

					Case 2

						.Execute (SQL)

						.Execute (SQL1)

					Case 3

						.Execute (SQL)

						.Execute (SQL1)

						.Execute (SQL2)

					Case 4

						.Execute (SQL)

						.Execute (SQL1)

						.Execute (SQL2)

						.Execute (SQL3)



					Case 5

						.Execute (SQL)

						.Execute (SQL1)

						.Execute (SQL2)

						.Execute (SQL3)

						.Execute (SQL4)



				End Select

			End With

			ADOErrors()

			if sError <> "" then



					oCon1.RollbackTrans

					bSuccess = false

			else

					oCon1.CommitTrans

					bSuccess = true	

			end if

			If isObject(oCon1) and typename(oCon1) <> "Nothing"

then

				If (oCon1.State = 1) then

      				oCon1.close

				End if

				Set oCon1 = nothing

			End If

		DML = bSuccess 

End Function

------------------------------------------