Final task: CC105 Information Management

Create a windows form that retrieves the username and Password of the user.

The windows form must have the following functionalities

1. The form is capable of adding a new user 

2. The details of the user can be displayed on the

Datagridview when you add a new user. (Tip: you can use the Login code on form1.vb)

3. Only the user with Admin position can log in and can view and modify the details of the user

4. The form must be capable of modifying the existing details of the user

5. The form is capable of deleting the existing details of the user

To the other details of your task, feel free to use and run the sample program I provided. Some of the buttons of the new form for retrieving username and password were disabled, you have to do the same!

For your Final:

I want you to copy and paste your software project on your own USB flash drive ( bumili ka na lang ng sarili mong USB) and submit it personally to me.

Deadline for submission will be on or before: March 8, 2019.

Late submission of your software project will not be accepted! Do not submit it if there were some errors.

Username: Clinton101

Password : Clint

Hint: You can use the given part of an SQL query conditions below under login button. ^_^

Make your own layout. Do not copy my own (basic) design! ^_^

GOD BLESS!

Connecting MS Access Database from MS Visual Studio

-----------------------------------------------------------------------------

CREATE A TABLE

-----------------------------------------------------------------------------

------------------------------------------------------------

BEFORE WE CONNECT THE DATABASE ACCESS FILE FROM WINDOWS APPLICATION , DO THE FF:

________________________________________

------------------------------------------------------------------------------------------------------------------------

CREATE NEW PROJECT IN VISUAL STUDIO

(FOLLOW THE STEPS)

------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------

ADD NEW WINDOWS FORM

--------------------------------------

--------------------------------

COPY AND PASTE THE DATABASE ACCESS FILE TO YOUR VB PROJECT(INSIDE DEBUG FOLDER)

---------------------------------

----------------------------
CREATE A MODULE
----------------------------



-----------------------------------------------------
ADD THE GIVEN CODE BELOW
-----------------------------------------------------

---------------------------------------

GO TO FORM1.VB(LOGIN FORM)

--------------------------------------

! Do the following

Login Code: 

============================================================

con.Open()

        Dim cmd As New OleDb.OleDbCommand("SELECT * from [user] where [username] = '" & TextBox1.Text & _

                                          "' and [password] = '" & TextBox2.Text & "'", con)

        Dim dr As OleDbDataReader = cmd.ExecuteReader

        'the ff. variable hold true if user is found

        Dim userfound As Boolean = False

        'the ff. variables will hold the firstname and lastname 

        Dim Firstname As String

        Dim Lastname As String

        'if found 

        While dr.Read

            userfound = True

            Firstname = dr("Firstname").ToString

            Lastname = dr("Lastname").ToString

        End While

        'check the result

        If userfound = True Then

            con.Close()

            mainform.Show()

            Me.Hide()

            mainform.lblstatususer.Text = Firstname

        Else

            MsgBox("Sorry, username or password not found!", MsgBoxStyle.OkOnly, "Invalid Login")

        End If

        con.Close()

==========================================================

Displaying Data in the Windows Forms DataGridView Control 

-----------------------------------

GO TO MAINFORM.vb

-----------------------------------

Display Data Code: 

 ==============================

Public Sub displaydata()

        con.Open()

        Dim dt As New DataTable("emp")

        Dim rs As New OleDb.OleDbDataAdapter("Select * from [emp]", con)

        rs.Fill(dt)

        DataGridView1.DataSource = dt

        DataGridView1.Refresh()

        rs.Dispose()

        con.Close()

        lblstatusrowcounter.Text = DataGridView1.Rows.Count

    End Sub

========================

-------------------

Result 

--------------------

=============================================================

[[[[[[ADDING AND SAVING DATA]]]]]]]]

==============================

1. Go to mainfrom.vb

(add the given code below)

2. Go to mainform Sourcecode file , place the cursor after "Public Class mainform" . Create a method name "adddata()". Add the given code below!

3.  Add the method name "adddata()"

! Try to add new data using the textboxes! , then hit save!

Add new code: 

