通过datagridview搜索值 [英] Searching values via a datagridview

问题描述

我尝试通过在文本框中输入文本，然后使用SQL查询数据库，然后在datagridview中显示结果来搜索数据库中的特定值。

i am try to search for a specific value in a database by entering text into a textbox and then using SQL to query the database and then display results in the datagridview.

这里是代码：

  Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtSearch.TextChanged
    Connection.Open()
    Dim dataTable As New DataTable
    Dim dataSet As New DataSet
    dataSet.Tables.Add(dataTable)
    Dim dataAdapter As New OleDbDataAdapter
    Dim SQLQuery As String
    SQLQuery = <sql> 
            SELECT * 
                 FROM Students
                 WHERE StudentFirstName = @StudentFirstName
                </sql> .Value

    dataAdapter = New OleDbDataAdapter(SQLQuery, Connection)
    dataAdapter.SelectCommand.Parameters.Add("@StudentFirstName", SqlDbType.Text).Value = txtStudentFirstname.Text
    dataAdapter.Fill(dataTable)
    dgrStudentDatabaseViewer.DataSource = dataTable.DefaultView
    ShowItems()
    Connection.Close()
End Sub

对ShowItems（）的调用刷新datagridview此处是代码为

the call to ShowItems() refreshes the datagridview here is the code for it

 Private Sub ShowItems() ' the following delcleration are used for displaying the contents of the table
    Dim dataAdapter As New OleDbDataAdapter
    Dim DataTable As New DataTable
    Dim DataSet As New DataSet
    Dim SQLQuery As String = <sql>SELECT * FROM Students</sql>
    DataSet.Tables.Add(DataTable)
    dataAdapter = New OleDbDataAdapter(SQLQuery, Connection)
    dataAdapter.Fill(DataTable) ' fills the content from the database into the table in vb net
    dgrStudentDatabaseViewer.DataSource = DataTable.DefaultView
    Connection.Close()
End Sub

目前，当我尝试搜索没有发生任何事情，datagridview的内容保持原样。我很瘦，可能与我的XML查询的XML文字有关，但不能弄清楚。

at the moment, when i attempt to search nothing happens and the contents of the datagridview remain as they always were. I thin it might have something to do with my XML literal of the SQL Query, but cant figure it out.

提前感谢。

推荐答案

您通过一次又一次地创建新的数据库对象来实现自己的方式。如果 DataAdapter 是一个表单级别的变量，那么您将不得不编写更少的代码：

You are getting in your own way by creating New DB Objects over and over. If the DataAdapter was a form level variables, you would have to write a lot less code:

Public Class Form1
    ' declare some persistant DB objects
    Private myDT As DataTable
    Private myDA As OleDbDataAdapter
    Private myStudentsDataView As DataView

    Private dbConnStr As String = "(your connection string)"

这些只是声明，没有（新）。但是它们被声明的决定了 Scope 。他们会一直在表单关闭之前（或者你用 Dim 和/或新覆盖它们）。表单加载：

These are just declared, there is no instance of them (no New). But where they are declared determines the Scope. They will be around until the form closes (or you overwrite them with Dim and/or New). Form load:

' initialize the objects
Dim sql = "SELECT A, B, C, D... FROM Students"

' this is the ONLY place you use NEW 
' with these objects
myDT = New DataTable()

' The Adapter can create its own Connection 
'     and SelectCommand
myDA = New OleDbDataAdapter(sql, dbConnStr)

Dim myCB As New OleDbCommandBuilder(da)

' "teach" the DA how to Update and Add:
myDA.UpdateCommand = myCB.GetUpdateCommand
myDA.InsertCommand = myCB.GetInsertCommand
myDA.DeleteCommand = myCB.GetDeleteCommand

myDA.Fill(myDT)
myDA.FillSchema(myDT, SchemaType.Source)

myStudentsDataView = myDT.DefaultView
dgvStudents.DataSource = myStudentsDataView

DataAdapter 需要连接对象才能正常工作，比明确创建一个，Adapter可以创建自己的。它将根据需要打开和关闭它。对于 SelectCommand 也是如此 - 它将通过传递的SELECT SQL语句创建自己的

The DataAdapter needs a connection object to work, but as the comment mentions rather than explicitly creating one, the Adapter can create its own. It will open and close it as it needs. The same is true for the SelectCommand - it will create its own from the SELECT SQL statement passed.

请注意最好按照您希望列出现在 DataTable 中的顺序指定每列。重要的是，最终 DataAdapter 知道如何删除，插入和更新行。只要你不破坏它或替换它，你不必写任何SQL添加或更改行！

Note that it is best to specify each column in the order you want the columns to appear in the DataTable. The important thing is that at the end that DataAdapter knows how to Delete, Insert and Update rows. As long as you dont destroy it or replace it, you wont have to write any SQL to Add or Change rows!

在大多数情况下， DataTable 用作 DataSource 为DGV：

In most cases, the DataTable is used as the DataSource for a DGV:

myDGV.DataSource = myDT 

DGV将创建所需的列，并将数据显示为行。当用户键入单元格时，这些更改将反映在 DataTable 中，因此无需任何代码即可将其删除。

The DGV will create the columns needed and show the data as rows. As the user types into the cells, those changes are reflected in the DataTable so there is no need for any code to fish it back out.

如果用户在 DataGridView 中编辑数据，则需要将更改发送回DB：

In cases where the user edits data in the DataGridView, this is all you need to send changes back to the DB:

myDa.Update(myDT)

在这种情况下，根据以前的问题，数据源自文本控件而不是DGV。所以：

In this case, based on previous questions, the data originates from text controls rather than the DGV. So:

Private Sub AddStudent()
    ' no need to (RE)create DataAdapter

    ' add the data to a new row:
    Dim dr = myDT.NewRow
    dr.Item("FirstName") = textbox1.text
    dr.Item("LastName") = textbox2.text
    ' etc etc

    ' add the new row to the datatable
    myDT.Rows.Add(dr)
   ' with a persistent DA, this is all you need to add a row:
   myDA.Update(myDT)
End Sub

我们教了 DataAdapter 如何在表单加载中更新一行，以便实际更新数据库（一旦数据在DT中）是一行代码： myDA.Update（myDT）。

We "taught" the DataAdapter how to update a row in form load so actually updating the database (once the data is in the DT) is one line of code: myDA.Update(myDT).

DataTable 跟踪每一行是否新增，更改或甚至删除，因此 myDA .Update（myDT）为每一个采取适当的操作。如果系统是多用户，您可以接收其他用户的更改：

The DataTable tracks whether each row is new, changed or even deleted, so myDA.Update(myDT) takes the appropriate action for each one. If the system is multiuser, you can pick up changes by other users:

myDa.Fill(myDT)

搜索也很简单：

Private Sub Search(txt As String)
    myStudentsDataView.RowFilter = String.Format("LastName = '{0}'", txt)

要删除过滤器：

myStudentsDataView = myDT.DefaultView

如果/当您的 DataAdapter 无法添加，插入，更新或删除它意味着您在某个地方创建了一个新的。不要这样做同样 myDataView 将显示 myDT 中的任何内容，直到创建新的DT或DV或更改 RowFilter 。

If/when your DataAdapter fails to add, insert, update or delete it means you created a New one somewhere. Dont do that. Likewise myDataView will show whatever is in myDT until you create a new DT or DV or change the RowFilter.

这篇关于通过datagridview搜索值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！