how to use transactions

Hi, :wave:

I am trying to use SqlTransaction object in my vb code for an application to insert vendor info.
The code runs till inserting a transaction and then gives following exception:

"Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

Any idea what's wrong with this? The code is as follows :

Code:

---

    Public Shared Function AddVendor(ByVal vendor As Vendor) As Integer
        Dim connection As SqlConnection = PayablesDB.GetConnection
        Dim vendorTransaction As SqlTransaction
        connection.Open()
        vendorTransaction = connection.BeginTransaction

        Dim insertStatement As String _
            = "INSERT Vendors " _
                & "(Name, Address1, Address2, City, State, ZipCode, Phone, " _
                & "ContactFName, ContactLName, DefaultTermsID, DefaultAccountNo) " _
            & "VALUES (@Name, @Address1, @Address2, @City, @State, " _
                & "@ZipCode, @Phone, @ContactFName, @ContactLName, " _
                & "@DefaultTermsID, @DefaultAccountNo)"
        Dim insertCommand As New SqlCommand(insertStatement, connection)
        insertCommand.Connection = connection
        insertCommand.Transaction = vendorTransaction

        insertCommand.Parameters.AddWithValue("@Name", vendor.Name)
        insertCommand.Parameters.AddWithValue("@Address1", vendor.Address1)

        If vendor.Address2 = "" Then
            insertCommand.Parameters.AddWithValue("@Address2", DBNull.Value)
        Else
            insertCommand.Parameters.AddWithValue("@Address2", vendor.Address2)
        End If
        insertCommand.Parameters.AddWithValue("@City", vendor.City)
        insertCommand.Parameters.AddWithValue("@State", vendor.State)
        insertCommand.Parameters.AddWithValue("@ZipCode", vendor.ZipCode)
        If vendor.Phone = "" Then
            insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value)
        Else
            insertCommand.Parameters.AddWithValue("@Phone", vendor.Phone)
        End If
        If vendor.ContactFName = "" Then
            insertCommand.Parameters.AddWithValue("@ContactFName", DBNull.Value)
        Else
            insertCommand.Parameters.AddWithValue("@ContactFName", vendor.ContactFName)
        End If
        If vendor.ContactLName = "" Then
            insertCommand.Parameters.AddWithValue("@ContactLName", DBNull.Value)
        Else
            insertCommand.Parameters.AddWithValue("@ContactLName", vendor.ContactLName)
        End If

        insertCommand.Parameters.AddWithValue("@DefaultTermsID", vendor.DefaultTermsID)
        insertCommand.Parameters.AddWithValue("@DefaultAccountNo", vendor.DefaultAccountNo)

        Try
            insertCommand.Transaction = vendorTransaction
            insertCommand.Connection = connection
            insertCommand.ExecuteNonQuery()
            Dim selectStatement As String _
                = "SELECT IDENT_CURRENT('Vendors') FROM Vendors"
            Dim selectCommand As New SqlCommand(selectStatement, connection)
            Dim vendorID As Integer = CInt(selectCommand.ExecuteScalar)
            Return vendorID
            vendorTransaction.Commit()
        Catch ex As SqlException
            vendorTransaction.Rollback()
            Throw ex
        Finally
            connection.Close()
        End Try
    End Function

---

Once you begin a transaction on a connection, any command you execute over that connection has to be enlisted in that transaction. You're setting the Transaction property for 'insertCommand' (twice, I might add) but not for 'selectCommand'.

That said, you don't even need that 'selectCommand'. You can execute the both the INSERT and SELECT with one command, e.g.

sql Code:

INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2); SELECT @ID = SCOPE_IDENTITY

You add the extra parameter to your command and set its Direction to Output.

Quote:

---

Originally Posted by jmcilhinney

Once you begin a transaction on a connection, any command you execute over that connection has to be enlisted in that transaction. You're setting the Transaction property for 'insertCommand' (twice, I might add) but not for 'selectCommand'.

That said, you don't even need that 'selectCommand'. You can execute the both the INSERT and SELECT with one command, e.g.

sql Code:

INSERT INTO MyTable (Column1, Column2) VALUES (@Column1, @Column2); SELECT @ID = SCOPE_IDENTITY

You add the extra parameter to your command and set its Direction to Output.

---

Thank you for your tip,
But I don't think I'm getting it, unfortunately.

I slightly have modified my code, and now am having a different problem where the new record I enter does not seem to get assigned of a new ID that the system should automatically be assigning.
do you still see any issue with this coding below?

Code:

---

    Public Shared Function AddVendor(ByVal vendor As Vendor) As Integer
        Dim connection As SqlConnection = PayablesDB.GetConnection
        Dim vendorTransaction As SqlTransaction
        connection.Open()
        vendorTransaction = connection.BeginTransaction

        Dim insertStatement As String _
            = "INSERT Vendors " _
                & "(Name, Address1, Address2, City, State, ZipCode, Phone, " _
                & "ContactFName, ContactLName, DefaultTermsID, DefaultAccountNo) " _
            & "VALUES (@Name, @Address1, @Address2, @City, @State, " _
                & "@ZipCode, @Phone, @ContactFName, @ContactLName, " _
                & "@DefaultTermsID, @DefaultAccountNo)"
        Dim insertCommand As New SqlCommand(insertStatement, connection)



        Try
            insertCommand.Connection = connection
            insertCommand.Transaction = vendorTransaction

            insertCommand.Parameters.AddWithValue("@Name", vendor.Name)
            insertCommand.Parameters.AddWithValue("@Address1", vendor.Address1)

            If vendor.Address2 = "" Then
                insertCommand.Parameters.AddWithValue("@Address2", DBNull.Value)
            Else
                insertCommand.Parameters.AddWithValue("@Address2", vendor.Address2)
            End If
            insertCommand.Parameters.AddWithValue("@City", vendor.City)
            insertCommand.Parameters.AddWithValue("@State", vendor.State)
            insertCommand.Parameters.AddWithValue("@ZipCode", vendor.ZipCode)
            If vendor.Phone = "" Then
                insertCommand.Parameters.AddWithValue("@Phone", DBNull.Value)
            Else
                insertCommand.Parameters.AddWithValue("@Phone", vendor.Phone)
            End If
            If vendor.ContactFName = "" Then
                insertCommand.Parameters.AddWithValue("@ContactFName", DBNull.Value)
            Else
                insertCommand.Parameters.AddWithValue("@ContactFName", vendor.ContactFName)
            End If
            If vendor.ContactLName = "" Then
                insertCommand.Parameters.AddWithValue("@ContactLName", DBNull.Value)
            Else
                insertCommand.Parameters.AddWithValue("@ContactLName", vendor.ContactLName)
            End If

            insertCommand.Parameters.AddWithValue("@DefaultTermsID", vendor.DefaultTermsID)
            insertCommand.Parameters.AddWithValue("@DefaultAccountNo", vendor.DefaultAccountNo)


            vendorTransaction.Commit()
        Catch ex As Exception
            vendorTransaction.Rollback()
            Throw ex
        Finally
            connection.Close()
        End Try
End Function

---

First up, don't do this:

Code:

---

        Catch ex As Exception
            vendorTransaction.Rollback()
            Throw ex

---

If you do that you'll lose the whole call stack. Unless you specifically want the current method to appear to be the original source of the exception, just do this:

Code:

---

        Catch ex As Exception
            vendorTransaction.Rollback()
            Throw

---

As for the question, you have now removed your query that was retrieving the generated ID but you haven't done what I suggested instead.