Public Sub adddata()

        'to stop the program from crashing!! pa add netong Try Catch ^_^ !

        Try

        con.Open()

            Dim rs As New OleDb.OleDbCommand("INSERT into [emp]([EmpID], [Firstname], [Lastname], [Position], [No_of_days],[Salary_per_day],[Bonus], [Deductions], [Gross_salary], [Net_salary], [SalaryFrom],[Datesaved] ) VALUES ('" & txtid.Text & _

                                         "', '" & txtfirstname.Text & "', '" & txtlastname.Text & _

                                         "', '" & txtposition.Text & "', '" & txtno_of_days.Text & _

                                         "', '" & txtsalaryperday.Text & "','" & txtbonus.Text & _

                                         "', '" & txtdeduction.Text & "', '" & lblgross.Text & _

                                         "', '" & lblnet.Text & "','" & txtsalaryfrom.Text & "', '" & Date.Now & "')", con)

        rs.ExecuteNonQuery()

        con.Close()

            displaydata()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

Code under add new buton: 

===============

 displaydata()

        GroupBox1.Enabled = True

        'tO CLEAR TEXTBOX

        txtid.Text = ""

        txtfirstname.Text = ""

        txtlastname.Text = ""

        txtposition.Text = ""

        txtsalaryfrom.Text = ""

        txtno_of_days.Text = ""

        txtsalaryperday.Text = ""

        txtbonus.Text = ""

        txtdeduction.Text = ""

        Dim empid As String

        Dim getcountrow As String

        getcountrow = DataGridView1.Rows.Count + 1

        'TO GENERATE EMPLOYEE ID NO.

        empid = (Date.Now.Year.ToString - 2000) & _

            Date.Now.Month.ToString & Date.Now.Day.ToString & _

            "-" & getcountrow

        txtid.Text = empid

===================================
CALCULATE GROSS SALARY AND NET SALARY
===================================

Code: 

'=================================

 lblgross.Text = Val(txtno_of_days.Text) * Val(txtsalaryperday.Text) + Val(txtbonus.Text)

        lblnet.Text = Val(txtno_of_days.Text) * Val(txtsalaryperday.Text) + Val(txtbonus.Text) - Val(txtdeduction.Text)

        lblgross.Text = CDbl(lblgross.Text).ToString("#,##0.00")

        lblnet.Text = CDbl(lblnet.Text).ToString("#,##0.00")

'==================================================================

RESULT

============================
FINDING SPECIFIC RECORD FROM DATABASE TABLE
============================

SEARCH CODE: 

   Public Sub searchdata()

        'para ma search ung specific data!

        con.Open()

        Dim dt As New DataTable("emp")

        Dim rs As New OleDb.OleDbDataAdapter("Select * from emp where EmpId = '" & _

                                            txtsearch.Text & "' or Firstname = '" & _

                                             txtsearch.Text & "' or Lastname ='" & _

                                             txtsearch.Text & "'", con)

        rs.Fill(dt)

        DataGridView1.DataSource = dt

        DataGridView1.Refresh()

        rs.Dispose()

        con.Close()

        'to count number of rows!!!

        lblstatusrowcounter.Text = DataGridView1.RowCount

        If Val(lblstatusrowcounter.Text) = 1 Then

            Dim i As Integer

            i = DataGridView1.CurrentRow.Index.ToString

            'in Datagridview1

            txtid.Text = DataGridView1.Item(0, i).Value

            txtfirstname.Text = DataGridView1.Item(1, i).Value

            txtlastname.Text = DataGridView1.Item(2, i).Value

            txtposition.Text = DataGridView1.Item(3, i).Value

            txtno_of_days.Text = DataGridView1.Item(4, i).Value

            txtsalaryperday.Text = DataGridView1.Item(5, i).Value

            txtbonus.Text = DataGridView1.Item(6, i).Value

            txtdeduction.Text = DataGridView1.Item(7, i).Value

            lblgross.Text = DataGridView1.Item(8, i).Value

            lblnet.Text = DataGridView1.Item(9, i).Value

            txtsalaryfrom.Text = DataGridView1.Item(10, i).Value

        End If

    End Sub

===========================
Next 

