Searching values via a datagridview

Question

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.

Pubescent

First, you are firing off a query everytime the user types a letter. If I type "h..a..s..a..n" it wont find a last name of "H" or "ha" etc until I am done. So move that first part into a Seartch button click. Second, the code in the first block uses an xml literal correctly (.Value) the second does not. That entire ShowItems method should not be needed using a datasource. Neither block uses the DataSet(s) that they declare. – Soundboard

if you would stop recreating DB objects over and over, you could use the row filter property: myStudentDataView.RowFilter = String.Format("StudentName = {0}", textbox1.Text) – Soundboard

@Plutonix firstly, which part are you referring to when you say first part. Secondly, where would i place the row filter. – Pubescent

First part == your first code block second part == your second code block. You should also turn on Option Strict. Even if the search did work, you are undoing what displays by calling ShowItems which runs new SQL. I dont think you can use RowFilter because you dont have a persistent DataTable – Soundboard

Answer 1

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.

Woodie

Will do. Greatly appreciated. – Pubescent

Great answer plutonix. I previously learnt to use Using as well from you. But just one question based on your guide above. If the database consists at least 2k rows of records, will the application start to lag when it is updating back the new changes to database? – Hydatid

@Hydatid In general 2k rows is very small. The dataadapter is smart - it is only going to update the new, changed or deleted rows; so the difference is many small changes sent, or one batch of several. – Soundboard

@Plutonix I see. So it is advisable to use dataadapter to update it instead of query a new update string right? Guess I will take my whole night changing it to your above example then :) – Hydatid

@Plutonix Also, since I have multiple table to query result from, there is no problem to use dataset instead of datatable right? – Hydatid

It can be better, simpler, less db traffic. It depends on the app. Part of the app that simply adds/edits base items could use it; another part that JOINs several tables together (e.g. Order, OrderItems, ServiceLineItem etc) could not use it. So, for multiple tables, it depends on how/if they are related; simple header-detail things can still work, rather than a DataSet I usually have 2 different classes handling each in a Transaction. If the base query is SELECT *... and the code reruns that query after deletes, changes, an adapter is likely more efficient. – Soundboard