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.