Multiple If condition using Microsoft Access database in VB Net

Question

I am trying to make a banking system where the customer will only be able to deposit money IF ONLY they enter the CORRECT AccountNo & Name using Microsoft Access Databse in VB NET.

Here are my codes:

Dim conn As New OleDbConnection

Dim conn = "provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Database\Bank-Application-Database.accdb;"

    conn.ConnectionString = "provider = Microsoft.ACE.OLEDB.12.0; Data Source = C:\Database\Bank-Application-Database.accdb;"

    cmdupdate.Connection = cnnOLEDB

    Dim deposit As OleDbCommand = New OleDbCommand("SELECT * FROM [Current_Account] WHERE [AccountNo] and [CustomerName] = '" & accnotxt.Text & "' AND [CustomerName] = '" & nametxt.Text & "' AND [Amount] = '" & amounttxt.Text & "'", myConnection)
    deposit.Connection = conn
    conn.Open()


    If accnotxt.ToString = True And nametxt.ToString = True Then

        cmdupdate.CommandText = "UPDATE [Current_Account] SET [Amount] = '" & amounttxt.Text.ToString & " WHERE [AccountNo] AND [CustomerName] = " & accnotxt.Text.ToString & " " & nametxt.ToString & ";"
        cmdupdate.CommandType = CommandType.Text
        cmdupdate.ExecuteNonQuery()
        MsgBox(" Successfully deposited.")
    Else
        MsgBox("Incorrect Account Number or Name.")

I am able to run.However when I click to Deposit, this is the error.

The error I got:

An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Additional information: Conversion from string "System.Windows.Forms.TextBox, Te" to type 'Boolean' is not valid.

Any helpers ? Thanks in advance.

Answer 1

In addition to @Jonathon Ogden's answer:

You missed your single quotes in this query:

cmdupdate.CommandText = "UPDATE [Current_Account] SET [Amount] = '" & amounttxt.Text.ToString & " WHERE [AccountNo] AND [CustomerName] = " & accnotxt.Text.ToString & " " & nametxt.ToString & ";"

Change it to this:

cmdupdate.CommandText = "UPDATE [Current_Account] SET [Amount] = " & Val(amounttxt.Text) & " WHERE [AccountNo]= '" & accnotxt.Text & "' AND [CustomerName] = '" & nametxt.Text & "';"

I also removed there the single quote from amounttxt.Text.ToString assuming it's a value, changed it to Val(amounttxt.Text) so that even that textbox is blank, it won't cause an error.

Also corrected your WHERE Condition:

Another is you don't need .ToString anymore since .Text property is already a String.

Answer 2

Your ToString usage will convert the value of the accnotxt and nametxt text boxes into a text string and you're trying to compare that to True (i.e. accnotxt.ToString = True) which is of a Boolean data type not a String data type i.e. you are trying to compare text to a boolean (true or false) value.

A very basic way (that can be improved on) of doing the account number and name check would be to change your SELECT statement to:

Dim deposit As OleDbCommand = New OleDbCommand("SELECT * FROM [Current_Account] WHERE [AccountNo] and [CustomerName] = '" & accnotxt.Text & "' AND [CustomerName] = '" & nametxt.Text & "'", myConnection)

I removed the Amount filter from the WHERE clause as I am not sure why you are doing that. You don't even use this deposit database command. You then execute the deposit SQL command and get its result: Dim result = deposit.ExecuteScalar(). ExecuteScalar does the following:

Executes the query, and returns the first column of the first row in the result set returned by the query.

You then change your account number and name check to If result IsNot Nothing Then instead of checking if the values of the textboxes are a particular account number and name.

In other words, if your query returns something and not nothing, then you can assume a current account exists with the accnotxt and nametxt.

Also, pay attention to @CrushSundae answer as they have highlighted other issues too.