Using Database Transactions from simple ASP

Originally posted by : Allan (aspielma@yahoo.com)Using Database Transactions from simple ASP (NOT MAllan - 7/23/2001 2:12:10 PM-------------------------------------------------------------------------------- I am trying to use database transactions from ASP. It is not an MTS environment, nor am I using VB components. I hope this is possible. I saw some very helpful info at: http://www.4guysfromrolla.com/webtech/081800-1.shtml I make a connection , then run a few updates using command objects, and no recordsets. Howeverm when I try to use objConn.BeginTrans, I get the following error at the SECOND command object: Microsoft OLE DB Provider for ODBC Drivers error '80004005' Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets. I am not using any record set objects here! Here is my code: 'Make database connection. Set dbWebReg = OpenNewConnection(Application("WebRegConnectString")) dbWebReg.BeginTrans ' first database action Set vDBCommand = Server.CreateObject("ADODB.Command") Set vDBCommand.ActiveConnection = dbWebReg vDBCommand.CommandText = "prcUpdateStaId" vDBCommand.CommandType = adCmdStoredProc vDBCommand.Parameters.Append vDBCommand.CreateParameter("@RegCde", adInteger, adParamInput, , Session("RegCode")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@StaId", adInteger, adParamInput, , -2) vDBCommand.Execute Set vDBCommand = Nothing ' second database action Set vDBCommand = Server.CreateObject("ADODB.Command") Set vDBCommand.ActiveConnection = dbWebReg vDBCommand.CommandText = "prcInsertVersion" vDBCommand.CommandType = adCmdStoredProc '*** Load up the parameters. vDBCommand.Parameters.Append vDBCommand.CreateParameter("@RegCde", adInteger, adParamInput, , Session("RegCode")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@OrgWrkTitle", adChar, adParamInput, 60, Ucase(Request.QueryString("txtTitle"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@OrgIswCde", adChar, adParamInput, 11, Ucase(Request.QueryString("txtISWC"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@IsoLngCde", adChar, adParamInput, 2, Request.QueryString("cboISOLanguage")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@Wtr1FstNa", adChar, adParamInput, 30, Ucase(Request.QueryString("txtFirstName"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@Wtr1LstNa", adChar, adParamInput, 45, Ucase(Request.QueryString("txtLastName"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@SrcDes", adChar, adParamInput, 60, Request.QueryString("txtSource")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@CreUid", adChar, adParamInput, 15, Session("UserName")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UpdUid", adChar, adParamInput, 15, Session("UserName")) vDBCommand.Execute Set vDBCommand = Nothing

Originally posted by : Tim Curtin (Tcurtin@nhhartford.com)use 2 different command objects------------Allan at 7/23/2001 1:21:12 PMUsing Database Transactions from simple ASP (NOT MAllan - 7/23/2001 2:12:10 PM-------------------------------------------------------------------------------- I am trying to use database transactions from ASP. It is not an MTS environment, nor am I using VB components. I hope this is possible. I saw some very helpful info at: http://www.4guysfromrolla.com/webtech/081800-1.shtml I make a connection , then run a few updates using command objects, and no recordsets. Howeverm when I try to use objConn.BeginTrans, I get the following error at the SECOND command object: Microsoft OLE DB Provider for ODBC Drivers error '80004005' Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets. I am not using any record set objects here! Here is my code: 'Make database connection. Set dbWebReg = OpenNewConnection(Application("WebRegConnectString")) dbWebReg.BeginTrans ' first database action Set vDBCommand = Server.CreateObject("ADODB.Command") Set vDBCommand.ActiveConnection = dbWebReg vDBCommand.CommandText = "prcUpdateStaId" vDBCommand.CommandType = adCmdStoredProc vDBCommand.Parameters.Append vDBCommand.CreateParameter("@RegCde", adInteger, adParamInput, , Session("RegCode")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@StaId", adInteger, adParamInput, , -2) vDBCommand.Execute Set vDBCommand = Nothing ' second database action Set vDBCommand = Server.CreateObject("ADODB.Command") Set vDBCommand.ActiveConnection = dbWebReg vDBCommand.CommandText = "prcInsertVersion" vDBCommand.CommandType = adCmdStoredProc '*** Load up the parameters. vDBCommand.Parameters.Append vDBCommand.CreateParameter("@RegCde", adInteger, adParamInput, , Session("RegCode")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@OrgWrkTitle", adChar, adParamInput, 60, Ucase(Request.QueryString("txtTitle"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@OrgIswCde", adChar, adParamInput, 11, Ucase(Request.QueryString("txtISWC"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@IsoLngCde", adChar, adParamInput, 2, Request.QueryString("cboISOLanguage")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@Wtr1FstNa", adChar, adParamInput, 30, Ucase(Request.QueryString("txtFirstName"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@Wtr1LstNa", adChar, adParamInput, 45, Ucase(Request.QueryString("txtLastName"))) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@SrcDes", adChar, adParamInput, 60, Request.QueryString("txtSource")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@CreUid", adChar, adParamInput, 15, Session("UserName")) vDBCommand.Parameters.Append vDBCommand.CreateParameter("@UpdUid", adChar, adParamInput, 15, Session("UserName")) vDBCommand.Execute Set vDBCommand = Nothing