You get your own path by creating New DB objects again and again. If the DataAdapter was a form level variable, 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)"
They are simply declared, they are not (no New ). But where they are declared, Scope defined. They will be located until the form is closed (or you overwrite them with Dim and / or New ). Form loading:
' 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 needs a connection object to work, but since the comment mentions, rather than explicitly creates it, the adapter can create its own. He will open and close it as necessary. The same is true for SelectCommand - it will create its own from the passed SELECT SQL statement.
Note that it is best to specify each column in the order in which you want the columns to appear in the DataTable . The important thing is that at the end of the DataAdapter knows how to delete, insert and update rows. As long as you do not destroy it or replace it, you do not have to write SQL to add or modify rows!
In most cases, a DataTable used as a DataSource for DGV:
myDGV.DataSource = myDT
DGV will create the necessary columns and display the data as rows. When the user types into the cells, these changes are reflected in the DataTable , so there is no need for any code to delete it.
In cases where the user edits the data in the DataGridView , this is all you need to send the changes back to the database:
myDa.Update(myDT)
In this case, based on previous questions, the data is taken from text controls, and not from 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 line in a form loading, so the actual database update (as soon as the data is in the DT) is one line of code: myDA.Update(myDT) .
DataTable keeps track of whether each row is new, changed, or even deleted, so myDA.Update(myDT) performs the appropriate actions for each of them. If the system is multi-user, you can receive changes by other users:
myDa.Fill(myDT)
The search is also simple:
Private Sub Search(txt As String) myStudentsDataView.RowFilter = String.Format("LastName = '{0}'", txt)
To remove a filter:
myStudentsDataView = myDT.DefaultView
If / if your DataAdapter cannot add, insert, update or delete, then you've created New somewhere. Do not do this. Similarly, myDataView will show everything in myDT until you create a new DT or DV or modify the RowFilter .