Need some help in OledbTransaction

[gautamshaw]

I did this code:

 Private Sub Transfer_Btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Transfer_Btn.Click
         Dim oleTran As OleDb.OleDbTransaction
        Try
            Using con As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=BankAccount.mdb")
                con.Open()
                oleTran = con.BeginTransaction
                cmd = New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance-@amount where BankName=@bankname and  AccountNo=@accountno", con)
                cmd.Parameters.AddWithValue("@amount", TextBox3.Text)
                cmd.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                cmd.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                cmd.Transaction = oleTran
                Dim rowseffected As Integer = cmd.ExecuteNonQuery()
                Dim cmd1 As New OleDbCommand
                cmd1 = New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance+@amount where bankname=@BankName and  AccountNo=@accountno", con)
                cmd1.Parameters.AddWithValue("@amount", TextBox3.Text)
                cmd1.Parameters.AddWithValue("@bankname", ComboBox3.Text)
                cmd1.Parameters.AddWithValue("@accountno", ComboBox4.Text)
                cmd1.Transaction = oleTran
                Dim rowseffected1 As Integer = cmd1.ExecuteNonQuery()
                Dim cmd2 As New OleDbCommand
                cmd2 = New OleDbCommand("Select CurrentBalance from BankAccount where BankName=@bankname and AccountNo=@accountno", con)
                cmd2.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                cmd2.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                Dim CurrentBal As Decimal = CDec(cmd2.ExecuteScalar())
                Dim Amount As Decimal
                Decimal.TryParse(TextBox3.Text, Amount)
                If CurrentBal < Amount Then
                    oleTran.Rollback()
                End If
                If ComboBox1.Text = ComboBox3.Text And ComboBox2.Text = ComboBox4.Text Then
                    MsgBox("You cant transfer the money in the same account")
                End If
                oleTran.Commit()
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
 End Sub

I am getting this error message.....
Attachment 72609
This is the first time i am working with the oledbtransaction.......
Where lies the fault in my above code?
Need some help!!!

[gautamshaw]

Attachment 72610

[jmcilhinney]

If you start a transaction on a connection then EVERY command that uses that connection must be enrolled in the transaction. You're setting the Transaction properties of 'cmd' and 'cmd1' but not of 'cmd2'.

Having said that, there's really no need for that query to be inside the transaction. In fact, why even create cmd and cmd1 if the transfer can't be performed because the amount exceeds the balance of the source account? Your code is structured incorrectly. It should be like this:

Create connection
Create query
Open connection
Execute query
If transfer can continue
    Create transaction
    Create update commands
    Perform transfer
    If transfer successful
        Commit transaction
    Else
        Rollback transaction
Close connection

As you can see, you only ever do as much work as you need to. The transaction and the update commands never even get created is the query indicates that they're not needed.

[gautamshaw]

I did upto this....

  Private Sub Transfer_Btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Transfer_Btn.Click
        Dim oleTran As OleDb.OleDbTransaction
        Try
            Using con As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=BankAccount.mdb") 'Creating a Connection
                oleTran = con.BeginTransaction
                cmd = New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance-@amount where BankName=@bankname and  AccountNo=@accountno", con) 'Creating a query
                cmd.Parameters.AddWithValue("@amount", TextBox3.Text)
                cmd.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                cmd.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                Dim cmd1 As New OleDbCommand
                cmd1 = New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance+@amount where bankname=@BankName and  AccountNo=@accountno", con) 'Creating a query
                cmd1.Parameters.AddWithValue("@amount", TextBox3.Text)
                cmd1.Parameters.AddWithValue("@bankname", ComboBox3.Text)
                cmd1.Parameters.AddWithValue("@accountno", ComboBox4.Text)
                con.Open() 'Open connection
                Dim rowseffected As Integer = cmd.ExecuteNonQuery() 'Executing cmd
                Dim rowseffected1 As Integer = cmd1.ExecuteNonQuery() 'Executing cmd1
                Dim cmd2 As New OleDbCommand
                cmd2 = New OleDbCommand("Select CurrentBalance from BankAccount where BankName=@bankname and AccountNo=@accountno", con)
                cmd2.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                cmd2.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                Dim CurrentBal As Decimal = CDec(cmd2.ExecuteScalar())
                Dim Amount As Decimal
                Decimal.TryParse(TextBox3.Text, Amount)
                If CurrentBal < Amount Then 'Denotes whether transfer can coninue or not
                    cmd.Transaction = oleTran 'Creating transaction

