时间戳

首页 > 解决方案 > VB.NET SQL 从表中搜索

sql-server - VB.NET SQL 从表中搜索

问题描述

我已经在我的 sql 数据库上创建了表：“tblInterni”，以便我可以在 datagridview 上看到它。我现在正在制作一个搜索功能，以便如果我搜索一个名称，它会在 datagridview 中加载具有该名称的每个人，但我所做的查询不起作用。

Private Sub Home_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim conn = New SqlConnection("Data Source=SRV-SQL;Initial Catalog=dbTest;User ID=pwdDb;Password=pwdDb")
    Dim adapter As New SqlDataAdapter("SELECT * FROM tblInterni", conn)
    Dim table As New DataTable()
    adapter.Fill(table)
    DataGridView1.DataSource = table

End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    Dim searchQuery As String = "SELECT * From tblInterni WHERE name like '%" & TextBox1.Text & "%'"
End Sub

形成图形

标签： sql-servervb.netsearch

解决方案

鉴于您在表单加载时检索所有数据，您应该做的是将您绑定DataTable到DataGridViewvia a ，BindingSource然后通过设置Filter.BindingSource

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using adapter As New SqlDataAdapter("SELECT * FROM MyTable", "connection string here")
        Dim table As New DataTable

        adapter.Fill(table)

        BindingSource1.DataSource = table
        DataGridView1.DataSource = BindingSource1
    End Using
End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    BindingSource1.Filter = $"MyColumn LIKE '%{TextBox1.Text}%'"
End Sub

请注意，BindingSource将添加到设计器中，就像网格一样。

但这仍然不理想。如果用户想要输入多个字符以进行过滤，那么此代码将不必要地修改过滤器几次，实际上会减慢它们的速度。一个更好的想法是使用 aTimer在过滤之前添加一个小的延迟，每次更改时都会重置。这样，如果他们足够快地键入几个字符，过滤器只会在最后一个字符之后发生变化。

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using adapter As New SqlDataAdapter("SELECT * FROM MyTable", "connection string here")
        Dim table As New DataTable

        adapter.Fill(table)

        BindingSource1.DataSource = table
        DataGridView1.DataSource = BindingSource1
    End Using
End Sub

Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
    'Start/reset the filter timer.
    Timer1.Stop()
    Timer1.Start()
End Sub

Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
    BindingSource1.Filter = $"MyColumn LIKE '%{TextBox1.Text}%'"
End Sub

您可以对 的 进行一些试验Interval，Timer但您应该发现大约 300 毫秒应该意味着过滤仍然感觉足够快，但以合理的速度输入应该避免大多数不必要的中间过滤器。

推荐阅读