-----------------------------------------------------------------------------
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
-----------------------------------------------------
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
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 :
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!
-----------------------------------------------------------------
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)
