Creating Data Forms with the Data Form Wizard

There's an easy way to create a data-entry form in Visual Basic—just use the Data Form Wizard. I'll do that in the DataForm example on the CD-ROM, which creates a data-entry form for the authors table in the pubs database.

A data form is a new form added to your project, so to create such a form, DataForm1.vb, use the Project|Add New Item menu item, then select the Data Form Wizard icon in the Templates pane and click OK. This opens the Data Form Wizard you see in Figure 21.4.

Figure 21.4: The Data Form Wizard.

Click the Next button in the Data Form Wizard to move to the pane you see in Figure 21.5, where the Wizard is asking for the name of a dataset to create (or you can use an existing dataset); I'll name the new dataset dsDataSet1 here.

Figure 21.5: Creating a dataset.

In the next pane, the Wizard asks what data connection to use (or allows you to create a new connection), and I'll use a connection to the pubs database, as you see in Figure 21.6.

Figure 21.6: Specifying a data connection.

In the next pane, you can choose which table(s) to add to the data form, and I'll add the authors table, as you see in Figure 21.7.

Figure 21.7: Selecting a data table.

If you are working with multiple tables, you can create a master/detail relationship between the tables (see "Using Master/Detail Relationships and Data Relation Objects" in this chapter) in the next pane, as you see in Figure 21.8. I'll just click next to move on to the next pane here.

Figure 21.8: Selecting a data table to display.

In the next pane, you can select the display style—whether the data form will use a data grid or separate, simply bound controls. I'll specify separate controls here; as you can see in Figure 21.9, that means the data form also can contain Add, Delete, and other controls.

Figure 21.9: Selecting the display style.

Finally, click the Finish button to create the data form, DataForm1.vb, and add it to the project. What does this data form look like? You can see it in Figure 21.10, where I've clicked the Load button to load the authors table. In this case, I've added code to the main form to make the data form visible:

Private Sub Form1_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load
    Dim d As New DataForm1()
    d.Show()
End Sub

Figure 21.10: A data form at work.

