Avatar of Jess31
Jess31

asked on 

Transaction / Locking ?

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

Open in new window

Microsoft SQL ServerVisual Basic.NET

Avatar of undefined
Last Comment
Vitor Montalvão

8/22/2022 - Mon