VB.NET Multi User Role Based Login System (SQLite)

VB.NET with SQLite : Multi-User Role-Based Login System.
Visual Basic .NET กับฐานข้อมูล SQLite สร้างระบบ login แบบแบ่งระดับผู้ใช้

Full tutorial: VB.NET with MS Access : How to Create a Multi-User Role-Based Login System.

VB.NET Source Code by iBasskung.

Module: ModCenter.vb

Option Explicit On

Option Strict On

Imports System.Data.SQLite

Module ModCenter

Public con As SQLiteConnection

Public da As SQLiteDataAdapter

Public cmd As SQLiteCommand

Public sql As String

Public Const strLang As String = "VB.NET 2022"

Public Const strAnd As String = Chr(38)

Public Const strSpace As String = Chr(32)

Public Const dbName As String = "SQLite"

Public Const mainProjectName As String = "Dormitory Management System."

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

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

Public appTitle1withDb 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

con = New SQLiteConnection()

Dim dbName As String = "vbdormsystem.db;"

Dim dbPath As String = Application.StartupPath & "\" & dbName

Dim conString As String = "Data Source=" & dbPath & "Version=3;New=False;Compress=True;"

con.ConnectionString = conString

con.Open()

conState = True

If con.State = ConnectionState.Open Then

' MessageBox.Show("The connection is " & con.State.ToString() & "." & vbNewLine &

' "Connected to SQLite Database.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)

End If

Catch ex As Exception

conState = False

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

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 SQLiteDataAdapter(str, con)

da.Fill(myDs, tbl)

ds = myDs

Catch ex As Exception

ds = Nothing

conState = False

MessageBox.Show("Error Displaying Data: " & ex.Message, appTitle1withDb, 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 SQLiteCommand(mySQL, con)

cmd.CommandTimeout = 600

cmd.ExecuteNonQuery()

bCheck = True

Catch ex As Exception

bCheck = False

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

Finally

conState = False

con.Close()

End Try

ExecuteDb = bCheck

End Function

End Module

Form: LoginForm.vb

Option Explicit On

Option Strict On

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

UsernameTextBox.Text = "admin"

PasswordTextBox.Text = "1234"

Catch ex As Exception

End Try

End Sub

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

End ' Environment.Exit(0);

End Sub

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

If UsernameTextBox.Text = "" Then

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

UsernameTextBox.Focus()

Exit Sub

End If

If PasswordTextBox.Text = "" 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

'MessageBox.Show("ds1 was nothing.")

'Throw New System.NullReferenceException("Dataset 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.ToString(), appTitle2, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

End Class

Form: MainForm.vb

Option Explicit On

Option Strict On

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

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

        UserLoginToolStripButton.Text = currentUsername

        RoleLabel.Visible = True

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

        ToolStripStatusLabel2.Text = "VB.NET 2022 and SQLite Database : Dormitory Management System v1.0.0 developed by iBasskung."

    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

        End If

        currentUserID = ""

        currentUsername = ""

        currentPermission = ""

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

        UserLoginToolStripButton.Text = "Login User"

        RoleLabel.Text = "Logged-Out."

        ' RoleLabel.Visible = False

        ToolStripStatusLabel2.Text = "(Logged-Out)"

        MainForm_Load(sender, e)

    End Sub

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

        MessageBox.Show("Visual Basic .NET 2022 and SQLite Database." & vbNewLine & "Dormitory Management System Version 1.0.0" & vbNewLine &

                        "Developed by Mr. Tanin Sangngam (iBasskung).", "VB.NET and SQLite : Dormitory Management System.",

                                                                        MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub UserToolStripButton_Click(sender As Object, e As EventArgs) Handles UserToolStripButton.Click

        Try

            Dim user As Form = New UserForm()

            user.ShowDialog()

            ShowPermission()

        Catch ex As Exception

        End Try

    End Sub

    Private Sub RoomToolStripButton_Click(sender As Object, e As EventArgs) Handles RoomToolStripButton.Click

        MessageBox.Show("Room Management : iBasskung Tutorial.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub CustomerToolStripButton_Click(sender As Object, e As EventArgs) Handles CustomerToolStripButton.Click

        MessageBox.Show("Customer Management : iBasskung Tutorial.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)

    End Sub

    Private Sub YouTubeToolStripButton_Click(sender As Object, e As EventArgs) Handles YouTubeToolStripButton.Click

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

    End Sub

    Private Sub CurrentUserToolStripButton_Click(sender As Object, e As EventArgs) Handles UserLoginToolStripButton.Click

        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

    Private Sub UserToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles UserToolStripMenuItem.Click

        UserToolStripButton.PerformClick()

    End Sub

    Private Sub RoomToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles RoomToolStripMenuItem.Click

        RoomToolStripButton.PerformClick()

    End Sub

    Private Sub CustomerToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CustomerToolStripMenuItem.Click

        CustomerToolStripButton.PerformClick()

    End Sub

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

        YouTubeToolStripButton.PerformClick()

    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

End Class

Form: UserForm.vb

Option Explicit On

Option Strict 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 ClearControls()

selectedID = ""

UsernameTextBox.Text = ""

PasswordTextBox.Text = ""

If RoleComboBox.Items.Count > 0 Then

RoleComboBox.SelectedIndex = 0

End If

ToolStripStatusLabel1.Text = "Status:"

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

'MessageBox.Show("Error: Dataset was nothing.")

'Throw New System.NullReferenceException("Dataset was Nothing.")

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 = "Auto ID"

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

' ClearToolStripButton.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

'ClearToolStripButton.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

ClearControls()

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 RoleComboBox.SelectedIndex = 0 Then

MessageBox.Show("Please select 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 &= "'" & RoleComboBox.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() & "', password = '" & PasswordTextBox.Text.Trim() & "', "

sql &= "permission = '" & RoleComboBox.SelectedItem.ToString().Trim() & "' "

sql &= "WHERE autoid = " & intID & ""

If currentUserID = selectedID Then

currentUsername = UsernameTextBox.Text.Trim()

currentPermission = RoleComboBox.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.", appTitle1withDb, 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

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 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 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()

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

' MsgBox(Convert.ToString(DataGridView1.CurrentRow.Cells(3).Value).Trim())

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

RoleComboBox.SelectedIndex = 1

Else

RoleComboBox.SelectedIndex = 2

End If

Catch ex As Exception

End Try

End Sub

End Class