Retrieve data in a DataGridView, on a specific date, using a specific radio button

2018-07-14

Question

_{Saturday, July 14, 2018 4:54 PM}

Hello Everyone,

I am a beginner in VB.Net programming. I will provide a brief overview of my database setup and connection to Visual Studio. I am using a MySql Database which is connected to Visual Studio 2017 Professional (Community Edition) software with the project being written in VB.net. I have installed the following extensions: - MySQL for Visual Studio 1.2.7 and MySQL ConnectorNet 8.0.11. I ensured that the DataSource is MySQL Database so that Visual Studio can connect to the MySQL database. I have managed to load/view the data, from the MySql database, into the DataGridView. On the Form1.vb [Design], a window (at the bottom) show icons for (i) my MySql Database and (ii) BindingSource1.

There are 5 radio buttons on the Form: (i)Today (ii) Tomorrow (iii) Yesterday (iv) Next 7 Days and (v) Last 7 Days which will allow an user to click on a specific radio button and retrieve the data for that specific date from the DataGridView. I have tried a few different methods to achieve this goal but they are not working. If I click on either radio button "Today" or "Tomorrow", the data in the DataGridView disappears and the DataGridView is blank. The other radio buttons do not elicit a result and nothing happens. I have attached my code below (forgive me for the code length, but I added the code to show the databinding to the datatable. The code with the problem is the case selection of the radio buttons at the bottom): -

Imports MySql.Data.MySqlClient
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports MySql.Data
Imports System.Windows.Forms
Imports System.Drawing
Imports System
Public Class Form1

    Dim connection As New MySqlConnection("server=localhost; Port=3306; database=mydatabase; username=root; Password=mypassword")
    Dim MysqlConn As MySqlConnection
    Dim COMMAND As MySqlCommand
    Private connStr As String

Public Class Form1
        Inherits System.Windows.Forms.Form

        Private DataGridView As New DataGridView()
        Private BindingSource As New BindingSource()
        Private dataAdapter As New SqlDataAdapter()
        Private WithEvents reloadButton As New Button()
        Private WithEvents submitButton As New Button()

        <STAThreadAttribute()>
        Public Shared Sub Main()
            Application.Run(New Form1())
        End Sub

        ' Initialize the form.
        Public Sub New()

            Me.DataGridView.Dock = DockStyle.Fill

            Me.reloadButton.Text = "reload"
            Me.submitButton.Text = "submit"

            Dim panel As New FlowLayoutPanel()
            panel.Dock = DockStyle.Top
            panel.AutoSize = True
            panel.Controls.AddRange(New Control() {Me.reloadButton, Me.submitButton})

            Me.Controls.AddRange(New Control() {Me.DataGridView, panel})
            Me.Text = "DataGridView databinding and updating demo"

        End Sub

        Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles Me.Load

            ' Bind the DataGridView to the BindingSource
            ' and load the data from the database.
            GetData("select * from Employees")
            Me.DataGridView.DataSource = Me.BindingSource

        End Sub

        Private Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
            Handles submitButton.Click

            ' Update the database with the user's changes.
            Me.dataAdapter.Update(CType(Me.BindingSource.DataSource, DataTable))

        End Sub

        Private Sub GetData(ByVal selectCommand As String)

            Try
                ' Specify a connection string. Replace the given value with a 
                ' valid connection string for a mydatabase SQL Server sample
                ' database accessible to your system.
                Dim connectionString As String =
                    "Integrated Security=SSPI;Persist Security Info=False;" +
                    "Initial Catalog=mydatabase;Data Source=localhost"

                ' Create a new data adapter based on the specified query.
                Me.dataAdapter = New SqlDataAdapter(selectCommand, connectionString)

                ' Create a command builder to generate SQL update, insert, and
                ' delete commands based on selectCommand. These are used to
                ' update the database.
                Dim commandBuilder As New SqlCommandBuilder(Me.dataAdapter)

                ' Populate a new data table and bind it to the BindingSource.
                Dim table As New DataTable()
                table.Locale = System.Globalization.CultureInfo.InvariantCulture
                Me.dataAdapter.Fill(table)
                Me.BindingSource.DataSource = table

                ' Resize the DataGridView columns to fit the newly loaded content.
                Me.DataGridView.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
            Catch ex As SqlException
                MessageBox.Show("To run this example, replace the value of the " +
                    "connectionString variable with a connection string that is " +
                    "valid for your system.")
            End Try

        End Sub

    End Class

    Private Sub SubmitButton_Click(sender As Object, e As EventArgs) Handles SubmitButton.Click

        Dim dtEmployee As New DataTable()

        Dim command As New MySqlCommand("SELECT * FROM Employees WHERE EmployeeDate BETWEEN @d1 And @d2", connection)

        command.Parameters.Add("@d1", MySqlDbType.Date).Value = DateTimePicker1.Value
        command.Parameters.Add("@d2", MySqlDbType.Date).Value = DateTimePicker2.Value

        Dim adapter As New MySqlDataAdapter(command)

        adapter.Fill(dtEmployee)

        EmployeeDataGridView.DataSource = dtEmployee

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        EmployeeDataGridView.DataSource = GetEmployeeList()

    End Sub

    Private Sub ReloadButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ReloadButton.Click

        Me.BindingSource1.DataSource = GetEmployeeList()
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1

    End Sub
    Private Function GetEmployeeList() As DataTable

        Dim dtEmployee As New DataTable

        Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString

        Using conn As New MySqlConnection(connString)

            Using cmd As New MySqlCommand("SELECT * FROM Employees", conn)

                conn.Open()

                Dim reader As MySqlDataReader = cmd.ExecuteReader()

                dtEmployee.Load(reader)

            End Using

        End Using

        Return dtEmployee

    End Function

    Private Sub rdoToday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoToday.CheckedChanged

        Dim bs As New BindingSource
        Dim dataEmployee As New DataTable
        Dim MySqlCommand As New MySqlCommand
        Dim thisDay As DateTime = DateTime.Today
        Dim Tomorrow As DateTime = Today.AddDays(+1)
        Dim Yesterday As DateTime = Today.AddDays(-1)
        Dim NextSevenDays As DateTime = Today.AddDays(+7)
        Dim LastSevenDays As DateTime = Today.AddDays(-7)
        Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
        bs.DataSource = dataEmployee
        EmployeeDataGridView.DataSource = bs
        MysqlConn = New MySqlConnection

        Select Case True
            Case rdoToday.Checked
                MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO thisDay"
            Case rdoTomorrow.Checked
                MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO Tomorrow"
            Case rdoYesterday.Checked
                MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO Yesterday"
            Case rdoNext7days.Checked
                MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO NexttSevenDays"
            Case rdoLast7days.Checked
                MySqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO LastSevenDays"
            Case Else
                MySqlCommand.CommandText = "SELECT * FROM Employees"
        End Select

    End Sub
