Retrieving a PIN code from a database?

[gareth88]

I am coding a banking and ATM application, which is split into two parts. In one part the employees of the bank can open new accounts for customers, edit accounts, etc. It includes assigning a unique PIN code for the customer. This is all saving to a database. The customer then uses the ATM part of the application to withdraw money. They use a unique PIN code to access their account. The problem that I'm having is retrieving the PIN from the database. I want to write a simple If statement, to match the PIN entered with the corresponding PIN in the database, so that it will retrieve all the relevant customer's details, eg. bank balance. The PIN is successfully saving to the database through the following....

If inc <> -1 Then

Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("tblCustomer").NewRow()

dsNewRow.Item("PINCode") = mskPINCode.Text

ds.Tables("tblCustomer").Rows.Add(dsNewRow)

da.Update(ds, "tblCustomer")

End If


So how do I retrieve the PIN from the database, that matches the PIN entered and hence, retrieves bank balance, etc?

 

[MattP]

Take a look at this series on working with data: Visual Basic Forms over Data Video Series.

You'll want to watch at least the first 4 videos but the whole series will be worthwhile in the long run.

 

[gareth88]

Retrieving the correct PIN code from a database.

I am having difficulty retrieving the correct PIN code from my database, for an ATM banking application. When someone enters a correct PIN code that is already in the database, they gain access to their ATM account. If incorrect, then a message box appears. I am having an unusual problem where, the system is partially working. The code I have, is preventing the user from accessing their account, in the correct manner, eg. message box pops up. However, it is also not accepting the correct PIN code. The most unusual thing about this, is that, if the user enters the number "1", the message box won't pop up, however it will still block the user from accessing their account. And even stranger again, after entering the number "1", trying it, then erasing it, if they enter the correct PIN after this, it will allow them to access their account. I am baffled as to why this would happen and would greatly appreciate any help with this.

Here is the code:

Public Class ATM2

Dim Pin As Integer

Public Sub Retrieve()

        objDataSet.Clear()

        objCustomerDA.FillSchema(objDataSet, SchemaType.Source, "tblCustomer")

        objCustomerDA.Fill(objDataSet, "tblCustomer")


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

End Sub


Private Sub ATM2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =E:\VB~\VB Group Project\BankingAp.mdb"
        con.Open()

        sql = "SELECT * FROM tblCustomer;"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "tblCustomer")
        con.Close()

        maxRows = ds.Tables("tblCustomer").Rows.Count
        inc = -1

    End Sub

This is the main bit of code in question...

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click

        Retrieve()

        Dim objRow As DataRow



        Try

            objRow = objDataSet.Tables("tblCustomer").Rows.Find(txtPin.Text)
            Pin = Val(objRow.Item("Pin"))

        Catch

            If Len(txtPin.Text) = 4 And txtPin.Text = Pin Then

                Me.Hide()
                ATM3.Show()

            Else
                MsgBox("You have entered the wrong PIN Code")
                txtPin.Text = ""

            End If

        End Try
    End Sub

 

[picoflop]

I am having difficulty retrieving the correct PIN code from my database

Hopefully!
The PIN should - of course - NEVER be stored as plaintext. Store a salted (new salt for each pin of course!) hash instead. Even if this is not a real world app, security should be the main focus in any case. Needless to say, that the bank emplyoees do not choose a pin, but the pin is generated by the system and securely given to the cust only.

 

[Tom]

Private Sub btnOK_Click() Handles btnOK.Click

        Retrieve()
        Dim objRow As DataRow


        Try

            objRow = objDataSet.Tables("tblCustomer").Rows.Find(txtPin.Text)
            Pin = Val(objRow.Item("Pin"))

        Catch

 [COLOR="seagreen"][B]          [SIZE="3"]'This code is only executed after an error has occurred above[/SIZE][/B][/COLOR]
            If Len(txtPin.Text) = 4 And txtPin.Text = Pin Then

                Me.Hide()
                ATM3.Show()

            Else
                MsgBox("You have entered the wrong PIN Code")
                txtPin.Text = ""

            End If

        End Try
    End Sub

 

[gareth88]

Right, I've changed things around to include an account number as well as a PIN code. I'm still having problems with it though. The account munber is working correctly, but combined with the PIN, it does not go through. Any suggestions as to how to fix it?


