how to use transactions

[roro728]

Hi,

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 :

    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

[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.

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.

[roro728]

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.

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?

    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

[jmcilhinney]

First up, don't do this:

        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:

        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.