=====================
Code under search textbox: 
====================
 searchdata()
        lblgross.Text = Val(txtno_of_days.Text) * Val(txtsalaryperday.Text) + Val(txtbonus.Text)
        lblnet.Text = Val(txtno_of_days.Text) * Val(txtsalaryperday.Text) + Val(txtbonus.Text) - Val(txtdeduction.Text)

        'currency Format "Double" (para may decimal point ung output )
        lblgross.Text = CDbl(lblgross.Text).ToString("#,##0.00")
        lblnet.Text = CDbl(lblnet.Text).ToString("#,##0.00")

        If txtsearch.Text = "" Then
            displaydata() '  pseudocode: pag blank ung textbox, idisplay lahat ng data sa emp table!

        End If

===============================

Result: 

===============================

=======================

UPDATE SPECIFIC RECORD

=======================

=========
Update code: 
========
 Public Sub updatedata()
        con.Open()
        Dim rs As New OleDb.OleDbCommand("Update [emp] set  [Firstname] = '" & _
                                         txtfirstname.Text & "', [Lastname]='" & _
                                         txtlastname.Text & "', [Position] = '" & _
                                         txtposition.Text & "', [No_of_days] = '" & _
                                         txtno_of_days.Text & "', [Salary_per_day] = '" & _
                                         txtsalaryperday.Text & "', [Bonus] = '" & _
                                         txtbonus.Text & "', [Deductions] = '" & _
                                         txtdeduction.Text & "', [Gross_salary] = '" & _
                                         lblgross.Text & "', [Net_salary] = '" & lblnet.Text & _
                                         "', [Salaryfrom] = '" & txtsalaryfrom.Text & "' where EmpID = '" & _
                                         txtid.Text & "'", con)
        rs.ExecuteNonQuery()
        con.Close()
        displaydata()
    End Sub
=============================

Next

Code: 

updatedata()

====================

Result : 

=========================

Delete a record from the database table

========================

Code: 
===============================================
   Public Sub deletedata()
        con.Open()
        Dim rs As New OleDb.OleDbCommand("Delete from emp where Empid= '" & _
                                         txtid.Text & "'", con)
        rs.ExecuteNonQuery()
        con.Close()
        displaydata()

    End Sub
=========================================================


NEXT


Result


Code under delete button : 

     Dim message As String = MsgBox(" Are you sure you want to permanently delete the selected record(s)? ", MsgBoxStyle.YesNo, "Delete")
        If message = DialogResult.Yes Then
            deletedata()
            MsgBox(txtid.Text & " has been deleted!")

            'tO CLEAR TEXTBOX
            txtid.Text = ""
            txtfirstname.Text = ""
            txtlastname.Text = ""
            txtposition.Text = ""
            txtsalaryfrom.Text = ""
            txtno_of_days.Text = ""
            txtsalaryperday.Text = ""
            txtbonus.Text = ""
            txtdeduction.Text = ""
            displaydata() ' to display all the data
        Else

        End If
=========================================================

Printing data from a database

To print the data from the database table

we need a seperate program.  We'll be working with  "Crystal Reports" to do this!

 

------------------------------------------------------------------
Since the continuation of your task is too complex, instead of giving the right steps using multiple images, I decided to create a video tutorial on how to print the data from our data source.
Feel free to watch the video below!
-----------------------------------------------------------------

==========================================================
PRINTING SPECIFIC RECORD
============================











------------------------------
watch this video tutorial below. This is the continuation of your task on how to print specific data using parameter field . 
-------------------------------


-------------------------------------------


------------------------
CODER UNDER PRINT
----------------------
Dim report1 As New CrystalReport2
        report1.SetParameterValue("empid", txtid.Text)
        report1.SetParameterValue("firstname", txtfirstname.Text)

        report1.SetParameterValue("lastname", txtlastname.Text)
        report1.SetParameterValue("salaryfrom", txtsalaryfrom.Text)
        report1.SetParameterValue("no_of_days", txtno_of_days.Text)
        report1.SetParameterValue("salaryperday", txtsalaryperday.Text)
        report1.SetParameterValue("bonus", txtbonus.Text)
        report1.SetParameterValue("deductions", txtdeduction.Text)
        report1.SetParameterValue("grossSalary", lblgross.Text)

        report1.SetParameterValue("netSalary", lblnet.Text)