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.