Update database(Microsoft Access) using VB.net

Question

I am creating an ATM application for a project. I am using VB.net to update the balance on MS access to a new balance to reflect the actions of the withdraw function on the application. Currently the new balance appears in the 'txtNewBalance' however it doesn't update on the database itself and in-turn can't be used on a separate deposit page as the original balance appears and not the updated balance after withdrawal of some of the balance. The following is my code:

Public Class frmWithdraw

    Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=ATM2.accdb")

    Dim objAccountDA As New OleDb.OleDbDataAdapter("Select * from tblAccount", objConnection)
    Dim objAccountCB As New OleDb.OleDbCommandBuilder(objAccountDA)
    Dim AccountDataSet As New DataSet()

    Public Sub StoreDetails()
        Dim objRow As DataRow

        objRow = AccountDataSet.Tables("tblAccount").Rows.Find("AccounNum")

        objRow.Item("AccountNum") = txtAccountNum.Text
        objRow.Item("AccountBalance") = txtBalance.Text

    End Sub

    Public Sub Retrieve()

        objAccountDA.FillSchema(AccountDataSet, SchemaType.Source, "tblAccount")
        objAccountDA.Fill(AccountDataSet, "tblAccount")

        txtAccountNum.Text = frmLogin.EmployeeNO

        FillAccountDetails()
        'FillUserDetails()
    End Sub

    Private Sub frmWithdraw_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'ATM2DataSet.tblAccount' table. You can move, or remove it, as needed.
        ' Me.TblAccountTableAdapter.Fill(Me.ATM2DataSet.tblAccount)
        Retrieve()
    End Sub

    Public Sub FillAccountDetails()
        Dim objRow As DataRow
        objRow = AccountDataSet.Tables("tblAccount").Rows.Find(txtAccountNum.Text.ToString)
        txtBalance.Text = objRow.Item("Balance").ToString

    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        Me.Hide()
        frmMain.Show()
    End Sub

    Private Sub btnWithdraw_Click(sender As Object, e As EventArgs) Handles btnWithdraw.Click

        If txtOutput.Text = "" Or txtOutput.Text = "0" Or txtOutput.Text >= 300 Then
            MessageBox.Show("Invalid amount")
            txtOutput.Clear()

        End If

        txtNewBal.Text = Val(txtBalance.Text) - Val(txtOutput.Text)
        objAccountDA.Update(AccountDataSet, "tblAccount")
        AccountDataSet.AcceptChanges()
        Dim objCurrentRow As DataRow
        objCurrentRow = AccountDataSet.Tables("tblAccount").Rows.Find(txtAccountNum.Text.ToString)
        objCurrentRow("Balance") = Val(txtNewBal.Text.ToString)

        Retrieve()
        MessageBox.Show("Withdrawal Confirmed")

        Me.Hide()
        frmMain.Show()

    End Sub

    Private Sub TblAccountBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs)
        Me.Validate()
        Me.TblAccountBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.ATM2DataSet)

    End Sub

    Private Sub btn20_Click(sender As Object, e As EventArgs) Handles btn20.Click
        txtOutput.Text = 20 * numAmount.Text
    End Sub

    Private Sub btn40_Click(sender As Object, e As EventArgs) Handles btn40.Click
        txtOutput.Text = 40 * numAmount.Text
    End Sub

    Private Sub btn60_Click(sender As Object, e As EventArgs) Handles btn60.Click
        txtOutput.Text = 60 * numAmount.Text
    End Sub

    Private Sub btn80_Click(sender As Object, e As EventArgs) Handles btn80.Click
        txtOutput.Text = 80 * numAmount.Text
    End Sub

    Private Sub btn100_Click(sender As Object, e As EventArgs) Handles btn100.Click
        txtOutput.Text = 100 * numAmount.Text
    End Sub

    Private Sub HelpToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles HelpToolStripMenuItem.Click
        Me.Hide()
        frmHelp.Show()
    End Sub

End Class