Here's the code...

Public Class ATM2

    Dim Pin As Integer
    Dim Account As Integer

Public Sub Retrieve()

        objDataSet.Clear()

        objCustomerDA.FillSchema(objDataSet, SchemaType.Source, "tblCustomer")

        objCustomerDA.Fill(objDataSet, "tblCustomer")


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

End Sub

Private Sub ATM2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =E:\VB~\VB Group Project\BankingAp.mdb"
        con.Open()

        sql = "SELECT * FROM tblCustomer;"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "tblCustomer")
        con.Close()

        maxRows = ds.Tables("tblCustomer").Rows.Count
        inc = -1

        txtPin.Visible = False
    End Sub

Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click

        Retrieve()

        Dim objRow As DataRow


        txtAccountNo.Focus()

        Try

            objRow = objDataSet.Tables("tblAccount").Rows.Find(txtAccountNo.Text)
            Account = Val(objRow.Item("AccountNumber"))
            txtAccountNo.Visible = False
            txtPin.Visible = True
            txtPin.Focus()
            


        Catch

            If Val(txtAccountNo.Text) <> Account Then

                MsgBox("Please enter your correct account number")

            End If

        End Try



        Try

            objRow = objDataSet.Tables("tblCustomer").Rows.Find(txtPin.Text)
            Pin = Val(objRow.Item("Pin"))
            Me.Hide()
            ATM3.Show()

        Catch

            If Val(txtPin.Text) <> Pin Then

                MsgBox("Please enter your correct PIN code")

            End If


        End Try


    End Sub

 

[Tom]

Not sure how many times I can tell ya the same thing, perhaps you autta start by learning more about how Try/Catch blocks work and second how to step thru your own code.

Private Sub btnOK_Click() Handles btnOK.Click

        Retrieve()

        Dim objRow As DataRow


        txtAccountNo.Focus()

        Try

            objRow = objDataSet.Tables("tblAccount").Rows.Find(txtAccountNo.Text)
            Account = Val(objRow.Item("AccountNumber"))
            txtAccountNo.Visible = False
            txtPin.Visible = True
            txtPin.Focus()
            
        [COLOR="Red"][B]Catch[/B][/COLOR]

            [COLOR="SeaGreen"][B][SIZE="4"]'This code is only executed after an error has occurred above[/SIZE][/B][/COLOR]

            If Val(txtAccountNo.Text) <> Account Then

                MsgBox("Please enter your correct account number")

            End If

        End Try

    End Sub

 

[cjard]

Good grief, gareth - does it really seem like a sensible thing for you to do, to read the ENTIRE contents of a database table into the client computer then go find the record you want?

Imagine if HSBC downloaded all 500 million customer records into the atm when I typed my PIN in - be a bit slow eh?

I think you'd be best off scrapping what you've written (yes; i know you've put blood sweat and tears into it, but what I'm about to show you will get this problem solved in about 10 minutes)

Read the DW2 link in my signature, section Creating a Simple Data App
Then read Creating a Form To Search Data

Now the following will make sense:

In your relevant tableadapter for the customer table, Add a Query
Query That Returns A Single Value

SQLSERVER: SELECT COUNT(*) FROM customers WHERE accountNumber = @accountNumber AND pin = @pin
ACCESS: SELECT COUNT(*) FROM customers WHERE accountNumber = ? AND pin = ?
ORACLE: SELECT COUNT(*) FROM customers WHERE accountNumber = :accountNumber AND pin = in
​

Name: CountAccountNumberPins
Finish


Then in your form:

Sub Login_Click(...) Handled Login.Click
  If customerTableAdapter.CountAccountNumberPins(acNoTextBox.Text, pinTextBox.Text) > 0 Then
    MessageBox.Show("Youre logged in")
  Else
    MessageBox.Show("Try again")
  End If
End Sub

 

[Tom]

Just to add....

Sub Login_Click(...) Handled Login.Click

Try

       If customerTableAdapter.CountAccountNumberPins(acNoTextBox.Text, pinTextBox.Text) > 0 Then
            MessageBox.Show("Youre logged in")
      Else
            MessageBox.Show("Try again")
      End If

Catch

       MessageBox.Show("This statement only executes when an error occurs")
        
End Try
End Sub

 

[gareth88]

Lovely jubbly.