Searching values via a 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.

here is the code:

  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

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

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.

Thanks in advance.

Solution

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)"

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

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.

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!

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

myDGV.DataSource = myDT

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.

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)

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

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).

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)

Searching is also simple:

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

To remove the filter:

myStudentsDataView = myDT.DefaultView

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.