End Class

All replies (11)

_{Sunday, July 15, 2018 12:19 AM ✅Answered}

You are missing the field for the WHERE condition e.g.

SELECT * FROM table WHERE DATE(SomeDate) = DATE(NOW() - INTERVAL 1 DAY);

Similar in SQL-Server

SELECT  id ,
        SomeDate ,
        FirstName ,
        LastName
FROM    dbo.WorkingWithDates
WHERE   DATEDIFF(d, SomeDate, GETDATE()) = 1;

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Thursday, July 19, 2018 5:23 AM ✅Answered}

Hi Karen,

Thank you for all of your help and advice. I was able to fix the code in the Visual Studio form.

I created individual functions for each date scenario to the filter the data for the specific date range and return a result to the datagridview. Then for each radio button, I called the individual function and bind the function to the datagridview. The source code is show below: -

Private Function GetEmployeeListToday() As DataTable

        Dim dtEmployeeToday As New DataTable
        Dim adapter As New MySqlDataAdapter("SELECT * FROM mydatabase.Employees WHERE DATE(Now())", connection)

        adapter.Fill(dtEmployeeToday)

        Return dtEmployeeToday

    End Function


    Private Sub rdoToday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoToday.CheckedChanged

        Me.BindingSource1.DataSource = GetEmployeeListToday()
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1

    End Sub

    Private Function GetEmployeeListTomorrow() As DataTable

        Dim dtEmployeeTomorrow As New DataTable
        Dim adapter As New MySqlDataAdapter("SELECT * FROM mydatabase.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 1 DAY)", connection)

        adapter.Fill(dtEmployeeTomorrow)

        Return dtEmployeeTomorrow

    End Function

    Private Sub rdoTomorrow_CheckedChanged(sender As Object, e As EventArgs) Handles rdoTomorrow.CheckedChanged

        Me.BindingSource1.DataSource = GetEmployeeListTomorrow()
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1

    End Sub

    Private Function GetEmployeeListYesterday() As DataTable

        Dim dtEmployeeYesterday As New DataTable
        Dim adapter As New MySqlDataAdapter("SELECT * FROM mydatabase.Employees WHERE SUBDATE(NOW(),1)", connection)

        adapter.Fill(dtEmployeeYesterday)

        Return dtEmployeeYesterday

    End Function

    Private Sub rdoYesterday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoYesterday.CheckedChanged

        Me.BindingSource1.DataSource = GetEmployeeListYesterday()
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1

    End Sub

    Private Function GetEmployeeListNextSevenDays() As DataTable

        Dim dtEmployeeNextSevenDays As New DataTable
        Dim adapter As New MySqlDataAdapter("SELECT * FROM mydatabase.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 7 DAY)", connection)

        adapter.Fill(dtEmployeeNextSevenDays)

        Return dtEmployeeNextSevenDays

    End Function

    Private Sub rdoNext7days_CheckedChanged(sender As Object, e As EventArgs) Handles rdoNext7days.CheckedChanged

        Me.BindingSource1.DataSource = GetEmployeeListNextSevenDays()
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1

    End Sub

    Private Function GetEmployeeListLastSevenDays() As DataTable

        Dim dtEmployeeLastSevenDays As New DataTable
        Dim adapter As New MySqlDataAdapter("SELECT * FROM mydatabase.Employees WHERE SUBDATE(NOW(),7)", connection)

        adapter.Fill(dtEmployeeLastSevenDays)

        Return dtEmployeeLastSevenDays

    End Function

    Private Sub rdoLast7days_CheckedChanged(sender As Object, e As EventArgs) Handles rdoLast7days.CheckedChanged

        Me.BindingSource1.DataSource = GetEmployeeListLastSevenDays()
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1

    End Sub

