Retrieve data in a datagridview using radio buttons (winforms) VB.NET

Question

0.00/5 (No votes)

See more:

, +

Hello All,

Hello Everyone,

I am a newbie in VB.Net programming. 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 am using a Winforms which contains a Datagridview. 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.

The code in the App.config file is shown below: -

<pre><?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>

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. Can you kindly assist me to get the radio buttons to retrieve data from the DataGridView

What I have tried:

 First, I tried a case selection method but only the first two radio buttons: (i) Today and (ii) Tomorrow elicited an action sometimes: - they would either show all of the data or sometimes if I press these radio buttons nothing happen (once I pressed these radio buttons after I had press the Reload button to reset and reload the original data in the DatagridView). The code is shown below: -

    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
        EmployeesDataGridView.DataSource = bs
        MysqlConn = New MySqlConnection

        Select Case True
            Case rdoToday.Checked
                MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE DATETODAY(Now())"
            Case rdoTomorrow.Checked
                MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 1 DAY)"
            Case rdoYesterday.Checked
                MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE SUBDATE(NOW(),1)"
            Case rdoNext7days.Checked
                MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 7 DAY)"
            Case rdoLast7days.Checked
                MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE SUBDATELASTSEVEN(NOW(),7)"
            Case Else
                MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees"
        End Select
End Sub


When this case select method did not work, I tried this alternative approach. In this scenario, when I press any of the radio buttons, they all show all of the data in the DataGridView. The source code is shown below:

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

        Me.EmployeeDataGridView.DataSource = Me.BindingSource1
        Dim MySqlCommand As New MySqlCommand
        MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE DATETODAY(Now())"

    End Sub

    Private Sub rdoTomorrow_CheckedChanged(sender As Object, e As EventArgs) Handles rdoTomorrow.CheckedChanged
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1
        Dim MySqlCommand As New MySqlCommand
        MySqlCommand.CommandText = "SELECT * FROM mydatabse.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 1 DAY)"
    End Sub

    Private Sub rdoYesterday_CheckedChanged(sender As Object, e As EventArgs) Handles rdoYesterday.CheckedChanged
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1
        Dim MySqlCommand As New MySqlCommand
        MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE SUBDATE(NOW(),1)"
    End Sub

    Private Sub rdoNext7days_CheckedChanged(sender As Object, e As EventArgs) Handles rdoNext7days.CheckedChanged
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1
        Dim MySqlCommand As New MySqlCommand
        MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE DATE(EmployeeDate) = DATE(NOW() - INTERVAL 7 DAY)"
    End Sub

    Private Sub rdoLast7days_CheckedChanged(sender As Object, e As EventArgs) Handles rdoLast7days.CheckedChanged
        Me.EmployeeDataGridView.DataSource = Me.BindingSource1
        Dim MySqlCommand As New MySqlCommand
        MySqlCommand.CommandText = "SELECT * FROM mydatabase.Employees WHERE SUBDATELASTSEVEN(NOW(),7)"
    End Sub

Posted 18-Jul-18 0:49am

wire_jp

Updated 18-Jul-18 19:14pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

wire_jp · Accepted Answer · 2018-07-18T19:15:00

Hi All,

I was able to solve my issue. 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