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

[liemos]

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.

Step 2

Do the form just like shown below.

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.

Download