mysql - 在 VB.NET 中使用多个复选框过滤 MySql 数据库

Question

我想通过组合多个复选框和文本框查询来过滤存储在 MySql 数据库中的数据。您可以在此处找到表格的屏幕截图:

搜索表单:

表格:

项目表

详细信息表

PS:Projects表中只有ID字段是唯一的!

如果用户在这三个文本框中的任何一个中写入，文本框的其余部分将变空。目前它适用于文本框。我想将搜索条件与复选框结合起来。

假设用户在 TEXTBOX1 中写入并从复选框中选择 FAS、VAPA 和 ACC，结果应该列在我的 ListView 框中。

这是我到目前为止的代码:

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    If TextBox1.Text = "" And TextBox2.Text = "" And TextBox3.Text = "" Then
        MsgBox("Bir arama kriteri girin!", MsgBoxStyle.Critical, "UYARI!")
        Exit Sub
    End If


        ListView1.Items.Clear()

        If TextBox1.Text <> "" Then
                Dim cmd As New MySqlCommand
                Dim ad As New MySqlDataAdapter
                Dim projeadi As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE PROJEADI='" & TextBox1.Text & "';"
                Dim tbl As New DataTable
                Dim i As Integer
                With cmd
                    .CommandText = projeadi
                    .Connection = con
                End With
                With ad
                    .SelectCommand = cmd
                    .Fill(tbl)
                End With

                For i = 0 To tbl.Rows.Count - 1
                    With ListView1
                        .Items.Add(tbl.Rows(i)("ID"))
                        With .Items(.Items.Count - 1).SubItems
                            .Add(tbl.Rows(i)("PROJEADI"))
                            .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                            .Add(tbl.Rows(i)("MARKA"))
                        End With
                    End With
                Next

        ElseIf TextBox2.Text <> "" Then
            Dim cmd As New MySqlCommand
            Dim ad As New MySqlDataAdapter
            Dim tekliffirma As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE TEKLIFFIRMA='" & TextBox2.Text & "';"
            Dim tbl As New DataTable
            Dim i As Integer
            With cmd
                .CommandText = tekliffirma
                .Connection = con
            End With
            With ad
                .SelectCommand = cmd
                .Fill(tbl)
            End With

            For i = 0 To tbl.Rows.Count - 1
                With ListView1
                    .Items.Add(tbl.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(tbl.Rows(i)("PROJEADI"))
                        .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                        .Add(tbl.Rows(i)("MARKA"))
                    End With
                End With
            Next
        ElseIf TextBox3.Text <> "" Then
            Dim cmd As New MySqlCommand
            Dim ad As New MySqlDataAdapter
            Dim marka As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE MARKA='" & TextBox3.Text & "';"
            Dim tbl As New DataTable
            Dim i As Integer
            With cmd
                .CommandText = marka
                .Connection = con
            End With
            With ad
                .SelectCommand = cmd
                .Fill(tbl)
            End With

            For i = 0 To tbl.Rows.Count - 1
                With ListView1
                    .Items.Add(tbl.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(tbl.Rows(i)("PROJEADI"))
                        .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                        .Add(tbl.Rows(i)("MARKA"))
                    End With
                End With
            Next
        End If

请指教。

谢谢。

乌古兹

Answer 1

好吧，因为我不知道对应于复选框筛选条件的表字段的名称，我假设它是 Projects.Sistem

我会将所有复选框分组到一个 GroupBox 中，以便使用 For 循环来检查它们。如果选中其中任何一个，AND 子句将添加到 SQL 语句的末尾。

完成后，另一个 For 将检查每个选中的 CheckBox 以将复选框的 Name 添加到您的最终 SQL 语句中，以获得这样的 SQL 语句

SELECT projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID,
details.MARKA from projects INNER JOIN details ON projects.ID = details.ID
WHERE ..... AND Projects.Sistem IN ('FAS', 'VAPA', 'CCTV',.....)

下面的代码只是一个近似值。您将需要检查它并使其适应您现有的代码

Dim SQL As String = ""

If projeadi <> "" Then
   SQL = projeadi
End If

If tekliffirma <> "" Then
   SQL = tekliffirma
End If

If marka <> "" Then
   SQL = marka
End If

For Each chkBox As CheckBox in GroupBox1.Controls
   If chkBox.Checked = True Then
      SQL = SQL & " AND Project.Sistem IN ( "
      Exit For
   End if
Next
For Each chkBox As CheckBox In GroupBox1.Controls
    If chkBox.Checked = True Then
         Sql = Sql & "'" & chkBox.Name & "',"
    End If
Next
Sql = Sql.Substring(0, Sql.Length - 1)
Sql = Sql & ")"