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)