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)



Walang komento:

Mag-post ng isang Komento