VB.NET Multi User Role Based Login System (MS Access)

VB.NET MS Access : How to Create a Multi-User Role-Based Login System (Full Tutorial)

สอนฟรี Visual Basic .NET กับฐานข้อมูล Microsoft Access สร้างระบบ login แบบแบ่งระดับผู้ใช้

VB.NET Source Code by iBasskung.

Module: ModCenter.vb

Option Explicit On

Option Strict On

Imports System.Data.OleDb

Module ModCenter

    Public con As New OleDbConnection

    Public da As OleDbDataAdapter

    Public cmd As OleDbCommand

    Public sql As String

    Public strLang As String = "VB.NET 2022"

    Public strAnd As String = Chr(38) ' &

    Public strSpace As String = Chr(32) ' Blank space

    Public dbName As String = "Access"

    Public mainProjectName As String = "Dormitory Management System."

    Public subProjectName As String = "Login System with User Authentication."

    Public appTitle1 As String = String.Format("{0} {1}", strLang, mainProjectName)

    Public appTitl1WithDb As String = String.Format("{0} {1} {2} {3}", strLang, strAnd, dbName, mainProjectName)

    Public appTitle2 As String = String.Format("{0} {1}", strLang, subProjectName)

    Public appTitle2WithDb As String = String.Format("{0} {1} {2} {3}", strLang, strAnd, dbName, subProjectName)

    Public conState As Boolean = False

    Public currentUserID As String = ""

    Public currentUsername As String = ""

    Public currentPermission As String = ""

    Public Sub ManageConnection()

        conState = False

        Try

            Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath

            conString &= "\VBDormSystemDatabase.accdb"

            If con.State = ConnectionState.Open Then

                con.Close()

            End If

            con.ConnectionString = conString

            con.Open()

            conState = True

            ' MessageBox.Show("Connected to Microsoft Access Database.", appTitle1,

            ' MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch ex As Exception

            conState = False

            MessageBox.Show("Error Database Connection: " & ex.Message, appTitl1WithDb,

                MessageBoxButtons.OK, MessageBoxIcon.Error)

            Return

        End Try

    End Sub

    Public Function DisplayData(str As String, tbl As String, myDS As DataSet) As DataSet

        If conState = False Then

            ManageConnection()

        End If

        Dim ds As New DataSet

        Try

            ds.Clear()

            da = New OleDbDataAdapter(str, con)

            da.Fill(myDS, tbl)

            ds = myDS

        Catch ex As Exception

            ds = Nothing

            conState = False

            MessageBox.Show("Error Displaying Data: " & ex.Message, appTitl1WithDb,

                            MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            conState = False

            con.Close()

        End Try

        DisplayData = ds

    End Function

    Public Function ExecuteDb(mySQL As String) As Boolean

        Dim bCheck As Boolean = False

        If conState = False Then

            ManageConnection()

        End If

        Try

            cmd = New OleDbCommand(mySQL, con)

            cmd.CommandTimeout = 600

            cmd.ExecuteNonQuery()

            bCheck = True

        Catch ex As Exception

            bCheck = False

            MessageBox.Show("Error: " & ex.Message, appTitl1WithDb, MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            conState = False

            con.Close()

        End Try

        ExecuteDb = bCheck

    End Function

End Module

Form: MainForm.vb

Public Class MainForm

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ManageConnection()

        Try

            Dim login As Form = New LoginForm()

            login.ShowDialog()

            ShowPermission()

        Catch ex As Exception

        End Try

    End Sub

    Private Sub ShowPermission()

        Dim PermissionDetail As String

        If currentPermission.ToUpper() = "USER" Then

            PermissionDetail = " (Read Only)."

        Else

            PermissionDetail = " (Full Control)."

        End If

        RoleLabel.Visible = True

        RoleLabel.Text = "Current Username: " & currentUsername & " : Log-in as: " & currentPermission.ToUpper() & PermissionDetail

        LoggedInUserToolStripButton.Text = currentUsername

        Me.Text = String.Format("{0} {1} {2} {3} : {4} ({5}) ", "Main Form :", strLang, strAnd, dbName, mainProjectName, subProjectName)

        ToolStripStatusLabel2.Text = "VB.NET 2022 and MS Access Database : Login System with User Authentication."

    End Sub

    Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click

        Application.Exit()

    End Sub

    Private Sub LogOutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles LogOutToolStripMenuItem.Click

        Dim strText As String = "Are you sure you want to log out?"

        Dim strCaption As String = "VB.NET : Log Out : iBasskung Tutorial."

        If MessageBox.Show(strText, strCaption, MessageBoxButtons.YesNo, MessageBoxIcon.Question,

                           MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.No Then

            Return ' Exit Sub

        End If

        currentUserID = ""

        currentUsername = ""

        currentPermission = ""

        Me.Text = String.Format("{0} {1} {2} {3} Database.", "Main Form :", strLang, strAnd, dbName)

        LoggedInUserToolStripButton.Text = "Logged-in User"

        RoleLabel.Text = "Logged-Out."

        ToolStripStatusLabel2.Text = "(Logged-Out)"

        MainForm_Load(sender, e)

    End Sub

    Private Sub UserManagementToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles UserManagementToolStripMenuItem.Click

        UserManagementToolStripButton.PerformClick()

    End Sub

    Private Sub YouTubeToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles YouTubeToolStripMenuItem.Click

        Process.Start("https://www.youtube.com/c/ibasskung")

    End Sub

    Private Sub AboutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AboutToolStripMenuItem.Click

        MessageBox.Show("Visual Basic .NET and MS Access Database." & vbNewLine &

                        "Login System with User Authentication Version 1.0.0" & vbNewLine &

                        "Developed by Mr. Tanin Sangngam (iBasskung).",

                        "VB.NET and MS Access : Login System with User Authentication.", MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub MainForm_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing

        Try

            If MessageBox.Show("Are you sure you want to exit the application?",

                               "VB.NET 2022 : Form Closing : iBasskung Tutorial",

                               MessageBoxButtons.YesNo, MessageBoxIcon.Question,

                               MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.No Then

                e.Cancel = True

            End If

            If con.State = ConnectionState.Open Then

                con.Close()

            End If

        Catch ex As Exception

        End Try

    End Sub

    Private Sub UserManagementToolStripButton_Click(sender As Object, e As EventArgs) Handles UserManagementToolStripButton.Click

        Try

            ' Hide()

            Dim user As Form = New UserForm()

            user.ShowDialog()

            'Show()

            ShowPermission()

        Catch ex As Exception

        End Try

    End Sub

    Private Sub LoggedInUserToolStripButton_Click(sender As Object, e As EventArgs) Handles LoggedInUserToolStripButton.Click

        ' Display Logged In User Data.

        MessageBox.Show("User ID: " & currentUserID & vbNewLine & vbNewLine &

                "Username: " & currentUsername & vbNewLine & vbNewLine &

                "Permission: " & currentPermission,

                "VB.NET : Current logged in user.",

                MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

End Class

Form: LoginForm.vb

Public Class LoginForm

    Private Sub LoginForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Try

            Me.Text = appTitle2

            Me.AcceptButton = LoginButton

            ToolStripStatusLabel2.Text = dbName

            PasswordTextBox.UseSystemPasswordChar = True

        Catch ex As Exception

        End Try

    End Sub

    Private Sub LoginButton_Click(sender As Object, e As EventArgs) Handles LoginButton.Click

        If UsernameTextBox.Text.Trim = "" Then

            MessageBox.Show("Please Input Username.", subProjectName, MessageBoxButtons.OK,

                            MessageBoxIcon.Warning)

            UsernameTextBox.Focus()

            Exit Sub

        End If

        If PasswordTextBox.Text.Trim() = "" Then

            MessageBox.Show("Please Input Password.", subProjectName, MessageBoxButtons.OK,

                            MessageBoxIcon.Warning)

            PasswordTextBox.Focus()

            Exit Sub

        End If

        Dim ds1 As New DataSet()

        Try

            sql = "SELECT * FROM tblUSer WHERE [Username] = '" & UsernameTextBox.Text.Trim() & "' "

            sql &= "AND [Password] = '" & PasswordTextBox.Text.Trim() & "' "

            ds1 = DisplayData(sql, "user", ds1)

            If ds1 Is Nothing Then

                MsgBox("ds1 was nothing.")

                Return

            End If

            If ds1.Tables("user").Rows.Count > 0 Then

                currentUserID = ds1.Tables("user").Rows(0)("AutoID").ToString().Trim()

                currentUsername = ds1.Tables("user").Rows(0)("Username").ToString().Trim()

                currentPermission = ds1.Tables("user").Rows(0)("Permission").ToString().Trim()

                Me.Close()

            Else

                MessageBox.Show("The Username Or Password is incorrect. Try again.",

                                appTitle2, MessageBoxButtons.OK, MessageBoxIcon.Error)

                UsernameTextBox.Clear()

                PasswordTextBox.Clear()

                UsernameTextBox.Focus()

                Exit Sub

            End If

        Catch ex As Exception

            MessageBox.Show("Error: " & ex.Message, appTitle2, MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub

    Private Sub ExitButton_Click(sender As Object, e As EventArgs) Handles ExitButton.Click

        End

    End Sub

End Class

Form: UserForm.vb

Option Explicit On

Option Strict On

Option Infer On

Public Class UserForm

    Private selectedID As String = ""

    Private Sub UserForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ShowUserData()

    End Sub

    Private Sub CloseToolStripButton_Click(sender As Object, e As EventArgs) Handles CloseToolStripButton.Click

        Me.Close()

    End Sub

    Private Sub ClearToolStripButton_Click(sender As Object, e As EventArgs) Handles ClearToolStripButton.Click

        ClearControls()

    End Sub

    Private Sub ClearControls() ' Let's try.

        selectedID = ""

        UsernameTextBox.Text = ""

        PasswordTextBox.Text = ""

        If PermissionComboBox.Items.Count > 0 Then

            PermissionComboBox.SelectedIndex = 0

        End If

        ToolStripStatusLabel1.Text = "Status:"

    End Sub

    Private Sub AddToolStripButton_Click(sender As Object, e As EventArgs) Handles AddToolStripButton.Click

        SaveUserData("INSERT")

    End Sub

    Private Sub EditToolStripButton_Click(sender As Object, e As EventArgs) Handles EditToolStripButton.Click

        SaveUserData("UPDATE")

    End Sub

    Private Sub DeleteToolStripButton_Click(sender As Object, e As EventArgs) Handles DeleteToolStripButton.Click

        SaveUserData("DELETE")

    End Sub

    Private Sub ShowUserData()

        Me.Text = "User Management Form | Current logged-in user: " & currentUsername.ToUpper() &

            " (ID: " & currentUserID & ")" & " (Log-in as: " & currentPermission & ")"

        Dim ds1 As New DataSet()

        sql = "SELECT [AutoID], Username, [Password], [Permission] FROM tblUser ORDER BY [AutoID] ASC;"

        Try

            ds1 = DisplayData(sql, "tblUser", ds1)

            If ds1 Is Nothing Then

                Return

            End If

            If ds1.Tables("tblUser").Rows.Count > 0 Then

                DataGridView1.DataSource = ds1.Tables("tblUser")

            Else

                DataGridView1.DataSource = Nothing

            End If

            Dim dgv1 As DataGridView = DataGridView1

            If dgv1.RowCount > 0 Then

                dgv1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

                dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

                dgv1.Columns(0).HeaderText = "AutoID"

                dgv1.Columns(1).HeaderText = "Username"

                dgv1.Columns(2).HeaderText = "Password"

                dgv1.Columns(3).HeaderText = "Permission"

                dgv1.Columns(0).Visible = False

                If currentPermission.ToUpper() = "USER" Then

                    AddToolStripButton.Enabled = False

                    EditToolStripButton.Enabled = False

                    DeleteToolStripButton.Enabled = False

                    PasswordTextBox.UseSystemPasswordChar = True

                    dgv1.Columns(1).Width = 336

                    dgv1.Columns(2).Visible = False

                Else

                    AddToolStripButton.Enabled = True

                    EditToolStripButton.Enabled = True

                    DeleteToolStripButton.Enabled = True

                    PasswordTextBox.UseSystemPasswordChar = False

                    dgv1.Columns(1).Width = 168

                    dgv1.Columns(2).Visible = True

                    dgv1.Columns(2).Width = 168

                    dgv1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

                End If

                dgv1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter

                dgv1.Columns(3).Visible = False

                dgv1.ClearSelection()

            End If

        Catch ex As Exception

            MessageBox.Show("Error: " & ex.Message.ToString(), "Error Message.",

                            MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub

    Private Sub DataGridView1_CellMouseDoubleClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseDoubleClick

        Try

            If e.RowIndex = -1 Then

                Exit Sub

            End If

            selectedID = DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString().Trim()

            ToolStripStatusLabel1.Text = "Selected ID: " & selectedID

            UsernameTextBox.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value.ToString().Trim()

            PasswordTextBox.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value.ToString().Trim()

            If Convert.ToString(DataGridView1.CurrentRow.Cells(3).Value).Trim().ToUpper() = "ADMIN" Then

                PermissionComboBox.SelectedIndex = 1 ' Admin

            Else

                PermissionComboBox.SelectedIndex = 2 ' User

            End If

        Catch ex As Exception

        End Try

    End Sub

    Private Sub SaveUserData(dbCommand As String)

        Try

            If dbCommand.ToUpper() = "UPDATE" Or dbCommand.ToUpper() = "DELETE" Then

                If selectedID = "" Then

                    MessageBox.Show("ID not found. Please select at least one item.", appTitle1,

                                    MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Return

                End If

            End If

            If dbCommand.ToUpper() = "INSERT" Or dbCommand.ToUpper() = "UPDATE" Then

                If UsernameTextBox.Text.Trim() = "" Then

                    MessageBox.Show("Please input Username.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Warning)

                    UsernameTextBox.Focus()

                    Exit Sub

                End If

                If PasswordTextBox.Text.Trim() = "" Then

                    MessageBox.Show("Please input Password.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Warning)

                    PasswordTextBox.Focus()

                    Exit Sub

                End If

            End If

            If dbCommand.ToUpper() <> "DELETE" Then

                If PermissionComboBox.SelectedIndex = 0 Then

                    MessageBox.Show("Please select the permission from the ComboBox.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Return

                End If

            End If

            Dim intID As Integer

            Select Case dbCommand.ToUpper()

                Case "INSERT"

                    If MessageBox.Show("Do you want to add this new user?",

                                       "Add New User : iBasskung Tutorial.",

                                       MessageBoxButtons.YesNo, MessageBoxIcon.Question,

                                       MessageBoxDefaultButton.Button2) = DialogResult.No Then

                        Exit Sub

                    End If

                    sql = "INSERT INTO tblUser([Username], [Password], [Permission]) "

                    sql &= " VALUES('" & UsernameTextBox.Text.Trim() & "', '" & PasswordTextBox.Text.Trim() & "', "

                    sql &= "'" & PermissionComboBox.SelectedItem.ToString.Trim() & "')"

                Case "UPDATE"

                    If MessageBox.Show("Do you want to update the selected record?", appTitle1, MessageBoxButtons.YesNo,

                                       MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = DialogResult.No Then

                        Exit Sub

                    End If

                    intID = Convert.ToInt32(selectedID.ToString().Trim())

                    sql = "UPDATE tblUser SET [Username] = '" & UsernameTextBox.Text.Trim() & "', "

                    sql &= "[Password] = '" & PasswordTextBox.Text.Trim() & "', "

                    sql &= "[Permission] = '" & PermissionComboBox.SelectedItem.ToString().Trim() & "' "

                    sql &= "WHERE [AutoID] = " & intID & ""

                    If currentUserID = selectedID Then

                        currentUsername = UsernameTextBox.Text.Trim()

                        currentPermission = PermissionComboBox.SelectedItem.ToString().Trim()

                    End If

                Case "DELETE"

                    If currentUserID = selectedID Then

                        MessageBox.Show("Error Deleting Record: " & "The user " & Chr(39) & currentUsername & Chr(39) &

                                        " Is currently logged in.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Warning)

                        Exit Sub

                    End If

                    If MessageBox.Show("Do you want to delete the selected record?", appTitle1, MessageBoxButtons.YesNo,

                                       MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = DialogResult.No Then

                        Exit Sub

                    End If

                    intID = Convert.ToInt32(selectedID.ToString().Trim())

                    sql = "DELETE FROM tblUser WHERE [AutoID] = " & intID & ""

            End Select

            If ExecuteDb(sql) = True Then

                selectedID = ""

                MessageBox.Show("Your SQL " & dbCommand & " QUERY has been executed successfully.",

                                appTitl1WithDb, MessageBoxButtons.OK, MessageBoxIcon.Information)

                ShowUserData()

            End If

        Catch ex As Exception

            MessageBox.Show("Error: " & ex.Message.ToString(), appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Error)

            Exit Sub

        End Try

    End Sub

End Class