Daily, Weekly and Monthly Report in VB.Net and MySQL Database

Submitted by janobe on Friday, February 22, 2019 - 10:16.
This time, I’m going to teach you how to make Daily, Weekly and Monthly Report in VB.Net and MySQL Database. This is very useful if you have a business because reports provide knowledge to owners about the progress at all levels. This can be an instrument to the success of your business and correct things as needed. This can also be used in any projects. Let’s begin.

Creating Database

Create a database named “dbtransaction”. Execute the following query for creating table and adding data in the table. 
  1. CEATE TABLE `tbltransaction` (
  2.   `TRANSID` int(11) NOT NULL,
  3.   `ORNO` int(30) NOT NULL,
  4.   `TRANSDATE` date NOT NULL,
  5.   `AMOUNTSALE` double NOT NULL,
  6.   `CASHIER` varchar(30) NOT NULL
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  8.  
  9. --
  10. -- Dumping data for table `tbltransaction`
  11. --
  12.  
  13. INSERT INTO `tbltransaction` (`TRANSID`, `ORNO`, `TRANSDATE`, `AMOUNTSALE`, `CASHIER`) VALUES
  14. (1, 70004, '2019-02-21', 385, 'Janno Palacios'),
  15. (2, 70005, '2019-02-21', 385, 'Janno Palacios'),
  16. (3, 70002, '2019-02-17', 385, 'Janno Palacios'),
  17. (4, 70001, '2019-02-18', 385, 'Janno Palacios'),
  18. (5, 70006, '2019-02-19', 69, 'Janno Palacios'),
  19. (6, 70007, '2019-02-21', 69, 'Janno Palacios'),
  20. (7, 70003, '2019-02-07', 138, 'Janno Palacios');

Creating Application

Step 1

Open Microsoft Visual Studio 2015 and create a new windows form application for visual basic. ps1

Step 2

Do the form just like shown below. ps2

Step 3

Open the code editor by pressing the F7 on the keyboard. After that, add a namespace above the public class to access mysql libraries. 
  1. Imports MySql.Data.MySqlClient

Step 4

Create a connection between mysql and visual basic 2015 and declare all the classes that are needed inside the public class. 
  1.     Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=;database=dbtransaction;sslMode=none")
  2.     Dim cmd As MySqlCommand
  3.     Dim da As MySqlDataAdapter
  4.     Dim dt As DataTable
  5.     Dim sql As String

Step 5

Create a sub procedure for retrieving data in the database. 
  1.     Private Sub findRecords(sql As String, dtg As DataGridView)
  2.         Try
  3.             con.Open()
  4.             cmd = New MySqlCommand
  5.             With cmd
  6.                 .Connection = con
  7.                 .CommandText = sql
  8.             End With
  9.             da = New MySqlDataAdapter
  10.             da.SelectCommand = cmd
  11.             dt = New DataTable
  12.             da.Fill(dt)
  13.             dtg.DataSource = dt
  14.         Catch ex As Exception
  15.             MsgBox(ex.Message)
  16.         Finally
  17.             con.Close()
  18.             da.Dispose()
  19.         End Try
  20.     End Sub

Step 6

Write the following codes to retrieve all data in the database to display it in the datagridview in the first load of the form. 
  1.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  2.         sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction`"
  3.         findRecords(sql, dtgList)
  4.     End Sub

Step 7

Write the following codes to filter the data by daily, weekly and monthly in the database. 
  1.     Private Sub RadioButton_Click(sender As Object, e As EventArgs) Handles rdo_monthly.Click, rdo_weekly.Click, rdo_daily.Click
  2.         If rdo_daily.Checked = True Then
  3.             sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE DATE(`TRANSDATE`) =CURDATE()"
  4.         ElseIf rdo_weekly.Checked = True Then
  5.             sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE WEEK(`TRANSDATE`) =WEEK(NOW())"
  6.         ElseIf rdo_monthly.Checked = True Then
  7.             sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE MONTH(`TRANSDATE`) =MONTH(NOW())"
  8.         End If
  9.         findRecords(sql, dtgList)
  10.     End Sub
The complete source code is included. You can download it and run it on your computer. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.

Tags

Comments

Submitted byMario Meireles (not verified)on Fri, 02/22/2019 - 17:47

Bom dia oq voce acha de fazer com banco de dados access abraco
Submitted byjanobeon Fri, 03/01/2019 - 09:21

ok sir, I will create the same article with access dataabase

Add new comment

About text formats