In the data form, you can see all the data in the current record displayed (you can move the controls around in a data form if the default layout doesn't suit you, of course), as well as navigation buttons, and Add, Delete, Cancel, and Update buttons.

The Add, Delete, Cancel, and Update buttons let you edit the data in the dataset in the form, and send it back to the database. When the user changes the data in the bound controls, the changed data is sent back to the dataset immediately, starting an edit operation in the dataset—note that any changes to the dataset are only sent back to the database when the user clicks the Update button. The Add button adds a new empty record to the end of the dataset, the Delete button deletes a record, and the Cancel button cancels any edit operation in the dataset that hasn't been sent back to the database yet. I'll take a look at the data form code for these various operations here.

Main Page

Adding Records to a Dataset

Adding records to a dataset is easy, as you can see in the code the data form uses for this purpose—all you have to do is to use the BindingContext object's AddNew method:

Private Sub btnAdd_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnAdd.Click
    Try
        'Clear out the current edits
        Me.BindingContext(objdsDataSet1, "authors").EndCurrentEdit()
        Me.BindingContext(objdsDataSet1, "authors").AddNew()
    Catch eEndEdit As System.Exception
        System.Windows.Forms.MessageBox.Show(eEndEdit.Message)
    End Try
    Me.objdsDataSet1_PositionChanged()
End Sub

Note also the call to the objdsDataSet1_PositionChanged Sub procedure—all this procedure does is to update the "1 of 23" text at the bottom of the data form:

Private Sub objdsDataSet1_PositionChanged()
    Me.lblNavLocation.Text = _
        (((Me.BindingContext(objdsDataSet1, _
        "authors").Position + 1).ToString + " of ") __
        + Me.BindingContext(objdsDataSet1, _
        "authors").Count.ToString)
    End Sub

Main Page

Deleting Records from a Dataset

To delete a record from the dataset, all you have to do is to use the RemoveAt method of the binding context:

Private Sub btnDelete_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnDelete.Click
    If (Me.BindingContext(objdsDataSet1, "authors").Count > 0) Then
        Me.BindingContext(objdsDataSet1, _
            "authors").RemoveAt(Me.BindingContext(objdsDataSet1, _
             "authors").Position)
        Me.objdsDataSet1_PositionChanged()
    End If
End Sub

Main Page

Canceling a Dataset Edit

When you begin to change the data in the controls bound to a dataset, that in turn starts an edit operation in the dataset. The next time the Update button is clicked, the edited records will be sent to the database itself. If you want to cancel the current edit operation, you can click the Cancel button, which uses the CancelCurrentEdit method:

Private Sub btnCancel_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnCancel.Click
    Me.BindingContext(objdsDataSet1, "authors").CancelCurrentEdit()
    Me.objdsDataSet1_PositionChanged()
End Sub

Main Page

Updating the Underlying Data Store

Working with the dataset is fairly easy, but when it comes to sending the changes back to the database itself, things get more complex, as you can see by taking a look at the code for the Update button in the data form. This code starts by calling a Sub procedure named UpdateDataSet:

Private Sub btnUpdate_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnUpdate.Click
    Try
        Me.UpdateDataSet()
    Catch eUpdate As System.Exception
        System.Windows.Forms.MessageBox.Show(eUpdate.Message)
    End Try
    Me.objdsDataSet1_PositionChanged()
End Sub

In the UpdateDataSet Sub procedure, we want to send the changes that have been made back to the database, so the code starts by creating a new dataset that holds only the changed records, using the current dataset's GetChanges method. Then it sends that new dataset to a Sub procedure called UpdateDataSource to update the data source. After the call to UpdateDataSource, the code calls the AcceptChanges method of the main dataset so the current edit operation ends and the changed records are no longer considered "changed" records, but simply normal records:

Public Sub UpdateDataSet()
    'Create a new dataset to hold the changes that have been made
    'to the main dataset.
    Dim objDataSetChanges As DataForm.dsDataSet1 = _
        New DataForm.dsDataSet1()
    'Stop any current edits.
    Me.BindingContext(objdsDataSet1, "authors").EndCurrentEdit()
    'Get the changes that have been made to the main dataset.
    objDataSetChanges = CType(objdsDataSet1.GetChanges, _
        DataForm.dsDataSet1)
    'Check to see if any changes have been made.
    If (Not (objDataSetChanges) Is Nothing) Then
        Try
        'There are changes that need to be made, so attempt
        'to update the datasource by
        'calling the update method and passing the dataset and
        ' any parameters.
            Me.UpdateDataSource(objDataSetChanges)
            objdsDataSet1.Merge(objDataSetChanges)
            objdsDataSet1.AcceptChanges()
        Catch eUpdate As System.Exception
            'Add your error handling code here.
            Throw eUpdate
        End Try
        'Add your code to check the returned dataset
        'for any errors that may have been
        'pushed into the row object's error.
    End If
End Sub

Tip

Note also the use of the Merge method in the code above; when you send the dataset of changes to the data provider, the data provider may make changes in that dataset (such as updating fields that hold calculated values, or adding primary keys) and return a new dataset, which this code then merges into the main dataset in the program, using the dataset's Merge method, before calling the AcceptChanges method.

To actually pass the changes back to the database, the code uses the Update data adapter method. Here's how that works in the UpdateDataSource Sub procedure:

Public Sub UpdateDataSource(ByVal ChangedRows As DataForm.dsDataSet1)
    Try
        'The data source only needs to be updated if there are
        'changes pending.
        If (Not (ChangedRows) Is Nothing) Then
            'Open the connection.
            Me.OleDbConnection1.Open()
            'Attempt to update the data source.
            OleDbDataAdapter1.Update(ChangedRows)
        End If
    Catch updateException As System.Exception
        'Add your error handling code here.
        Throw updateException
    Finally
        'Close the connection whether or not the exception was thrown.
        Me.OleDbConnection1.Close()
    End Try
End Sub

And that's it—now the user can edit data and update the database with it, creating a true data-entry form.