Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 7th, 2007, 04:48 PM
saf saf is offline
Authorized User
 
Join Date: Nov 2006
Location: , , .
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating an INSERT INTO SQL statement for 2 tables sburgess Access 1 April 18th, 2007 08:52 AM
Help with SQL Insert statement in VBA Code ausmoran Access VBA 1 December 28th, 2006 06:16 PM
insert and/or update statement carswelljr SQL Language 3 September 28th, 2006 10:03 AM
Error when trying to use an UPDATE statement wayne62682 Access VBA 2 April 4th, 2006 07:50 PM
SQL Insert statement with apostrophe xgbnow SQL Language 11 June 18th, 2003 08:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.