Need some help in OledbTransaction
I did this code:
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!!!
Re: Need some help in OledbTransaction
Re: Need some help in OledbTransaction
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:
Code:
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.
Re: Need some help in OledbTransaction
I did upto 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") '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 refers?
Is it the update query?
Then why should i
Code:
Create update commands
after the ?
I am having a mix up between the Create query and Create update commands
Re: Need some help in OledbTransaction
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.
Re: Need some help in OledbTransaction
I gave a try with 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") '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:
Code:
If transfer successful
Commit transaction
Else
Rollback transaction
What to do for this?
Re: Need some help in OledbTransaction
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:
vb.net Code:
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.
Re: Need some help in OledbTransaction
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 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:
Code:
Dim CurrentBal As Decimal = CDec(currentBalanceQuery.ExecuteScalar()) 'Executing the query
I am getting this error:
Code:
ExecuteScalar requires an open and available Connection. The connection's current state is closed.
How to deal with this?
Re: Need some help in OledbTransaction
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.
Re: Need some help in OledbTransaction
ok let me take another look
Re: Need some help in OledbTransaction
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:
Code:
1>oleTran = connection.BeginTransaction()
2> oleTran.Commit()
3>oleTran.Rollback()
Need to clear my concept regarding the above code......
Re: Need some help in OledbTransaction
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:
Code:
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
Re: Need some help in OledbTransaction
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.
