Searching Data Based on Date Using VB.Net and SQL Server 2018

Submitted by janobe on Wednesday, October 30, 2019 - 16:00.
In this tutorial, I will teach you how to search data based on the date using vb.net and SQL server 2018. This method has the ability to find the data in the datagridview based on the selected date in the DateTimePicker. This will help you find the specific transaction within the selected date that you choose in the DateTimePicker.

Creating Database

1. Install the MSSMS 2018 on your machine. 2. Open the MSSMS 2018 . After that, right click the database, the select “New Database” and name it “dbpos” figure 1 3. Do the following query to create a table in the database that you have created
  1.   USE [dbpos]
  2. GO
  3. /****** Object:  Table [dbo].[tbltransaction]    Script Date: 28/10/2019 2:05:52 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. CREATE TABLE [dbo].[tbltransaction](
  9.         [TransactionId] [INT] IDENTITY(1,1) NOT NULL,
  10.         [InvoiceNo] [INT] NULL,
  11.         [Barcode] [VARCHAR](MAX) NULL,
  12.         [TransactionDate] [datetime] NULL,
  13.         [Price] [money] NULL,
  14.         [TransVat] [nvarchar](50) NULL,
  15.         [TransDiscount] [money] NULL,
  16.         [TransactionQty] [INT] NULL,
  17.         [SubTotal] [money] NULL,
  18.         [UserId] [INT] NULL,
  19.         [OrderId] [INT] NULL,
  20.  CONSTRAINT [PK_tbltransaction] PRIMARY KEY CLUSTERED
  21. (
  22.         [TransactionId] ASC
  23. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  24. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  25. GO

Creating Application

Step 1

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

Step 2

Add a DateTimePicker, and a DataGridview inside the Form. Then do the Form just like shown below. figure 2

Step 3

Press F7 to open the code editor. In the code editor, add a namespace to access SQL Server libraries.
  1.      
  2. Imports System.Data.SqlClient

Step 4

Create a connection between Visual Basic 2015 and SQL Server database. After that, declare and initialize all the classes and variables that are needed.
  1.      
  2.     Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;Database=dbpos;trusted_connection=true;")
  3.     Dim cmd As SqlCommand
  4.     Dim da As SqlDataAdapter
  5.     Dim dt As DataTable
  6.     Dim sql As String

Step 5

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

Step 6

Go back to the design view, double click the DateTimePicker to open the ValueChanged event handler on it. After that, add this code inside the “DateTimePicker1_ValueChanged” event to find the specific transaction in the database.
  1.        
  2.     Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles DateTimePicker1.ValueChanged
  3.  
  4.         Dim d As String = Format(DateTimePicker1.Value, "MM/d/yyyy")
  5.         sql = "SELECT * FROM tbltransaction WHERE FORMAT (TransactionDate, 'd', 'en-US') = '" & d & "'"
  6.         load_data(sql, DataGridView1)
  7.  
  8.     End Sub

Step 7

Write the following codes for retrieving data in the database in the first load of the form.
  1.     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
  2.         sql = "SELECT * FROM tbltransaction"
  3.         load_data(sql, DataGridView1)
  4.     End Sub
Output figure 4 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
Search data using datetimepicker in vb
vb.net
sql server 2018
Retrieving data in the database

Add new comment

About text formats
  • 2220 views