T-SQL statement causing error with insert/update
Hello. Hoping someone can give me some insight into why the following function is failing sometimes. I believe the table is deadlocking when multiple users try to make inserts at the same time. It works sometimes, other times it errors out and rollsback. I'm fairly new mssql. Unfortunately I don't even have the exact error right now as I'm waiting for my host company to give me some insights into how to view the error logs (and its working on my machine for now so I can't even debug). Anyway, hoping someone with experience will see something off the bat that will point me in the right direction. If it looks familiar its taken from your wonderful Instant Results book....
Public Shared Function FinalizeOrder(ByVal theShoppingCart As ShoppingCart, ByVal schoolId As Integer, ByVal lastName As String, ByVal firstName As String, ByVal grade As String, ByVal email As String) As Integer
Dim myTransaction As SqlClient.SqlTransaction = Nothing
Dim counter As Integer = 1
ReTry:
Try
Using myConnection As New SqlConnection(Config.ConnectionString)
myConnection.Open()
myTransaction = myConnection.BeginTransaction
Dim myCommand As SqlCommand = New SqlCommand( _
"sprocOrderHeaderInsertSingleItem", myConnection)
myCommand.Transaction = myTransaction
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.AddWithValue("@SchoolId", schoolId)
myCommand.Parameters.AddWithValue("@FirstName", firstName)
myCommand.Parameters.AddWithValue("@LastName", lastName)
myCommand.Parameters.AddWithValue("@Grade", grade)
myCommand.Parameters.AddWithValue("@Email", email)
Dim theReturnValue As SqlParameter = New SqlParameter()
theReturnValue.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(theReturnValue)
myCommand.ExecuteNonQuery()
Dim orderId As Integer = Convert.ToInt32(theReturnValue.Value)
Dim initials As String = Left(firstName, 1) + Left(lastName, 1)
Dim accessCode As String = initials + Convert.ToString(orderId)
' now update the orderheader with the access code
Dim myCommand1 As SqlCommand = New SqlCommand( _
"sprocOrderHeaderUpdateAccessCode", myConnection)
myCommand1.Transaction = myTransaction
myCommand1.CommandType = CommandType.StoredProcedure
myCommand1.Parameters.AddWithValue("@OrderId", orderId)
myCommand1.Parameters.AddWithValue("@AccessCode", accessCode)
myCommand1.ExecuteNonQuery()
' Now that we saved an order, let's save the order details as well
Dim myCommand2 As SqlCommand = New SqlCommand("sprocOrderDetailInsertSingleItem", myConnection)
myCommand2.Transaction = myTransaction
myCommand2.CommandType = CommandType.StoredProcedure
Dim orderHeaderIdParam As SqlParameter = _
New SqlParameter("OrderHeaderId", SqlDbType.Int)
myCommand2.Parameters.Add(orderHeaderIdParam)
Dim categoryItemIdParam As SqlParameter = _
New SqlParameter("categoryItemId", SqlDbType.Int)
myCommand2.Parameters.Add(categoryItemIdParam)
Dim lunchDateParam As SqlParameter = _
New SqlParameter("lunchDate", SqlDbType.DateTime)
myCommand2.Parameters.Add(lunchDateParam)
Dim priceParam As SqlParameter = _
New SqlParameter("price", SqlDbType.Money)
myCommand2.Parameters.Add(priceParam)
Dim quantityParam As SqlParameter = _
New SqlParameter("quantity", SqlDbType.Int)
myCommand2.Parameters.Add(quantityParam)
Dim lunchDateIdParam As SqlParameter = _
New SqlParameter("lunchDateId", SqlDbType.Int)
myCommand2.Parameters.Add(lunchDateIdParam)
For Each myOrderedCategoryItem As OrderedCategoryItem In theShoppingCart.Items
orderHeaderIdParam.Value = orderId
categoryItemIdParam.Value = myOrderedCategoryItem.CategoryItemId
lunchDateParam.Value = myOrderedCategoryItem.LunchDate
priceParam.Value = myOrderedCategoryItem.Price
quantityParam.Value = myOrderedCategoryItem.Quantity
lunchDateIdParam.Value = myOrderedCategoryItem.LunchDateId
myCommand2.ExecuteNonQuery()
Next
myTransaction.Commit()
Return orderId
End Using
Catch ex As Exception
myTransaction.Rollback()
' Pass up the error; it will be caught by the code in the Global.asax and the generic error page set up in web.config.
If counter = 1 Then
counter = counter + 1
GoTo ReTry
End If
Throw
End Try
End Function
Thanks in advance for your time.
|