如何使用VB.NET和ms access过滤datagridview？ [英] How to filter datagridview using VB.NET and ms acces?

问题描述

我是编程新手，我只是通过观看教程视频和阅读文章来学习编程。我一直在寻找这个，但未能找到它。作为像我这样的新手，我们非常感谢像你这样的专家提供的任何帮助。



我创建了一个表单，并使用datagridview我的数据库数据，我是能够使用文本框和我创建的按钮进行搜索。但是我想对它进行过滤，所以如果用户想从日本搜索IT人员，那么他们在第一个组合框中选择Country，在第二个combobox2中选择日本。现在他们想要搜索状态为Hired的日本IT员工，所以他们在第二个文本框中输入Hired，数据网格显示日本IT员工的状态为Hired。



这是我的表

I am a newbie in programming and I just learn programming by watching tutorial videos and reading articles. I have been searching for this but failed to find it. As a newbie like me we really appreciate any kind of help from the expert like you guys.

I created a form and use datagridview where my database data goes and i am able to search using textbox and the Button that I created. But I want to put a Filter on it so If a user wants to search the IT Staff from japan, so they choose Country in the first combobox and japan in 2nd combobox2. now they want to search IT Staff from japan whose status is Hired, so they enter Hired in the second text box , and the data grid shows IT Staff from japan whose status is Hired.

This is my Table

Position       Country          Status

IT Staff        Japan            Hired
Teacher        Germany           Not Hired
IT Staff        Mexico           Hired

当我在我的文本框中输入Hired在我的Datagridview中显示

when i type on my textbox "Hired" in my Datagridview shows

IT Staff          Japan            Hired
IT Staff          Mexico           Hired

我只想在我的datagridview中显示所有IT员工日本谁雇了。



我尝试过：

I only want to show in my datagridview all IT Staff in japan whose hired.

What I have tried:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
      If ComboBox1.Text = "Country" Then
          If ComboBox2.Text = "Japan" Then
              Status()
          End If
      End If
  End Sub

Public Sub Status()
      Try
          Dim sCon As String = String.Format("Provider= Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db.accdb")
          Dim str As String = "SELECT * FROM dbtable WHERE Status = @S"
          Dim dt As New DataTable

          Using con1 As OleDbConnection = New OleDbConnection(sCon)
              con1.Open()
              Using cmd1 As OleDbCommand = New OleDbCommand(str, con1)
                  With cmd1.Parameters
                      .Add("@S", OleDbType.VarChar).Value = TextBox1.Text
                  End With
                  Dim adp As New OleDbDataAdapter(cmd1)
                  adp.Fill(dt)
                  DataGridView1.DataSource = dt
              End Using
              con1.Close()
          End Using
      Catch ex As Exception
          MsgBox(ex.Message)
      End Try
  End Sub

推荐答案

尝试一下像这样：

Try something like this:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Status()
End Sub

Public Sub Status()
    ' TODO: Move this to the application's configuration file:
    Const sCon As String = "Provider= Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\db.accdb"
    
    Using con As OleDbConnection = New OleDbConnection(sCon)
        Using cmd As OleDbCommand = New OleDbCommand("", con)
            Dim str As String = "SELECT * FROM dbtable WHERE Status = @S"
            cmd.Parameters.Add("@S", OleDbType.VarChar).Value = TextBox1.Text
            
            If ComboBox1.Text = "Country" Then
                str = str & " AND Country = @Country"
                cmd.Parameters.Add("@Country", OleDbType.VarChar).Value = ComboBox2.Text
            End If
            
            cmd.CommandText = str
            
            Dim dt As New DataTable
            Dim adp As New OleDbDataAdapter(cmd)
            adp.Fill(dt)
            
            DataGridView1.DataSource = dt
        End Using
    End Using
End Sub

这篇关于如何使用VB.NET和ms access过滤datagridview？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！