What does the

Create query

refers?
Is it the update query?
Then why should i

 Create update commands

after the

 Create transaction

?

I am having a mix up between the Create query and Create update commands

[jmcilhinney]

The update commands are the commands that perform the updates. The query is the command that performs the select. Many people use the term "query" as a general term for any SQL statement but that is incorrect. Only a SELECT statement is a query.

[gautamshaw]

I gave a try with this:

Private Sub Transfer_Btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Transfer_Btn.Click
        Dim oleTran As OleDb.OleDbTransaction
        Try
            Using con As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=BankAccount.mdb") 'Creating a Connection
                Dim cmd2 As New OleDbCommand
                cmd2 = New OleDbCommand("Select CurrentBalance from BankAccount where BankName=@bankname and AccountNo=@accountno", con) 'Creating a query
                cmd2.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                cmd2.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                con.Open() 'Open connection
                Dim CurrentBal As Decimal = CDec(cmd2.ExecuteScalar()) 'Executing the query
                Dim Amount As Decimal
                Decimal.TryParse(TextBox3.Text, Amount)
                If CurrentBal > Amount Then 'Denotes whether transfer can coninue or not
                    oleTran = con.BeginTransaction 'Creating transaction
                    cmd = New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance-@amount where BankName=@bankname and  AccountNo=@accountno", con) 'Creating a query
                    cmd.Parameters.AddWithValue("@amount", TextBox3.Text)
                    cmd.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                    cmd.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                    Dim cmd1 As New OleDbCommand
                    cmd1 = New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance+@amount where bankname=@BankName and  AccountNo=@accountno", con) 'Creating a query
                    cmd1.Parameters.AddWithValue("@amount", TextBox3.Text)
                    cmd1.Parameters.AddWithValue("@bankname", ComboBox3.Text)
                    cmd1.Parameters.AddWithValue("@accountno", ComboBox4.Text)
                    Dim rowseffected As Integer = cmd.ExecuteNonQuery() 'Executing cmd
                    Dim rowseffected1 As Integer = cmd1.ExecuteNonQuery() 'Executing cmd1
                    If rowseffected <> 1 And rowseffected1 <> 1 Then 'Whether transaction is succesful or not
                        oleTran.Rollback()
                    Else
                        oleTran.Commit()
                    End If
                End If
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try

I think i have gambled up with this:

 If transfer successful
        Commit transaction
    Else
        Rollback transaction

What to do for this?

[jmcilhinney]

Again I will say that you should ALWAYS use meaningful variable names. Ask yourself, do "cmd", "cmd1" and "cmd2" really mean anything? Your code should look more like this:

Using connection As New OleDbConnection("connection string here")
    Try
        connection.Open()
 
        Dim currentBalance As Decimal
 
        Using currentBalanceQuery As New OleDbCommand("SELECT ...", connection)
            currentBalance = CDec(currentBalanceQuery.ExecuteScalar())
        End Using
 
        If currentBalance < transferAmount Then
            'Notify user of failure.
        Else
            Dim transaction As OleDbTransaction = connection.BeginTransaction()
 
            Using withdrawalCommand As New OleDbCommand("UPDATE ...", connection, transaction)
                Using depositCommand As New OleDbCommand("UPDATE ...", connection, transaction)
                    Try
                        withdrawalCommand.ExecuteNonQuery()
                        depositCommand.ExecuteNonQuery()
                        transaction.Commit()
                    Catch ex As Exception
                        'Log error
                        transaction.Rollback()
                    End Try
                End Using
            End Using
        End If
    Catch ex As Exception
        'Log error
    End Try
End Using

