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

on


VB.NET MySQL : How to Create a Multi-User Role-Based Login System.
Visual Basic .NET กับฐานข้อมูล MySQL สร้างระบบ 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 MySql.Data.MySqlClient


Module ModCenter


Public con As New MySqlConnection

Public da As MySqlDataAdapter

Public cmd As MySqlCommand

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 = "MySQL (MariaDB)"


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 Function GetConnectionString() As String


Dim host As String = "server=localhost;"

Dim port As String = "port=3306;"

Dim db As String = "database=vbdormsystem;"

Dim user As String = "user=root;"

Dim pass As String = "password=1234;"


Dim conString As String = String.Format("{0}{1}{2}{3}{4}", host, port, db, user, pass)


Return conString


End Function


Public Sub ManageConnection()


conState = False


Try


Dim conString As String = GetConnectionString()


If con.State = ConnectionState.Open Then

con.Close()

End If


con.ConnectionString = conString

con.Open()

conState = True


' MessageBox.Show("Connected to MySQL (MariaDB) Database.", appTitle1, MessageBoxButtons.OK, MessageBoxIcon.Information)


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 con.State = ConnectionState.Closed Then

ManageConnection()

End If


Dim ds As New DataSet


Try


ds.Clear()

da = New MySqlDataAdapter(str, con)

Dim cmdBuilder As New MySqlCommandBuilder(da)

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 MySqlCommand(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.AcceptButton = LoginButton
Me.Text = appTitle2
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
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
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()
        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 = "(ID: " & currentUserID & ") ---> " & "Log-in as: " & currentPermission.ToUpper()
        ToolStripStatusLabel2.Text = "VB.NET 2022 and MySQL (MariaDB) : 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 2022 : 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}.", "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 YouTubeToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles YouTubeToolStripMenuItem.Click
        YouTubeToolStripButton.PerformClick()
    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 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 UserToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles UserToolStripMenuItem.Click
        UserToolStripButton.PerformClick()
    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 2022 : Current logged in user.", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub AboutToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles AboutToolStripMenuItem.Click
        MessageBox.Show("Visual Basic .NET and MySQL (MariaDB)." & vbNewLine & "Dormitory Management System Version 1.0.0" & vbNewLine &
                        "Developed by Mr. Tanin Sangngam (iBasskung).", "VB.NET 2022 and MySQL (MariaDB) : Dormitory Management System.",
                                                                        MessageBoxButtons.OK, MessageBoxIcon.Information)
    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 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 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 FileToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles FileToolStripMenuItem.Click

    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 CloseToolStripButton_Click(sender As Object, e As EventArgs) Handles CloseToolStripButton.Click
Me.Close()
End Sub

Private Sub ShowUserData()

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

ToolStripStatusLabel1.Text = "Status:"

Dim ds1 As New DataSet()

sql = "SELECT autoid, username, password, permission FROM tblUser ORDER BY autoid ASC;"

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

ClearControls()

End Sub

Private Sub SaveUserData(dbCommand As String)

Try

If dbCommand.ToUpper().Trim() = "UPDATE" Or dbCommand.ToUpper().Trim() = "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().Trim()

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

selectedID = ""

UsernameTextBox.Text = ""
PasswordTextBox.Text = ""

If RoleComboBox.Items.Count > 0 Then
RoleComboBox.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 ClearToolStripButton_Click(sender As Object, e As EventArgs) Handles ClearToolStripButton.Click
ClearControls()
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

' RoleComboBox.SelectedItem = Convert.ToString(DataGridView1.CurrentRow.Cells(3).Value).Trim()

Catch ex As Exception

End Try

End Sub

End Class



Comments

Post a Comment