VB6 User Management System with SQL Server
On our 5th post on this series, we create a Login Form for the e-Statement application. In this post we are going to create a VB6 User Management System with SQL Server where we can ADD,UPDATE,DELETE usernames in the system.A User Management System is a necessary module for our e-Statement solution. This will enable use to create or update system users and give then access to the e-Statement application as needed.
ATTENTION: For you to understand this post you need to look at the previous posts if you haven`t done so already
If you haven`t been following these posts from the beginning you need to start from the beginning where we create the SmartMail database, tables, Vb modules and all the other parts of this system so far. If you have, let us create the User Management System with SQL Server.
In Summary, we are going to do the following in this article
- Create a User Management Form
- Create ADD,UPDATE,DELETE buttons
- Disable or Enable a Username
- Make a Username an Administrator
- Code the form and connect to the SQL database
- Test the User Management System
Create a User Management Form
Open you SmartMail project and add a new form. Name your from as ‘frmSystemUsers’ and give it a Caption of ‘Manage Users’.
On the form add the following Controls
Control | Name | Caption |
Command Button | cmdNew | ADD |
Command Button | cmdUpdate | UPDATE |
Command Button | cmdDelete | DELETE |
Command Button | cmdExit | EXIT |
Data Combo | dcUser | |
Text Box | txtUsername | |
Text Box | txtName | |
Text Box | txtPass | |
Text Box | txtConfirmPass | |
Lable | Username | |
Lable | Full Name | |
Lable | Password | |
Lable | Confirm Password | |
Check Box | chkAdmin | Make User Administrator |
Check Box | chkDisable | Disable User |
Your Form should look like below
Now we need to code the form, below is the code for all the actions needed to make the form work. Copy and paste the code to your form.
'eStatement Application Development 'User Management Module 'www.smarttechdiary.com Option Explicit Dim IsAdmin As String Dim IsDiabled As String Dim Username As String Dim Password As String Dim ConfirmPassword As String Dim UpdatePass As Boolean Dim rs As New Recordset Private Sub cmdDelete_Click() 'Delete Username in the Users table With rs If rs.State = 1 Then rs.Close .Open "SELECT * FROM Users WHERE Username='" & txtUsername & "'", CN, adOpenDynamic, adLockOptimistic If Not .EOF Then .Delete MsgBox "Username deleted", vbExclamation Else MsgBox "Username does not exists", vbExclamation End If .Close End With End Sub 'Close the User Management Form Private Sub cmdExit_Click() Unload Me End Sub 'Add new username Private Sub cmdNew_Click() 'Check if passwords are the same If txtPass <> txtConfirmPass Then MsgBox "Passwords do not match", vbExclamation Exit Sub End If 'Check if a username is typed If txtUsername.Text = "" Then MsgBox "Please enter a username", vbExclamation Exit Sub End If 'Check if a name is typed If txtName.Text = "" Then MsgBox "Please enter a username", vbExclamation Exit Sub End If 'Check if a password is typed If txtPass.Text = "" Then MsgBox "Please enter a password", vbExclamation Exit Sub End If 'Check if password is confirmed If txtConfirmPass.Text = "" Then MsgBox "Please enter confirm your password", vbExclamation Exit Sub End If 'Query the Users table With rs If rs.State = 1 Then rs.Close 'Check if another user is using the username .Open "SELECT * FROM Users WHERE Username='" & txtUsername & "'", CN, adOpenDynamic, adLockOptimistic If Not .EOF Then MsgBox "Username already exists,please choose a different username", vbExclamation, "New Username" Exit Sub Else 'Add the new Username .AddNew rs!Username = txtUsername.Text rs!Name = txtName.Text rs!Password = txtPass.Text rs!IsAdmin = chkAdmin.Value rs!isDisabled = chkDisable.Value rs!CreatedBy = CurrUser.USER_NAME 'Current Logged in user .Update MsgBox "New username successfully added", vbInformation End If .Close End With End Sub 'Update the current selected username Private Sub cmdUpdate_Click() UpdatePass = False If dcUser.Text = "" Then MsgBox "Please select a username to update", vbExclamation, "User Management" Exit Sub End If 'Check if we are updating the password If txtPass.Text <> "" Then UpdatePass = True 'Check if the passwords match If txtPass.Text <> txtConfirmPass Then MsgBox "Passwords do not match", vbExclamation, "Password Mismatch" Exit Sub End If End If 'Select the current username details With rs If rs.State = 1 Then rs.Close .Open "SELECT * From Users WHERE ID='" & dcUser.BoundText & "'", CN, adOpenDynamic, adLockOptimistic If Not .EOF Then rs!Username = dcUser.Text rs!Name = txtName.Text 'Update password if the user typed a new password If UpdatePass = True Then rs!Password = txtPass.Text End If rs!IsAdmin = chkAdmin.Value rs!isDisabled = chkDisable.Value .Update MsgBox "Updated", vbInformation, "Username Modified" End If .Close End With End Sub Private Sub dcUser_Change() 'Load select user details txtUsername.Text = getValueAt("SELECT Username FROM Users WHERE ID='" & dcUser.BoundText & "'", "Username") txtName.Text = getValueAt("SELECT Name FROM Users WHERE ID='" & dcUser.BoundText & "'", "Name") chkAdmin.Value = 0 IsAdmin = getValueAt("SELECT IsAdmin FROM Users WHERE ID='" & dcUser.BoundText & "'", "IsAdmin") If IsAdmin = True Then chkAdmin.Value = 1 chkDisable.Value = 0 IsDiabled = getValueAt("SELECT IsDisabled FROM Users WHERE ID='" & dcUser.BoundText & "'", "IsDisabled") If IsDiabled = True Then chkDisable.Value = 1 End Sub Private Sub Form_Load() 'Get usernames from the Users table bind_dc "SELECT * FROM Users", "Username", dcUser, "ID" End Sub
I hope you enjoyed this post.In case of any questions please leave your comments below or contact me directly. Kindly like and share with your friends using the below buttons.
Leave a Reply