How to develop a commercial e-Statement solution in vb6 step by step – Part 7 -VB6 User Management System with SQL Server

 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

1. Create a User Management Form
2. Create ADD,UPDATE,DELETE buttons
3. Disable or Enable a Username
4. Make a Username an Administrator
5. Code the form and connect to the SQL database
6. 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

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.