user login to database for atm?

[rusty]

I'm writing an atm program. I've set my database up and I'm ready to check the login screen with the actual user's account number and pin number in the database. I'm curious how to do this, as in the proper way to do this. I'm guessing that I'm going to use an If statement to get a match for the user number of the login screen that matches the account number in the database.

If txtCustNumber = AccountID And txtCustPin = AccountPinID Then
give the user access to the atm screens
Else
message("Invalid Account or Pin!)
End If

Is this the correct way to do this? Do I need to populate a DataTable in order to check the user account number to the database account number? Is there a way to just reference the account number and pin and check the login screen? I'm planning on making a transaction report in a table to keep a record of basic atm procedures. This is all just for fun. I haven't written a program in the last 12 years, so it's all coming back but there's new things to learn. Thanks...

 

[Satal Keto]

Hello please remember to use code BB tags when posting code, no matter how small the code is, it just makes it look better and keeps all the formatting.

Right down to your question personally I would do something like

    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Dim conn As New SqlConnection("Your connection string")
        Dim sql As String = "SELECT * FROM tblAccounts WHERE AccountID=@AccountID AND PIN=@Pin"
        Dim cmd As New SqlCommand(sql, conn)
        Dim rdr As SqlDataReader

        cmd.Parameters.Add("@AccountID", SqlDbType.Int, 4).Value = txtCustNumber.Text
        cmd.Parameters.Add("@Pin", SqlDbType.Int, 4).Value = txtCustPin.Text

        conn.Open()
        rdr = cmd.ExecuteReader
        If rdr.HasRows Then
            'The user has a valid AccountID/PIN combination
        Else
            'The user doesn't have a valid AccountID/PIN combination
        End If
        conn.Close()
    End Sub

You will obviously need to provide the connection string for your database on the first line. On line 2 you will have to adjust the SQL to match your database design.
You would also want to extend this code with some validation on the text fields (to check that they are populated and valid numbers for example). You will also need to import System.Data.SqlClient by putting

Imports System.Data.SqlClient

at the top of your code page.

Anyway hopefully from that you should have a pretty good idea how to go about doing what you've asked.

Hope it helps

Satal

 

[rusty]

I'm using a database from Microsoft Office. I haven't used sql and I'm going to read a book on that next. However, I've run into a small snag. I am using Option Strict and I ran into a problem converting an object into a string. I'm trying to check the user's textbox to the account number in my database. I'm checking through the rows one by one and I'm not sure if this is the best way to do this. Do I have to cast the database row into a string, or integer? This is the code I'm getting the "Object to String" comments on.

Dim strAccountId As String
Dim strPin As String
Dim m_rwBankdb As DataRow = m_dtBankdb.Rows(0)

If m_rowPosition < (m_dtBankdb.Rows.Count - 1) Then
       'The next two lines are the "Object to String" conversions...
       strAccountId = m_dtBankdb.Rows(m_rowPosition)("AccountId")
       strPin = m_dtBankdb.Rows(m_rowPosition)("Pin")
       If txtCustCardNumber.Text = strAccountId And txtCustCardPin.Text = strPin Then
          pnlLogin.Hide()
          pnlMainMenu.Show()
       Else
           MsgBox("Invalid Account or Pin")
       End If
   m_rowPosition = m_rowPosition + 1   
End If

 

[Satal Keto]

Try changing

m_dtBankdb.Rows(m_rowPosition)("AccountId")

to

m_dtBankdb.Rows(m_rowPosition)("AccountId").ToString

And do the same for the Pin line.

Hopefully that should help

 

[cjard]

Satal

Why don't you just do:

SELECT COUNT(*) FROM tblAccounts WHERE AccountID=@AccountID AND PIN=@Pin

and then ExecuteScalar() it?

 

[Satal Keto]

Damn, I had thought that and then came up the reason for not choosing that method and kept telling myself "you must explain why you chose this method" and apparently still forgot

The reason that I chose to actually select the data was that I would assume you would not only want to check that its a valid account, but if it was a valid account you would want to have some information about the account, which would be accessible by using the following code;

If rdr.HasRows Then
    'The user has a valid AccountID/PIN combination
    rdr.read
    msgbox(rdr("AccountID"))
    msgbox(rdr("Pin"))
Else
    'The user doesn't have a valid AccountID/PIN combination
End If

The rdr("AccountID") will return the data that is in the column named AccountID.

The code above assumes that only one record has been returned (which if AccountID is a unique identifier then it should be true). Although it would have been better for the SQL to specify to only retrieve one record maximum, by changing the SQL to;

SELECT TOP 1 * FROM tblAccounts WHERE AccountID=@AccountID AND PIN=@Pin

Sorry for forgetting to complete my explanation and thank you to cjard for bringing it to my attention.

Satal