Creating a Log In form with Database
1. Create database “Example”
2. Create table “tblUsers”
3. In the tblUsers insert the fields id, name, username,password, role
4. Set id as autoincrement
5. Insert the following data
id Name username password role
1 Manuel Admin 12345 admin
2 Peter Pete 123 secretary
3 Paul Jay Paul 1234 encoder
4 Pauline Pau 4321 encoder
6. Create a log in form
Creating the Connection between the Visual studio and the Database
7. Then click on the project menu.
8. Click Add Reference
9. Then a dialog box will appear
10. Then Click on the Browse Button
11. Then locate [Link]
12. After locating, the [Link] must be added on the list, then check it
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
13. Then on the SOLUTION EXPLORER, add a MODULE, rename it as “connection” then
copy the following code:
Imports [Link]
Module connection
Public cn As New MySqlConnection("Server=localhost; userid=root; password=;
database=YourDatabaseName")
Public cmd As New MySqlCommand
Public da As New MySqlDataAdapter
Public dt As New DataTable
Public dr As MySqlDataReader
Public Sub connect()
If [Link] = [Link] Then
[Link]()
End If
End Sub
Public Sub disconnect()
If [Link] = [Link] Then
[Link]()
End If
End Sub
End Module
14. Now go back to your log in form design.
15. Then double click the OK button and type the code below, but modify the necessary
modifications needed
Dim i As Integer = 0
[Link]()
With cmd
.Connection = cn
.CommandText = "SELECT * from tblUsers where username ='" & [Link] & "'
and password='" & txt_password.Text & "'"
End With
dr = [Link]
[Link]()
If [Link] Then
[Link]()
'MsgBox("Welcome!", [Link], "Successful Login")
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
[Link]()
Else
MsgBox("Incorrect Username and Password", [Link], "Login")
End If
[Link]()
Designing the Main Form
1. Name it as MainForm and make the Text Main Form
2. Drag two buttons. And name them properly according txtCreateUsers and
txtTransactions
3. Then from the Solution Explorer, add two new forms
4. Name the first one as frmUsers and frmTransaction
Design the frmUsers
This is a DataGridView
***Here we will load the data from
the database
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Note: Upon Dragging the DataGridView this must be how it look like
Click the Edit Columns so we can
have our own Header names.
After Clicking the Edit Columns. This dialog box will appear
Click the Add so we can add our
personalized header to our
datagridview.
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
After Clicking the Add button. Another dialog box will appear
1. -Now Edit the Header Text:
Column1 to Name
2. -Then Click Add: then edit
again the Column2 to
Username
3. -Then to Password
4. -Then to Role
5. And etc.
6. Then close the diaglog box if
you are done.
Your Output should be like this
Now we are done with
our frmUsers design,
we are now ready for
our coding
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Our Next Task is to do this output!!!
Preview:
We will now load the
data of our tblUsers to the
datagridview
Step 1: Double Click the form
Step 2: Above the Private Sub frmUser_Load….. code, Paste the whole code of the sub
LoadData
Sub LoadData()
Dim i As Integer = 0
Try
[Link]()
With cmd
.Connection = cn
.CommandText = "Select * from tblUsers"
dr = [Link]
End With
[Link]()
If [Link] Then
While [Link]
[Link]()
[Link](0, i).Value = [Link](1)
[Link](1, i).Value = [Link](2)
[Link](2, i).Value = [Link](3)
[Link](3, i).Value = [Link](4)
i = i + 1
End While
End If
[Link]()
Catch ex As Exception
MsgBox([Link], [Link])
End Try
End Sub
Step 3: Then inside the Private Sub frmUser_Load… paste the following code
LoadData()
Step 4: You can now test the program if it load the data from the database
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Now we are ready to level up in our task
******************************************************************************
Save Button code
Try
If [Link] = Nothing Or [Link] = Nothing Or [Link] =
Nothing Or [Link] = Nothing Then
MsgBox("Please Complete the Information needed", [Link],
"Product")
Else
[Link]()
With cmd
.Connection = cn
.CommandText = "insert into tblUsers (name, username, password,role)
values ('" & [Link] & "', '" & [Link] & "', '" & [Link] & "',
'" & [Link] & "')"
.ExecuteNonQuery()
MsgBox("Record has been successfully saved.",
[Link], "Save")
End With
'clear textboxes
[Link]()
[Link]()
[Link]()
[Link] = ""
[Link]()
'It will load in the datagridview
LoadData()
End If
Catch ex As Exception
MsgBox([Link], [Link], "Ooooops Duplication of Record")
Finally
[Link]()
End Try
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
That’s it!
**********************************************
Now our next task is to Update Record
Oooops!!! But before that we have to select data from
the datagridview for update. So here are the following
steps guys!
Step 1: Double Click your Datagridview
Click that part there and
then Select and click CellClick
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Paste This Codes there at the Cell Click event of
DataGrid View!!!
Dim i As Integer
i = [Link]
[Link] = [Link](0, i).Value
[Link] = [Link](1, i).Value
[Link] = [Link](2, i).Value
[Link] = [Link](3, i).Value
[Link]=false
Now we are Ready for our update codes
Update Button Code
Try
If [Link] = Nothing Then
MsgBox("No record has been updated", [Link], "Update")
Else
[Link]()
With cmd
.Connection = cn
.CommandText = "update tblUsers set name='" & [Link] & "',
username='" & [Link] & "', password='" & [Link] & "', role='" &
[Link] & "' where id='" & [Link] & "'"
.ExecuteNonQuery()
End With
[Link]()
MsgBox("Record has been successfully updated.", [Link],
"Update")
LoadData() 'select the records
'clear textboxes
[Link]()
[Link]()
[Link]()
[Link] = ""
End If
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Catch ex As Exception
MsgBox([Link], [Link])
Finally
[Link]()
End Try
*******************************************************************
Will you try to run the Program?
Does the program has Error?
I have a secret to tell you then….....
We have to go back from the top………….
But don’t worry it’s just simple
Can you see that small arrow button
there?
-Click it
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Click the edit
columns
Then Add Column
then make the
Header Text as ID
Make sure to set
the Visible
property to False
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Next to modify!!
Now add 1 text box and 1 label in the form
Like This!!!!
Note!!!
Do not forget to set the VISIBLE
property of the id label and id
textbox to FALSE
Try to run the program
This is how it looks like
when you run the
program!!!
We’re almost done!…
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Let’s proceed to the coding
But wait!!! Please, Will you check your
database?
Make sure that you set ID as Auto Increment
Check your codes on this parts!
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Delete Button Code
Try
If [Link] = Nothing Then
MsgBox("No data deleted")
ElseIf [Link]("Are you sure you want to delete?", "Message",
[Link], [Link]) = [Link]
Then
[Link]()
With cmd
.Connection = cn
.CommandText = "delete from tblUsers where id='" & [Link] & "'"
.ExecuteNonQuery()
End With
[Link]()
MsgBox("Record has been successfully Deleted.", [Link],
"Deleted")
LoadData() 'select the records
'clear textboxes
[Link]()
[Link]()
[Link]()
[Link] = ""
End If
Catch ex As Exception
MsgBox([Link], [Link])
Finally
[Link]()
End Try
End Sub
ADD NEW BUTTON CODES
[Link]=true
[Link] =true
[Link] =true
[Link] =true
CLEAR BUTTON CODES
[Link]()
[Link]()
[Link]()
[Link] = ""
NOW WE ARE DONE WITH THE MAIN FOUNDATION CODES OF
CREATING A SYSTEM.
The Save Update Delete
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Now Let us Proceed on Searching a data from
the datagridview
Add the missing parts on our frmUser design
Name the text box as txtSearch
Then double click the textbox and then paste the following codes:
Dim i As Integer = 0
Try
[Link]()
With cmd
.Connection = cn
.CommandText = "select * from tblUsers where name like '%" & [Link] &
"%' or Username like '%" & [Link] & "%'"
dr = [Link]
End With
[Link]()
If [Link] Then
While [Link]
[Link]()
[Link](0, i).Value = [Link](1)
[Link](1, i).Value = [Link](2)
[Link](2, i).Value = [Link](3)
[Link](3, i).Value = [Link](4)
[Link](4, i).Value = [Link](0)
i = i + 1
End While
End If
[Link]()
Catch ex As Exception
MsgBox([Link], [Link])
End Try
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Name: _____________________________________
Individual Type Written Quiz for Finals
Note: This will be your last type written Quiz in ICT 007. After this, we may
now proceed to focus on you final requirement. God bless you guys!
Reflection Paper:
1. As an accountancy students, what can you say that you
can now program?
2. How this programming skills we have develop in this
subject be helpful to you?
3. Could you explain how we integrate the sql queries to
the program design?
***Please send this type written quiz to [Link]***
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007
Individual Laboratory Quiz for Finals
Note: This will be your last laboratory Quiz in ICT 007. After this, we may now
proceed to focus on you final requirement. God bless you guys!
1. Create a Simple Student Information System
2. Make sure to include log in for security purposes. Or else, you may
use the one you created in this activity. Just modify the designs.
3. The following are the information needed in our application:
a. Student Personal Information
Student Id
Student First Name
Student Last Name
Student Gender
Student Age
Student Date of Birth
Student Home Address
Student E-mail Address
Student Course
b. Student Account
Student Total Tuition Fee
Student Prelim payment
Student Midterm Payment
Student Finals Payment
4. Make your own database for this
5. Do not forget to apply the previous lesson.
6. Be creative. You can add more features.
7. Checking of this Laboratory Quiz is on February 19, 2018
Criteria for checking
CRITERIA ITEM POINTS YOUR
POINTS
1. System Design 10 pts.
2. Applying Special Effects 10 pts.
3. Program Completeness 10 pts.
4. No Error 10 pts.
5. Creativity of the features 10 pts.
6. Following of Instructions 10 pts.
7. Speed and Sacrifice in finishing the Program 10pts.
TOTAL POINTS 70 pts.
Prepared by: Eloisa M. Santos All Right Reserved 2018
CRUD Tutorial for ICT 007