Programming for Everybody: VB.net: filter dates from access database between two datetimepickers and display in datagridview with source code

VB.net: filter dates from access database between two datetimepickers and display in datagridview with source code

 Imports System.Data.OleDb

Public Class Form4

    Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\dates.accdb")

    Private Sub Form4_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        d1.Format = DateTimePickerFormat.Custom

        d1.CustomFormat = "MM/dd/yyyy"

        d2.Format = DateTimePickerFormat.Custom

        d2.CustomFormat = "MM/dd/yyyy"

        DataGridView1.BackgroundColor = System.Drawing.SystemColors.Control

        If conn.State = ConnectionState.Closed Then

            conn.Open()

        End If

        Dim cmd1 As New OleDbCommand(" select id,date1,username from table1", conn)

        Dim da As New OleDbDataAdapter

        Dim dt As New DataTable

        da.SelectCommand = cmd1

        dt.Clear()

        da.Fill(dt)

        DataGridView1.DataSource = dt

        DataGridView1.Columns(1).DefaultCellStyle.Format = "dd/MM/yyyy"

        DataGridView1.Columns(0).HeaderText = "ID"

        DataGridView1.Columns(1).HeaderText = "Start date"

        DataGridView1.Columns(2).HeaderText = "Username"

        DataGridView1.EnableHeadersVisualStyles = False

        With DataGridView1.ColumnHeadersDefaultCellStyle

            .Font = New Font("arial", 12, FontStyle.Italic)

            .BackColor = Color.Black

            .ForeColor = Color.White

        End With

        DataGridView1.Columns(0).DefaultCellStyle.Font = New Font("tahoma", 10, FontStyle.Bold)

        DataGridView1.Columns(1).DefaultCellStyle.Font = New Font("tahoma", 10, FontStyle.Italic)

        DataGridView1.Columns(2).DefaultCellStyle.Font = New Font("tahoma", 10, FontStyle.Underline)

        DataGridView1.Columns(0).HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter

        DataGridView1.Columns(1).HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter

        DataGridView1.Columns(2).HeaderCell.Style.Alignment = DataGridViewContentAlignment.TopCenter

        DataGridView1.Columns(0).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter

        DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter

        DataGridView1.Columns(2).DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopCenter

        DataGridView1.Columns(0).DefaultCellStyle.BackColor = Color.Yellow

        DataGridView1.Columns(1).DefaultCellStyle.BackColor = Color.Brown

        DataGridView1.Columns(2).DefaultCellStyle.BackColor = Color.Green

        DataGridView1.Rows(0).Cells(0).Selected = False

        DataGridView1.Rows(2).Cells(1).Selected = False

    End Sub

     Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

         If conn.State = ConnectionState.Closed Then

            conn.Open()

        End If

        Dim dtdate1 As DateTime = DateTime.Parse(d1.Text)

        Dim dtdate2 As DateTime = DateTime.Parse(d2.Text)

        Dim cmd1 As OleDbCommand = New OleDbCommand("select id,date1,username from table1 where date1 between #" &

        dtdate1.ToString("MM/dd/yyyy") & "# and #" &

dtdate2.ToString("MM/dd/yyyy") & "# order by date1 desc", conn)

        Dim da As New OleDbDataAdapter

        da.SelectCommand = cmd1

        Dim dt As New DataTable

        dt.Clear()

        da.Fill(dt)

        DataGridView1.DataSource = dt

        conn.Close()

    End Sub

End Class



No comments:

Post a Comment