I am using ASP.NET / VB.NET to access SQL Server 2000. I am accessing an table to insert / update which is heavily hit during peak hours of usage. The following is the piece of code which is does not consistently gives me the error. I had used DataAccessApp block provided by Microsoft. The piece of code is :
Public Function MakeOrder(ByRef OrderDetails As OrderInfo) As Boolean
Dim myCon As New System.Data.SqlClient.SqlC
onnection(
Connection
Str)
Dim myTrans As SqlClient.SqlTransaction
Try
myCon.Open()
Dim sProcname As String = "CreateNewOrder_sp"
myTrans = myCon.BeginTransaction("Ma
keOrder")
paramOrderNo = New SqlParameter("@OUTI_ORDERN
O", 0)
paramOrderNo.Direction = ParameterDirection.Output
ParamExtn = New SqlParameter("@OUTI_EXTENS
ION", 0)
ParamExtn.Direction = ParameterDirection.Output
SqlHelper.ExecuteNonQuery(
myTrans, CommandType.StoredProcedur
e, sProcname, _
paramOrderNo, _
ParamExtn, _
'Other Parameters
)
' Call the detail section inserts
Dim DRow As DataRow
sProcname = "CreateNewOrderItems_sp"
For Each DRow In OrderDetails.DetailSection
.Rows
SqlHelper.ExecuteNonQuery(
myTrans, CommandType.StoredProcedur
e, sProcname, _
'Other Parameters
)
Next
' Everything works fine, commit the changes.
myTrans.Commit()
Return True
Catch Ex As Exception
' Rollback the transaction
myTrans.Rollback("MakeOrde
r")
Throw Ex
Finally
myCon.Close()
End Try
Emd Function
==========================
==========
==========
========
During Peak hours I get the following Error Message
==========================
==========
==========
=======
Server Error in '/OrderEntry' Application
This SqlTransaction has completed; it is no longer usable.
Description:An unhandled excepion occured during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.InvalidOper
ationExcep
tion: This SqlTransaction has completed;it is no longer usable.
==========================
==========
==========
==========
The problem is this does not happen consistently and we are not able to identify what the problem is to fix. We were able to see a lot of locks during the process and some times few users blocked each other, but there was never a deadlock situation. Can anyone provide light on what is going wrong.
Thanks
View the Solution FREE for 7 Days
