troubleshooting Question

Transaction / Locking ?

Avatar of Jess31
Jess31 asked on
Microsoft SQL ServerVisual Basic.NET
12 Comments1 Solution54 ViewsLast Modified:
I have this vb.net  code that is trying to do a simple read Select outside of the Transaction. But it fails. I would think there should not be a problem do this. What am I doing wrong?
sql server database is set to compatibility 120
   myTransaction = SQLConn.BeginTransaction(IsolationLevel.ReadUncommitted)
                Dim sql As String = "Execute dbo.insertCancelInvoicePlan @InvoiceHeaderID, @PFCustomerOrderNumber"
                Using comm As New SqlCommand(sql, SQLConn)
                    comm.CommandTimeout = 300
                    comm.Transaction = myTransaction
                    For Each InvoiceHeaderID As Integer In InvoiceHeaderIDList
                        Dim CustomerOrderNumber As String = GetRandomPO(8)
                        While True
                            ' next line does a select against a table that is used in this transaction
                            If IsNewCustomerPO(CustomerOrderNumber) = True Then
                                Exit While
                            End If
                            CustomerOrderNumber = GetRandomPO(8)
                        End While
                        comm.Parameters.AddWithValue("@InvoiceHeaderID", InvoiceHeaderID)
                        comm.Parameters.AddWithValue("@PFCustomerOrderNumber", CustomerOrderNumber)
                        comm.ExecuteNonQuery()
                        comm.Parameters.Clear()
                    Next
                End Using
ASKER CERTIFIED SOLUTION
Jess31

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros