Vb.net - Create a login form using a datareader

[dday9]

Prerequisits:

1. 2 Labels
2. 2 Textbox's
3. 2 Buttons
4. MS Access or SQL database

The 2 TextBox's are named:

1. txt_Username
2. txt_Password

The 2 Button's are named:

1. btn_OK
2. btn_Cancel

The Database's datatable should be named "login" and have 2 datafields. Both fields should be string's. They are named:

• username
• password

Oledb:

Option Strict On
Option Explicit On

Imports System.Data.OleDb
Public Class Form1
    Private Sub btn_OK_Click(sender As System.Object, e As System.EventArgs) Handles btn_OK.Click
        'Declare the oledbconnection adn oledbcommand
        Dim con As New OleDbConnection("MyConnectionString")
        Dim cmd As New OleDbCommand("SELECT username,password FROM login WHERE username = ? AND password = ?", con)

        'Set up parameters for the oledbcommand
        cmd.Parameters.AddWithValue("username", txt_Username.Text)
        cmd.Parameters.AddWithValue("password", txt_Password.Text)

        'If the username or password is empty then throw an error
        If txt_Username.Text = String.Empty Then
            MessageBox.Show("Please enter the username.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        ElseIf txt_Password.Text = String.Empty Then
            MessageBox.Show("Please enter the password.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
                'Open the connection and declare an oledbreader
                con.Open()
                Dim reader As OleDbDataReader = cmd.ExecuteReader

                'If our reader has one or more rows then read those rows and compare the text
                If reader.HasRows = True Then
                    reader.Read()
                    'If the username and password match then it's a success
                    If txt_Username.Text = reader.Item("username").ToString And txt_Password.Text = reader.Item("password").ToString Then
                        MessageBox.Show("Login successful")
                    Else
                        'If they don't match than throw an error
                        MessageBox.Show("Login Failed." & Environment.NewLine & _
                                        "Username and password are casesensitive.", _
                                        Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                con.Close()
            End Try
        End If
    End Sub

    Private Sub btn_Cancel_Click(sender As System.Object, e As System.EventArgs) Handles btn_Cancel.Click
        Me.Close()
    End Sub
End Class

SQL:

Option Strict On
Option Explicit On

Imports System.Data.SqlClient
Public Class Form1
    Private Sub btn_OK_Click(sender As System.Object, e As System.EventArgs) Handles btn_OK.Click
        'Declare the sqlconnection adn sqlcommand
        Dim con As New SqlConnection("MyConnectionString")
        Dim cmd As New SqlCommand("SELECT username,password FROM login WHERE username = ? AND password = ?", con)

        'Set up parameters for the sqlcommand
        cmd.Parameters.AddWithValue("@username", txt_Username.Text)
        cmd.Parameters.AddWithValue("@password", txt_Password.Text)

        'If the username or password is empty then throw an error
        If txt_Username.Text = String.Empty Then
            MessageBox.Show("Please enter the username.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        ElseIf txt_Password.Text = String.Empty Then
            MessageBox.Show("Please enter the password.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            Try
                'Open the connection and declare an sqlreader
                con.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader

                'If our reader has one or more rows then read those rows and compare the text
                If reader.HasRows = True Then
                    reader.Read()
                    'If the username and password match then it's a success
                    If txt_Username.Text = reader.Item("username").ToString And txt_Password.Text = reader.Item("password").ToString Then
                        MessageBox.Show("Login successful")
                    Else
                        'If they don't match than throw an error
                        MessageBox.Show("Login Failed." & Environment.NewLine & _
                                        "Username and password are casesensitive.", _
                                        Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If
            Catch ex As Exception
                MessageBox.Show(ex.ToString, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                con.Close()
            End Try
        End If
    End Sub

    Private Sub btn_Cancel_Click(sender As System.Object, e As System.EventArgs) Handles btn_Cancel.Click
        Me.Close()
    End Sub
End Class

[gep13]

Hello,

As per the CodeBank entry here:

http://www.vbforums.com/showthread.p...a-in-Databases

The only suggestion that I would make about the above code would be that you use a Using statement around the objects that implement IDisposable.

Gary

[EricBHK]

Hi !



I used the OLEDB sample code in a project and it is working fine as long as there is a succesful match for both username and password.

However, in case there is NO match (either username or password), I do NOT get any eror messages (not the error message after the else statement nor the exception error message). The login form just refuses to open the new form it is supposed to open in case of success. The application doesn't get stuck or anything : it just refuses access without further error message.
And just to be complete : there are of course no other issues whatsoever (e.g. build errors, ... etc.).

So basically, the login form is doing its job (keeping people out who don't have the proper username/password).

However, it would be appropriate to have the error message dispayed in case of wrong username/password.

Does anyone have any idea why none of these two error messages are displayed ? Actually it is nothing more than the most basic 'If .. else ... end if statement' ; so what could be going wrong ??



Thanks.

[dday9]

Hmm, upload the project and I'll take a look at it. Or you can post the entire thing if you want(so long as it isn't to long).

[EricBHK]

Dear dday9,

I have attached my code for the login form.

As you will notice, I have been experimenting with alternative solutions (commented out) in case there is no successful login (e.g. If not, if <>, ... etc.)
The weird thing is that if I run the code in debug mode with the alternatives, I do get both a succesful as well as an unseccessful message.
So it looks to me that reader is not recognizing any unsuccessful attemps and is getting confused.

Hope you can sort this out.


Thanks in advance !

[EricBHK]

Dear dday9,



I just came up with a possible solution (see second attachment called LogInSolution(2003) by adding an additional 'else' statement. However, I am still having doubts about this.

In this possible solution, I can get 'Login Failed2' to work in the way it should appear on screen for the end-user, but not 'Login Failed1' (as I think is the proper way do do things).

Or am I missing something here ?


LogInSolution(2003).doc

[dday9]

Ah, nope that was an error on my end. The:

If reader.HasRows = True Then
                    reader.Read()
                    'If the username and password match then it's a success
                    If txt_Username.Text = reader.Item("username").ToString And txt_Password.Text = reader.Item("password").ToString Then
                        MessageBox.Show("Login successful")
                    Else
                        'If they don't match than throw an error
                        MessageBox.Show("Login Failed." & Environment.NewLine & _
                                        "Username and password are casesensitive.", _
                                        Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If

should be:

If reader.HasRows = True Then
                    reader.Read()
                    'If the username and password match then it's a success
                    If txt_Username.Text = reader.Item("username").ToString And txt_Password.Text = reader.Item("password").ToString Then
                        MessageBox.Show("Login successful")
                    End If
         Else
                    'If they don't match than throw an error
                     MessageBox.Show("Login Failed." & Environment.NewLine & _
                                    "Username and password are casesensitive.", _
                                    Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                    End If
                End If