All the disposable objects are created with Using blocks and, therefore, are all disposed implicitly. This also closes the connection implicitly. An exception handler is also used to decide whether to rollback the transaction as a failure on the database is the only reason a rollback would be required.

Note that that is not all the code. It's just a template. You need to fill in the rest, e.g. adding parameters to the commands.

[gautamshaw]

I did this:

Private Sub Transfer_Btn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Transfer_Btn.Click
        Dim oleTran As OleDb.OleDbTransaction
        Try
            Using connection As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=BankAccount.mdb") 'Creating a Connection
                connection.Open() 'Open connection
                Dim currentBalanceQuery As New OleDbCommand
                currentBalanceQuery = New OleDbCommand("Select CurrentBalance from BankAccount where BankName=@bankname and AccountNo=@accountno", connection) 'Creating a query
                currentBalanceQuery.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                currentBalanceQuery.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                Dim CurrentBal As Decimal = CDec(currentBalanceQuery.ExecuteScalar()) 'Executing the query
                Dim Amount As Decimal
                Decimal.TryParse(TextBox3.Text, Amount)
                If CurrentBal < Amount Then 'Denotes whether transfer can coninue or not
                    MsgBox("Failure")
                Else
                    oleTran = con.BeginTransaction 'Creating transaction
                    Using withdrawalCommand As New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance-@amount where BankName=@bankname and  AccountNo=@accountno", connection) 'Creating a query
                        withdrawalCommand.Parameters.AddWithValue("@amount", TextBox3.Text)
                        withdrawalCommand.Parameters.AddWithValue("@bankname", ComboBox1.Text)
                        withdrawalCommand.Parameters.AddWithValue("@accountno", ComboBox2.Text)
                        Using depositCommand As New OleDbCommand("Update BankAccount set CurrentBalance= CurrentBalance+@amount where bankname=@BankName and  AccountNo=@accountno", connection) 'Creating a query
                            depositCommand.Parameters.AddWithValue("@amount", TextBox3.Text)
                            depositCommand.Parameters.AddWithValue("@bankname", ComboBox3.Text)
                            depositCommand.Parameters.AddWithValue("@accountno", ComboBox4.Text)
                            Try
                                Dim rowseffected As Integer = withdrawalCommand.ExecuteNonQuery() 'Executing cmd
                                Dim rowseffected1 As Integer = depositCommand.ExecuteNonQuery() 'Executing cmd1
                                oleTran.Commit()
                            Catch ex As Exception
                                oleTran.Rollback()
                            End Try
                        End Using
                    End Using
                End If
            End Using
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
End Sub

in this line:

Dim CurrentBal As Decimal = CDec(currentBalanceQuery.ExecuteScalar()) 'Executing the query

I am getting this error:

ExecuteScalar requires an open and available Connection. The connection's current state is closed.

How to deal with this?

[jmcilhinney]

You need to take a good look at your code. The answer is very simple if you read your code. Don't just look at it. Read it.

Also, you should take a closer look at my code because there's another critical difference between mine and yours, i.e. you are creating a transaction and never using it.

[gautamshaw]

ok let me take another look

[gautamshaw]

Ok jm i rectified it........i was not using the oleTran in the Update Statement...I m such a stupid

Now i want to understand this code step by step.......
Please help me with the use of these lines jm:

1>oleTran = connection.BeginTransaction()
2> oleTran.Commit()
3>oleTran.Rollback()

Need to clear my concept regarding the above code......

[gautamshaw]

in tha above code i am puttin ght commit() in a try block and performing the executenonquery.
what are the possible errors that may occur while performing this so that it enters the catch block and perform a rollback?
i.e,i am talking about the last try catch block:

Try
                                Dim rowseffected As Integer = withdrawalCommand.ExecuteNonQuery() 'Executing cmd
                                Dim rowseffected1 As Integer = depositCommand.ExecuteNonQuery() 'Executing cmd1
                                oleTran.Commit()
                            Catch ex As Exception
                                oleTran.Rollback()
                            End Try

[jmcilhinney]

Look at what methods you're calling inside the Try block and then read the documentation for those methods to see what exceptions they can throw.