_{Saturday, July 14, 2018 5:00 PM}

Kindly find below a screenshot of the radio buttons to retrieve data from the DataGridView using a specific date in Visual Studio

_{Saturday, July 14, 2018 5:14 PM}

For your reference, the code in the App.config file is:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
     <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
    </startup>
   <connectionStrings>
     <add name="dbx" connectionString ="server=localhost;port=3306;database=mydatabase;user id=root;password=mypassword" providerName="MySql.Data.MySqlClient"/>
   </connectionStrings>
</configuration>

I tried another unsuccessful approach using only the "Today" radio button, but it did not work. The data in the DataGridView disappeared and it went blank. I used this code: -

    Private Sub rdoToday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoToday.CheckedChanged

        Dim bs As New BindingSource
        Dim dataEmployee As New DataTable
        Dim thisDay As DateTime = DateTime.Today
        bs.DataSource = dataEmployee
        Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
        EmployeeDataGridView.DataSource = bs
        MysqlConn = New MySqlConnection
        MysqlConn.ConnectionString =
        "server=localhost;port=3306;database=mydatabse;user id=root;password=mypassword"
        Dim MysqlCommand As New MySqlCommand

        Dim tableName As String = "Employees"

        If rdoToday.Checked = True Then

            MysqlCommand.CommandText = "SELECT * FROM Employees WHERE EmployeeDate EQUAL TO thisDay"

        End If

        Using conn As New MySqlConnection(connString)

            conn.Open()

            MysqlCommand = New MySqlCommand(MysqlConn.ConnectionString, MysqlConn)

        End Using

        Dim reader As MySqlDataReader = MysqlCommand.ExecuteReader()

            dataEmployee.Load(reader)

    End Sub

_{Saturday, July 14, 2018 6:47 PM}

The basics are using a WHERE condition setup with parameter(s). Basics were the actual dates become parameters and their values come from the DateTimePicker or composed other than yesterday and tomorrow.

Between

