Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Runtime Binding
If you thought binding controls at design time was complicated, don't panic. Although you follow the same steps to bind controls at runtime, the process is much simpler now that you understand the objects involved. Not using all those wizards and dialog boxes also makes things a bit easier.
Start a new project, and add a DataGrid control to its form. Set its Dock property to Fill, as before. Then open the form's code designer. At the very top of the file, enter the following line to allow the program to easily refer to the OLE DB objects you will need.
Imports System.Data.OleDb
Now, enter the code shown in Listing 1. At a form-global level, the program declares an OleDbDataAdapter object, and creates a new DataSet.
The Load event handler allocates the OleDbDataAdapter, passing its constructor the SQL SELECT string it should use and a database connect string.
If you have trouble building the connect string, you can create a connection object at design time, as described in the first part of this article. Then, examine the object's ConnectionString property to see how it connects to the database. You can cut and paste this string into your code to make a similar connection at runtime. Often, you can greatly simplify this string by removing a bunch of unnecessary options.
After it creates the OleDbDataAdapter, the Load event handler calls the adapter's Fill method to copy data from the database into the DataSet. It finishes by setting the DataGrid control's DataSource property to the DataSet's first DataTable (that's where the data is).
When the Load event handler creates the program's data adapter, it passes the adapter's constructor a SQL SELECT statement, but it does not tell the adapter which SQL statements to use to insert, update, or delete records from the database. Unless it has those commands, the adapter cannot save changes back to the database. You could create those commands yourself, or you can use a command builder object to create those commands for you.
When you close the form, the Closing event handler makes an OleDbCommandBuilder object attached to the data adapter. The OleDbCommandBuilder creates the commands the data adapter needs to update the database. The Closing event handler then invokes the data adapter's Update method to save any changes.
Listing 1—This Code Binds Data to a DataGrid at Runtime
Private m_DataAdapter As OleDbDataAdapter
Private m_DataSet As New DataSet()
Private Sub Form1_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
' Make the data adapter.
m_DataAdapter = New OleDbDataAdapter( _
"SELECT * FROM Books ORDER BY Title", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\InformIT\New\BindControls\DesignTime\Books.mdb")
' Fill the DataSet.
m_DataAdapter.Fill(m_DataSet)
' Bind the DataGrid to the DataSet's first DataTable.
DataGrid1.DataSource = m_DataSet.Tables(0)
End Sub
Private Sub Form1_Closing(ByVal sender As Object, _
ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
' Make a command builder to generate INSERT,
' UPDATE, and DELETE commands as necessary.
Dim command_builder As New OleDbCommandBuilder(m_DataAdapter)
' Save any changes.
m_DataAdapter.Update(m_DataSet)
End Sub