Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:02 AM.


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