VB.NET SQL Server : How to Create a Multi-User Role-Based Login System.
Visual
Basic .NET กับฐานข้อมูล SQL Server สร้างระบบ 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.SqlClient
Module ModCenter
Public con As New SqlConnection
Public da As SqlDataAdapter
Public cmd As SqlCommand
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 = "SQL
Server"
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
Dim Constring As String = "Data
Source=.\SQLEXPRESS;Initial Catalog=VBDormSystemDB;Integrated Security=True"
If con.State = ConnectionState.Open
Then
con.Close()
End If
con.ConnectionString = conString
con.Open()
conState = True
' MessageBox.Show("Connected to
Microsoft SQL Server 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 SqlDataAdapter(str, con)
Dim cmdBuilder As New
SqlCommandBuilder(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 SqlCommand(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: 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 and SQL
Server 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 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}
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 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 SQL Server
Database." & vbNewLine & "Dormitory Management System Version 1.0.0"
& vbNewLine &
"Developed by Mr. Tanin Sangngam (iBasskung).", "VB.NET and SQL Server
: 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
End Class
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
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: 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() = "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 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()
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