wire_jp Ответов: 1

Извлечение данных в datagridview с помощью переключателей (winforms) VB.NET

Всем Привет,

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>



В форме есть 5 переключателей: (i)сегодня (ii) завтра (iii) вчера (iv) следующие 7 дней и (v) последние 7 дней, которые позволят пользователю нажать на определенный переключатель и получить данные за эту конкретную дату из DataGridView. Я попробовал несколько различных методов для достижения этой цели, но они не работают. Не могли бы вы любезно помочь мне получить переключатели для извлечения данных из DataGridView

Что я уже пробовал:

 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

1 Ответов

Рейтинг:
5

wire_jp

Всем Привет,

Я смог решить свою проблему. Я создал отдельные функции для каждого сценария даты, чтобы отфильтровать данные для определенного диапазона дат и вернуть результат в datagridview. Затем для каждой кнопки радио, я позвонил в отдельную функцию, и привязать функцию к элементу управления datagridview. Исходный код показан ниже: -

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