Sorting Data Based on Date in VB.Net

Submitted by janobe on Monday, February 25, 2019 - 17:31.
In this tutorial, I will teach you how to sort data based on date in vb.net. In this method you can sort the data on the database based on the transaction date and it will then be displayed in the datagridview. It has two buttons that has functions such as descending and ascending order. It depends on you which button you will use in sorting data chronologically.

Creating Database

Create a database named “dbtransaction” After that, execute the following query to create a table and add 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
  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 in visual basic. ps1

Step 2

Do the form just like shown below. ps2

Step 3

Open the code editor and add a namespace to access MySQL libraries.
  1. Imports MySql.Data.MySqlClient

Step 4

Create a connection between MSQL database and Visual Basic 2015. After that, declare all the classes that are needed.
  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 sorting in ascending.
  1.     Private Sub Sort_Ascending()
  2.         Try
  3.             con.Open()
  4.             cmd = New MySqlCommand
  5.             With cmd
  6.                 .Connection = con
  7.                 .CommandText = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` ORDER BY TRANSDATE ASC"
  8.             End With
  9.             da = New MySqlDataAdapter
  10.             da.SelectCommand = cmd
  11.             dt = New DataTable
  12.             da.Fill(dt)
  13.             DataGridView1.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

Create a sub procedure for sorting in descending.
  1.     Private Sub Sort_Descending()
  2.         Try
  3.             con.Open()
  4.             cmd = New MySqlCommand
  5.             With cmd
  6.                 .Connection = con
  7.                 .CommandText = "SELECT `ORNO`, `TRANSDATE` as 'Date', `AMOUNTSALE` as 'TOTALAMOUNT', `CASHIER` FROM `tbltransaction` ORDER BY TRANSDATE DESC"
  8.             End With
  9.             da = New MySqlDataAdapter
  10.             da.SelectCommand = cmd
  11.             dt = New DataTable
  12.             da.Fill(dt)
  13.             DataGridView1.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 7

Double click the “Sort by Ascending” button to fire the click event handler of it and call the method for ascending.
  1.     Private Sub btn_ascending_Click(sender As Object, e As EventArgs) Handles btn_ascending.Click
  2.         Sort_Ascending()
  3.     End Sub

Step 8

Double click the “Sort by Descending” button to fire the click event handler of it and call the method for descending.
  1.     Private Sub btn_descending_Click(sender As Object, e As EventArgs) Handles btn_descending.Click
  2.         Sort_Descending()
  3.     End Sub

Step 9

Press F5 to run your project. For any questions about this article. You can contact me @ Email – [email protected] Mobile No. – 09305235027 – TNT Or feel free to comment below.
Tags
Sorting in vb.net
vb.net
MySQL Database
descending order in vb.net
ascending order in vb.net

Add new comment

About text formats
  • 1490 views