Rolly G. Bueno Jr.

Visual Basic 2012 also known as VB 11 or Visual Basic .NET is the latest stable release from Microsoft, which implemented on .NET Framework. In this blog entry, we’re going to teach you on how to create login form in VB.Net using Microsoft Access 2007. The reason I choose Access over SQL Server is that I’m used to this database software since my college days but don’t worry, I will post another one using SQL Server when I have time.

The first step is we’re going to create a database for storing user’s information. Open Microsoft Access and create blank database. Make sure you know where you save the file as this is important on OleDB Connection String later. Create table and name it “tbl_user”. Depending on how you design the table, the 3 most important columns are ID, username and password. I have different approach on designing my user table and you may follow it but not recommended. Add these columns: “first_name”, “last_name”, “middle_name”, “age”, “address”, “user_level”, “username”, “password”

You must populate after creating the table as many as you can for our testing later. Nest step, open Visual Studio and create New Project for Visual Basic under Templates and select Windows Form Application. Name the application on whatever you want but I recommend letting the software do it.

After you create new project, Visual Studio will create a new form ready to use. We’re going to design this form and name it frmLogin.

Design the form same as below. Please take note of these important steps:

1.) Rename the username textbox to txtUsername and password textbox to txtPassword

2.) Rename the login button to btnLogin and cancel button to btnCancel

Double click on Login button. This will show you the coding window and your mouse focus is in inside btnLogin Click declaration as shown below.

Next step, we need to make sure that user provide both username and password so we are going to use If Else condition statement and OR comparison operator. Add this line inside btnLogin Private Sub.

' Check if username or password is empty
If txtPassword.Text = "" Or txtUsername.Text = "" Then
            MessageBox.Show("Please complete the required fields..", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
End If

We are using OR operator as it is must satisfy either one of the condition to be TRUE. So if the user enters either only username or password, error will be shown. The first statement is only a reminder of what this particular line of code is all about. Visual Basic compiler will not include this line when executing.

Notice that MessageBox.Show() has 4 arguments? It’s not required for those four completely but only for the first argument. The 1^st argument is important as this will be shown to end user of whatever you have written on it. In this case, we’re going to use “Please complete the required fields..” to indicate the user that those fields are important. 2^nd argument(  “Authentication Error” )is the MessageBox caption at the left side of those Minimize, Maximize and Close button at the very top. 3^rd argument is the button and last one is the icon( Red X ). See the figure below.

Next step we will do the connecting to database and execute data comparison inside Else condition. Add this line after the Else statement.

' Connect to DB
            Dim conn As New System.Data.OleDb.OleDbConnection()
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LibraryManagementSystem.accdb"

Please take note that the ConnectionString may vary from machine to machine. In my case, Jet Pack provider is not compatible in 64 bit machine and I use ACE OLEDB instead. To get your connection string, Follow the steps below:

1.)    Go back to design environment window and click on DataBindings in Properties window. Click F4 in you can’t see the Properties window or add it on VIEW->Properties Window.

2.)    Under DataBindings, click on Text->Add Project Data Source. This will give you Data Source Configuration wizard.

3.)    Choose Database as your Data Source Type and click Next.

4.)    Choose Dataset as your Database Model and then click Next.

5.)    Click on New Connection and you will see Add Connection window.

6.)    On the Data source, click Change and choose Microsoft Access Database File.

7.)    Now we’re going to locate the database that we created a while ago. Click on Browse and locate the database. If you create a secured database, enter your Username and Password to make a successful connection. Since this is just a tutorial, I didn’t show you on how to secure a database. However, if you’re going to develop software for distribution, you must secure your database.

8.)    Now click on Test Connection. If you have succeeded, then we’re good to go to next step.

9.)    Click OK to close the Add Connection wizard and expand the “Connection string that you will save in the application”. Copy the connection string given and paste it on conn.ConnectionString

Next step we are going to use Try and Catch the Exception. Go back to your btnLogin Private Sub and add the code below.

Try  
Catch ex As Exception
 MessageBox.Show("Failed to connect to Database.. System Error Message:  " & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

The MessageBox instances are the same as the previous one except we concatenate( ampersand & ) the exception message to let us know the exact error.

Add this code inside Try.

Dim sql As String = "SELECT * FROM tbl_user WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "'"
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

'Open Database Connection
sqlCom.Connection = conn
conn.Open()
Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

The sql string holds the username and password comparison in SQL Statement format. Astrerisk( * ) means that we are going to fetch every column on tbl_user we’ve created earlier on matched record in WHERE condition output.  In checking the required fields earlier, we use OR comparison operator in which only one conditions must be TRUE in order to satisfy. In here, we’re going to use AND as it will proceed only if both conditions are TRUE. Meaning username and password must be matched or it will fail. The sql will be executed and store in sqlRead variable.

Next step, were going to check if user enter correct username and password in order to proceed. Create another form and name it as frmMain. When user has been authenticated, this frmMain will show and we’re going to hide the frmLogin. Otherwise, we’re going to display error message, clear username and password text box and focus the set the input focus to username text field. Add the code below.

If sqlRead.Read() Then
    frmMainForm.Show()
    Me.Hide()
Else
    ' If user enter wrong username and password combination
    ' Throw an error message
    MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

    'Clear all fields
    txtPassword.Text = ""
    txtUsername.Text = ""

    'Focus on Username field
    txtUsername.Focus()
End If

Our full code for btnLogin Private Sub will be like this:

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        ' Check if username or password is empty
        If txtPassword.Text = "" Or txtUsername.Text = "" Then
            MessageBox.Show("Please complete the required fields..", "Authentication Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            ' Both fields was supply
            ' Check if user exist in database
            ' Connect to DB
            Dim conn As New System.Data.OleDb.OleDbConnection()
            conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\LibraryManagementSystem.accdb"

            Try
                'conn.Open()
                'MsgBox("Susscess")

                Dim sql As String = "SELECT * FROM tbl_user WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "'"
                Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)

                'Open Database Connection
                sqlCom.Connection = conn
                conn.Open()

                Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()

                If sqlRead.Read() Then
                    frmMainForm.Show()
                    Me.Hide()

                Else
                    ' If user enter wrong username and password combination
                    ' Throw an error message
                    MessageBox.Show("Username and Password do not match..", "Authentication Failure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

                    'Clear all fields
                    txtPassword.Text = ""
                    txtUsername.Text = ""

                    'Focus on Username field
                    txtUsername.Focus()
                End If

            Catch ex As Exception
                MessageBox.Show("Failed to connect to Database..", "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

        End If
    End Sub

Next step we are going to code the btnCancel. It depends on how you want to do with the Cancel button. Mostly, other people simply exiting the program when clicking on Cancel button but in my case, I’m going to clear username and password field text and focus the key to username. So double click on Cancel button and add the code inside the btnCancel Private Sub. Note the below code is the complete Private Sub.

Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
    'User clicking on cancel button only clears field
    ' and refocus to first field
    txtUsername.Text = ""
    txtPassword.Text = ""
    txtUsername.Focus()
End Sub

So how the login form looks like when running? Check the screenshots below.

Hopefully you enjoy my little tutorial. Next, I’m going to use SQL Server instead of Access and will share it in the future.