Dataset Transaction problem

rainbow_man

Member
Joined
Nov 9, 2009
Messages
5
Programming Experience
Beginner
Hi

I have a problem with implementing transactions with my vb.net application. My code goes like this:

public Function processCoursePayment()
---
--- code
---

mancosa.payCourse()

---
--- code
---
End Function

Code:
VB.NET:
public Function payCourse()
Dim sd As DataSet1TableAdapters.StudentPaymentTableAdapter = New DataSet1TableAdapters.StudentPaymentTableAdapter
        Dim dsPayCourse As DataSet1.StudentPaymentDataTable = New DataSet1.StudentPaymentDataTable
        Dim connection = sd.Connection
        Dim transaction As SqlTransaction
        Dim studentRow As DataRow = dsPayCourse.NewRow
        studentRow.Item("amount") = amount
        studentRow.Item("date") = payDate
        studentRow.Item("studentID") = studentID
        studentRow.Item("courseFeesID") = courseFeesID
        studentRow.Item("paymentMethodID") = paymentMethodID
        dsPayCourse.AddStudentPaymentRow(studentRow)
        Dim count As Integer = dsPayCourse.Count
        connection.Open()
        transaction = connection.BeginTransaction(IsolationLevel.Serializable)
        sd.Adapter.UpdateCommand.Transaction = transaction
        sd.Adapter.InsertCommand.Transaction = transaction
        Try
            sd.Adapter.Update(dsPayCourse)
            dsPayCourse.AcceptChanges()
            transaction.Commit()
        Catch ex As Exception
            transaction.Rollback()
            Console.WriteLine(ex.Message)
        Finally
            connection.Close()
        End Try
End Function

I had the exception that 'The Transaction property of the command has not been initialized.' But this is solved once I inserted the
'sd.Adapter.InsertCommand.Transaction = transaction' part.

However the problem is that when I run in debugger mode and I stop the running of the program before the commit() part, my data table still gets updated and the results are in the database, hence it has not performed the rollback properly. Also, like shown above when the payCourse() function exits, there are still code to be run in the main processCoursePayment() function. So I would want that if a rollback occured in the inner function, then the whole processCoursePayment() is aborted.

Anyone can advise on this please? Thanks.
 
Top Bottom