Since the DataSet is created at run time, you cannot bind the form’s controls at design time. But you can bind the fields from the DataSet to form controls after you have retrieved the DataSet.
The best way to bind form controls is to use the BindingSource object. You declare and instantiate a new BindingSource object in code and set its Data- Source and DataMember properties. You can then bind form controls to the BindingSource object.
' Set up the binding source.
Dim ABindingSource As New BindingSource With ABindingSource
.DataSource = APubsDataSet .DataMember = "employee"
.Sort = "emp_id" ' Optionally sort the data. End With
Note : It isn’t necessary to sort the data using the BindingSource if your
SELECT statement includes an ORDER BY clause.
Binding to a DataGridView
To bind a DataGridView to a DataSet that you instantiate in code, you can set the grid’s DataSource to the BindingSource. After you set up the binding source, as shown in the preceding example, set the grid’s DataSource property to the new BindingSource object.
' Bind the grid.
EmployeeDataGridView.DataSource = ABindingSource
You can use a handy technique to set the properties of the grid, such as selecting and ordering the columns and modifying the column headings. In the Form Designer, drag the grid from the Data Sources window, which sets up all of the components for data binding. Use the smart tag and Properties window to set up the grid columns as you want them. Then you can delete the visual components from the component tray and write code to fill the grid (or you can leave them there but just not use them, but bind to the data-tier DataSet in code).
One word of caution: If you delete the DataSet and BindingSource compo- nents, you won’t be able to make modifications to the DataGridView columns in the designer.
Binding Table Data to a List Box or Combo Box
For a list box or combo box, you can set the control’s DataSource to the Bind- ingSource object and set the DisplayMember to one of the table fields. Notice in the following code that you must specify the field name of an existing field in quotes.
' Bind a combo box. With Emp_idComboBox
.DataSource = ABindingSource .DisplayMember = "emp_id"
.DataBindings.Add("text", ABindingSource, "emp_id", _ False, DataSourceUpdateMode.Never)
End With
To bind labels, text boxes, the Text property of a combo box, and the date- time picker, you must use the DataBindings.Add method in code to bind to a field from a DataSet.
The DataBindings.Add Method—General Form
Object.DataBindings.Add("property", DataSource, "FieldName")
General
Form
Overloaded format; to format the data field:
Object.DataBindings.Add("property", DataSource, "FieldName", True, DataSourceUpdateMode.Never, Nothing, "FormatString")
General
Form
Note that the property and field name must be enclosed in quotes and the data source is not. Also, be aware that the property name must be lowercase; the field name is not case-sensitive.
Use the overloaded format if you need to format the data. You can use the same codes that you use in the ToString method for formatting, such as "C" for currency and "d" for a short date. The True argument specifies that format- ting is enabled; the DataSourceUpdateMode argument specifies that each time a new value is assigned, either from a database field or user input, the format- ting occurs. The Nothing argument specifies that if the field is empty, it should be allowed to have a value of Nothing. And the FormatString is the format- ting code. See Help for a complete list of codes.
The DataBindings.Add Method—Example
Example
LastNameTextBox.DataBindings.Add("text", ABindingSource, "lname")
HireDateTextBox.DataBindings.Add("text", EmployeeBindingSource, "hire_date", True, DataSourceUpdateMode.Never, Nothing, "d")
Here is the complete Form_Load event handler that instantiates the data tier, retrieves the DataSet, sets up the BindingSource, and binds to the form fields.
Private Sub EmployeeForm_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load
' Retrieve the data and bind the form’s controls. Try
' Create an instance of the data-tier component. Dim EmployeeData As New PubsEmployeeData
' Retrieve the dataset from the data tier. Dim APubsDataSet As PubsDataSet
APubsDataSet = EmployeeData.GetEmployeeDataset() ' Set up the binding source.
Dim ABindingSource As New BindingSource With ABindingSource
.DataSource = APubsDataSet .DataMember = "employee" .Sort = "emp_id"
End With
' Fill the combo box. With Emp_idComboBox
.DataSource = ABindingSource .DisplayMember = "emp_id"
.DataBindings.Add("text", ABindingSource, "emp_id", _ False, DataSourceUpdateMode.Never)
End With
' Bind the other controls.
FullNameTextBox.DataBindings.Add("text", _ ABindingSource, "FullName") Hire_dateDateTimePicker.DataBindings.Add("text", _ ABindingSource, "hire_date") Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub
Binding Data from a Stored Procedure
The previous examples of data binding illustrated using fields from a table. If you are using a stored procedure, you must specify the name of the stored pro- cedure in place of the table name. Here is the binding from the previous code example, but using a GetEmployeeData stored procedure instead of the em- ployee table.
' Set up the binding source.
Dim ABindingSource As New BindingSource With ABindingSource
.DataSource = APubsDataSet
.DataMember = "GetEmployeeData" ' Stored procedure name. End With
Notice that the Sort property of the BindingSource is not set in this exam- ple because the GetEmployeeData stored procedure includes an ORDER BY clause that sorts the data.