通过数据网格视图搜索值

问题描述

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

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 的内容保持不变.我认为它可能与我的 SQL 查询的 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.

提前致谢.

推荐答案

通过一遍又一遍地创建 New DB 对象，您会遇到自己的问题.如果 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)"

这些只是声明，没有它们的实例(没有New).但是声明它们的位置决定了Scope.它们将一直存在，直到表单关闭(或者您用 Dim 和/或 New 覆盖它们).表单加载:

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 需要一个连接对象才能工作，但正如评论中提到的，而不是显式创建一个，适配器可以创建自己的.它将根据需要打开和关闭它.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 用作 DGV 的 DataSource:

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 中编辑数据，您只需将更改发送回数据库即可:

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 无法添加、插入、更新或删除时，则意味着您在某处创建了一个 New.不要那样做.同样，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.

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