SELECT [field list]
FROM [your table]
WHERE (some_date BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

yesterday

SELECT [field list] WHERE SUBDATE(NOW(),1)

tomorrow

SELECT [field list] FROM [your table] WHERE DATEDIFF(some_Date, CURDATE()) = 1

_{Saturday, July 14, 2018 9:34 PM}

Hi Karen,

Thank you for your response. I tried to update my code as attached but I still did not get a result: -

Private Sub rdoToday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoToday.CheckedChanged

Dim bs As New BindingSource
        Dim dataEmployee As New DataTable
        Dim MySqlCommand As New MySqlCommand
        Dim DATETODAY As DateTime = DateTime.Today
        Dim connString As String = ConfigurationManager.ConnectionStrings("dbx").ConnectionString
        bs.DataSource = dataEmployee
        EmployeeDataGridView.DataSource = bs
        MysqlConn = New MySqlConnection

        Select Case True
            Case rdoToday.Checked
                MySqlCommand.CommandText = "SELECT EmployeeID,EmployeeAge,EmployeeCourse* FROM Employees WHERE DATETODAY(Now())"
            Case rdoTomorrow.Checked
                MySqlCommand.CommandText = "SELECT EmployeeID,EmployeeAge,EmployeeCourse* FROM Employees WHERE DATEDIFF(some_Date, CURDATE()) = 1"
            Case rdoYesterday.Checked
                MySqlCommand.CommandText = "SELECT EmployeeID,EmployeeAge,EmployeeCourse* FROM Employees WHERE SUBDATE(NOW(),1)"
            Case rdoNext7days.Checked
                MySqlCommand.CommandText = "SELECT EmployeeID,EmployeeAge,EmployeeCourse* FROM Employees WHERE DATEDIFFNEXTSEVEN(some_Date, CURDATE()) = 7"
            Case rdoLast7days.Checked
                MySqlCommand.CommandText = "SELECT EmployeeID,EmployeeAge,EmployeeCourse* FROM Employees WHERE SUBDATELASTSEVEN(NOW(),7)"
            Case Else
                MySqlCommand.CommandText = "SELECT * FROM Employees"
        End Select

    End Sub

_{Saturday, July 14, 2018 10:12 PM}

The worst method to test SQL statements is directly in code, the best way to test SQL statements in in a tool, in this case MySQL Workbench.

When I write SQL or Oracle, SQL-Server or MS-Access it's in Toad for Oracle, SSMS (SQL-Management Studio) for SQL-Server and for MS-Access, in MS-Access.

By writing SQL statements and talking directly to the database this validates that the proper results are returned while working with code in Visual Studio is the same. Once you validate in the editor (MySQL Workbench) it should work in code and if not you need to come back and indicate this is the case that it validated in the workbench and not in code. This would mean a code issue rather than a SQL issue while at this point it could be code or SQL.

_{Saturday, July 14, 2018 11:28 PM}

Hi Karen,

The MySQL database has two rows of sample data with field list names: EmployeeID, EmployeeDate,EmployeeName, EmployeeAge, EmployeeCourse. The EmployeeDate is 2017-01-30 and 2018-02-28.

When I tested the yesterday date scenario, the MySQL database returned the two rows of data in the query which is shown below (however, I am a bit confused as I thought that no data would be returned, since yesterday is 2018-07-13 and the table do not contain any data for this date):-

SELECT EmployeeID,EmployeeDate,EmployeeName,EmployeeCourse 
FROM Employees
WHERE  SUBDATE(NOW(),1)

For the tomorrow scenario, the MySQL database did not return any data in the query which is shown below: -

SELECT EmployeeID,EmployeeDate,EmployeeName,EmployeeCourse 
FROM Employees
WHERE DATEDIFF('2018-01-29', CURDATE()) = 1

_{Sunday, July 15, 2018 1:06 AM}

Hi Karen,

Thank you for your prompt response. I amended the WHERE condition (for the tomorrow date scenario) but no data being returned in the MySQL query: -

SELECT EmployeeID ,
    EmployeeDate ,
        EmployeeName ,
        EmployeeAge ,
        EmployeeCourse ,
       
FROM Employees
WHERE DATE('2018-03-01') = DATE(NOW() - INTERVAL 1 DAY);

_{Sunday, July 15, 2018 2:16 AM}

If you carefully inspect my last reply I have a date to for the WHERE condition, this is what you are missing.

_{Sunday, July 15, 2018 12:42 PM}

Hi Karen,

I changed one of the dates in the table to yesterday's date: 2018-07-14 and when I ran the query, it has returned this specific row of data for the date 2018-07-14: -

SELECT * From employeedb.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 1 DAY);

The Action Output showed the following:

Action:-

SELECT * From employeedb.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 1 DAY); LIMIT 0, 1000;

Message:

1 row(s) selected

שתף באמצעות

Retrieve data in a DataGridView, on a specific date, using a specific radio button

Question

All replies (11)

משאבים נוספים