You code is a bit confused, and seems to be mixing weakly and strongly typed datatable methodologies. I can't see anywhere that you actually decrement the datatable amount, but I also suspect that the dataset being sent for update and the dataset showing things on the form, is a different dataset.. — Caius Jard
Normally, aggregate data should not be saved but calculated when needed. There are ways to show opening balance on report without saving and updating field. — June7
Cannot stress @preciousbetine comment enough; MS should ship VB.NET with Strict on. They should also ditch the DLLs intended to make VB6 code work with copy and paste, like Val, CInt, MsgBox etc - these things all have new proper names now — Caius Jard

Anonymous Anonymous · Accepted Answer · 2020-04-04T17:37:10

Currently the new balance appears in the 'txtNewBalance' however it doesn't update on the database itself and in-turn can't be used on a separate deposit page as the original balance appears and not the updated balance after withdrawal of some of the balance.

I don't see txtNewBalance in your code, so I am not sure how you retrieve and maintain the value of this control. I suppose it is another form, that may be non-modal and possibly out of sync with the activity carried out in the other forms.

The code does not look very safe overall and I see a lot of opportunity for things to go wrong.

I am worried with this bit of code:

If txtOutput.Text = "" Or txtOutput.Text = "0" Or txtOutput.Text >= 300 Then
    MessageBox.Show("Invalid amount")
    txtOutput.Clear()

End If

Here you check txtOutput, but even if the value entered is incorrect the rest of the code will execute nonetheless. MessageBox suspends the execution but does not end it. If you want to stop execution, you should add Exit Sub right after txtOutput.Clear(). I think this is what you intended. What happens here, is that you proceed anyway and update AccountDataSet no matter what, and you change txtNewBal too. I am not sure about the final result, but it's extremely likely to be wrong. That could be your problem, and that may not be the only one.

In fact, this check is incorrect because you are only testing for 3 specific conditions, but there are many more that could occur: just think whitespace or anything else.

To further elaborate on the possible security issues that may occur consider this code in the Retrieve function:

txtAccountNum.Text = frmLogin.EmployeeNO

A better way would be to define EmployeeNO as a global variable right after login and pass that variable as a parameter to your function, rather than repeatedly checking the other form to fetch this value.

Imagine that after login, a malicious person goes back to the login form and changes the value of EmployeeNO (presumably a textbox). Your Retrieve function will then use a new value, that could be that of another employee.

'Normally' that should not happen. But you use the Show function for your forms and not ShowDialog, that means your forms are not modal. If there is flaw in your code and the Hide does fail for some reason, you could find yourself in a situation where more than one form is visible at the same time and it is possible to switch from one form to another to trick your application into doing bad things.

Other remarks:

In all cases I would strongly advise to log all actions to a file. It is important to keep an activity trail, especially when there is money or real goods involved.

Would you do business with a bank that is unable to explain where and when you withdrew money from your account ?

Any changes should be committed to database immediately. Don't keep changes in a dataset for any longer than strictly necessary. Storing data in memory is just too dangerous. If there is a power cut you could lose data and your accounts may be in an inconsistent state.

If you have to update more than than one record, it will be important to use transactions to preserve the integrity of your data. Imagine that you credit one account but your code crashes before you can deduct the corresponding amount from the source account. You are left with a big discrepancy in your accounts. What is even worse is the inability to explain where the discrepancy comes from.

Regarding validation of user input in your code: are you really using regular textboxes ? A better alternative would be the NumericUpDown control. It behaves like a textbox but will limit input to numbers, in a range that you can define in advance. That also improves your UI a little bit.

Are you even handling exceptions in your code ?

To sum up here are some of things you might want to do:

improve validation techniques
choose the best UI controls for the job
log everything
have strong exception handling - if in doubt stop your application rather than risk doing something wrong
maybe read a few tutorials, because many people have done similar stuff and there is no need to reinvent the wheel every time. On Stack Overflow alone there is plenty of vetted code, not to mention repositories like Github where you can see how applications are built. You can take inspiration from the things that exist. I myself have learned a lot simply by watching the more experienced developers. It is important to learn best practices early and not develop bad habits that will always bite you later.

An ATM application sounds like serious stuff, especially if you are going to service a number of people and you are not the sole user. You don't want to make them angry. You certainly cannot afford to be sloppy. So take your time and document yourself. Good luck.

Update database(Microsoft Access) using VB.net

2 Answers