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.

CEATE TABLE `tbltransaction` (
  `TRANSID` int(11) NOT NULL,
  `ORNO` int(30) NOT NULL,
  `TRANSDATE` date NOT NULL,
  `AMOUNTSALE` double NOT NULL,
  `CASHIER` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `tbltransaction`
--
 
INSERT INTO `tbltransaction` (`TRANSID`, `ORNO`, `TRANSDATE`, `AMOUNTSALE`, `CASHIER`) VALUES
(1, 70004, '2019-02-21', 385, 'Janno Palacios'),
(2, 70005, '2019-02-21', 385, 'Janno Palacios'),
(3, 70002, '2019-02-17', 385, 'Janno Palacios'),
(4, 70001, '2019-02-18', 385, 'Janno Palacios'),
(5, 70006, '2019-02-19', 69, 'Janno Palacios'),
(6, 70007, '2019-02-21', 69, 'Janno Palacios'),
(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.

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.

    Dim con As MySqlConnection = New MySqlConnection("server=localhost;user id=root;password=;database=dbtransaction;sslMode=none")
    Dim cmd As MySqlCommand
    Dim da As MySqlDataAdapter
    Dim dt As DataTable
    Dim sql As String

Step 5

Create a sub procedure for retrieving data in the database.

    Private Sub findRecords(sql As String, dtg As DataGridView)
        Try
            con.Open()
            cmd = New MySqlCommand
            With cmd
                .Connection = con
                .CommandText = sql
            End With
            da = New MySqlDataAdapter
            da.SelectCommand = cmd
            dt = New DataTable
            da.Fill(dt)
            dtg.DataSource = dt
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
            da.Dispose()
        End Try
    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.

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction`"
        findRecords(sql, dtgList)
    End Sub

Step 7

Write the following codes to filter the data by daily, weekly and monthly in the database.

    Private Sub RadioButton_Click(sender As Object, e As EventArgs) Handles rdo_monthly.Click, rdo_weekly.Click, rdo_daily.Click
        If rdo_daily.Checked = True Then
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE DATE(`TRANSDATE`) =CURDATE()"
        ElseIf rdo_weekly.Checked = True Then
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE WEEK(`TRANSDATE`) =WEEK(NOW())"
        ElseIf rdo_monthly.Checked = True Then
            sql = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` WHERE MONTH(`TRANSDATE`) =MONTH(NOW())"
        End If
        findRecords(sql, dtgList)
    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.