MySQL + VB.NET - TabControl 和多个 DataGridview

Question

我的 DataGridViews 有问题，实际上我有一个带有 TabControl 的表单，有 8 个选项卡，每个选项卡都有一个 DataGridView 控件。我在表单加载时填充它们，问题是我在这里受到了某种限制，只有 3 个 DataGridView 被填充，而不是 3 个特定的，而是我在代码中首先放置的任何 3 个。我手动填充它们，执行 mysql 查询，填充 DataTable，然后使数据源等于 DataTable。

有什么想法吗？

Imports MySql.Data.MySqlClient
Imports System.Data

Public Class frmMantenimiento
    Dim conn As MySqlConnection
    Dim myCommand As New MySqlCommand
    Dim myAdapter As New MySqlDataAdapter
    Dim dataEmp As New DataTable
    Dim dataNomi As New DataTable
    Dim dataPres As New DataTable
    Dim dataGast As New DataTable
    Dim dataAmon As New DataTable
    Dim dataHora As New DataTable
    Dim SQL As String

    Public Sub FillEmps()
        conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

        myCommand.Connection = conn
        SQL = "SELECT IDEmpleado as ID, Nombre, Cargo FROM Empleados"

        Try
            conn.Open()

            Try
                myCommand.Connection = conn
                myCommand.CommandText = SQL

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(dataEmp)

                dgvConsEmp.DataSource = dataEmp

                dgvConsEmp.Columns(0).Width = 30
                dgvConsEmp.Columns(1).Width = 200
                dgvConsEmp.Columns(2).Width = 200

            Catch myerror As MySqlException
                MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub

    Public Sub FillNom()
        conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

        myCommand.Connection = conn
        SQL = String.Empty
        SQL = "SELECT IDNomina as ID, Fecha, MontoBruto as 'Monto Bruto', MontoNeto as 'Monto Neto', CantidadEmpleados as Empleados FROM Nomina"

        Try
            conn.Open()

            Try
                myCommand.Connection = conn
                myCommand.CommandText = SQL

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(dataNomi)

                dgvConsNomi.DataSource = dataNomi

                dgvConsNomi.Columns(0).Width = 30

            Catch myerror As MySqlException
                MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub

    Public Sub FillPres()
        conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

        myCommand.Connection = conn
        SQL = String.Empty
        SQL = "SELECT IDPrestamo as ID, IDEmpleado as 'ID Empleado', MontoInicial as Monto, Fecha, MontoDescuento as Descuento, FormaDescuento as Recurrencia, Status FROM Prestamos"

        Try
            conn.Open()

            Try
                myCommand.Connection = conn
                myCommand.CommandText = SQL

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(dataPres)

                dgvConsPres.DataSource = dataPres

                dgvConsPres.Columns(0).Width = 30

            Catch myerror As MySqlException
                MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub

    Public Sub FillGast()
        conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

        myCommand.Connection = conn
        SQL = String.Empty
        SQL = "SELECT IDGasto as ID, IDEmpleado as 'ID Empleado', Monto, Fecha, Concepto, IDUsuario as Creador FROM GastosEmpleados"

        Try
            conn.Open()

            Try
                myCommand.Connection = conn
                myCommand.CommandText = SQL

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(dataGast)

                Me.dgvConsGast.DataSource = dataGast

                Me.dgvConsGast.Columns(0).Width = 30

            Catch myerror As MySqlException
                MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub

    Public Sub FillAmon()
        conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

        myCommand.Connection = conn
        SQL = "SELECT IDAmonestacion as ID, TipoAmonestacion as Tipo, Descripcion, IDUsuario as Creador FROM Amonestaciones"

        Try
            conn.Open()

            Try
                myCommand.Connection = conn
                myCommand.CommandText = SQL

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(dataAmon)

                dgvConsAmon.DataSource = dataAmon

                dgvConsAmon.Columns(0).Width = 30

            Catch myerror As MySqlException
                MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub

    Public Sub FillHora()
        conn = New MySqlConnection("Data Source=localhost; user id=root; password=; database=arj;")

        myCommand.Connection = conn
        SQL = "SELECT IDControlHorario as ID, IDEmpleado as 'ID Empleado', Fecha, Hora FROM ControlHorario"

        Try
            conn.Open()

            Try
                myCommand.Connection = conn
                myCommand.CommandText = SQL

                myAdapter.SelectCommand = myCommand
                myAdapter.Fill(dataHora)

                dgvConsHora.DataSource = dataHora

                dgvConsHora.Columns(0).Width = 30

            Catch myerror As MySqlException
                MsgBox("Ocurrió un error leyendo la base de datos: " & myerror.Message)
            End Try
        Catch myerror As MySqlException
            MessageBox.Show("Ocurrió un error conectando a la base de datos: " & myerror.Message)
        Finally
            If conn.State <> ConnectionState.Closed Then conn.Close()
        End Try
    End Sub


    Private Sub frmMantenimiento_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillEmps()
        FillNom()
        FillPres()
        FillGast()
        FillAmon()
        FillHora()
    End Sub

Answer 1

有很多重复的代码可以重构。但是，我认为根本原因是您没有在每个 FillX() 方法中重新初始化 myCommand 和 myAdapter 变量。因此，我首先将 conn、myCommand、myAdapter 和 SQL 变量设置为每个 的本地变量>FillX() 方法。

然后我会重构代码以应用 DRY（不要重复自己）原则并消除重复的代码。