DataAadapter Events in VB.NET

In this article I will explain working with DataAadapter Events in ADO.NET.
  • 2623
The data adapter has a FillError event that occurs during a fill method. It allows a user to determine whether a fill operation should continue. For example, FillError could occur when you can't convert data from data from a database to Common Language Runtime (CLR) type without losing precision or when data type casting is not valid.
FillErrorEventHandler handles the FillError event. The event handler receives an argument of type FillErrorEventArgs, which contains data related to this event. The FillErrorEventHandler is as follows:

Public Delegate Sub FillErrorEventHandler(ByVal senderAs Object,ByVal e As FillErrorEventArgs)

Where sender is the source of the event and e is FillErrorEventArgs, which contains the event data. Table 9-3 defines the FillErrorEventArgs properties.
Table 9-3. The FillErrorEventArgs members




Represent a value indicating whether to continue the fill operator


Return the data table being updated when the error occurred


Returns the errors


Returns the rows being updated when error occurred

RowUpdated and RowUpdating are two more events that a data adapter supports. The RowUpdating event occurs when an Update method is called before a command executed against the
data source. The RowUpdated event occurs when a command executed.

The RowUpdated event handler receives an argument of type OleDbRowUpdatedEventArgs that contains data related to this event. Table 9-4 describes the OleDbRowUpdateEventArgs members.

Table 9-4. The OleDbRowUpdatedEventArgs Members




Returns the command object executed when Update is called


Returns errors generated during the update operation


Returns the number of rows affected during insert, update, and delete operations


Returns the data row sent through an Update


Returns the SQL statement type


Returns the UpdateStatus of a command


Returns the DataTableMapping sent through an Update

Similar to RowUpdated, the RowUpdating event handler receives an argument of type OleDbRowupdatingEventArgs that defines the same properties as OleDbRowUpdated with the same meaning.
Ok, now it's time to write an application to test the data adapter events. I created a Windows application and added a button control to the form. After that I changed the button's name property to DataAdapterEventsTestBtn and wrote a button-click event handler by double-clicking on the button control.
Next, I added a data adapter control to the form by dragging the OleBbdAtaadapter component from the toolbox's data tab to the form. As you drop the OleDbDataAdapter component to the form, the data adapter configuration wizard pops up. In this application, I added the Customers table to the Query Builder and selected the CompanyName, ContactName, and CustomerID columns from the Customers table. As you can see from figure 9-8, the Query Builder shows the SQL statement.
Figure 9-8. The query builder with the SELECT statement from the Customers table
Next, I used the Properties > Event (the lightning bolt) button of the data adapter to add the event handlers. I added all three FillError, RowUpdating and RowUpdated event handlers, as shown in Figure 9-9.

Figure 9-9 AddingDataAdapter events from the Properties window
Now you can write code on the FillError event handler. You call the FillError event handler when the Fill method of data adapter of data adapter fails. Listing 9-3 shows the code for the FillError event handler. As you can see from listing 9-3, I used DataFillErrorEventArgs's members to get information about the error.
Listing 9-3. DataAdapter FillError event handler code

    'FillError Event Handler

    Private Sub oleDbDataAdapter1_FillError(ByVal sender As Object,ByVal e As System.Data.FillErrorEventArgs)
        If e.Errors.[GetType]()Is GetType(System.OverflowException) Then
            MessageBox.Show((("Error in Fill operation for table" & e.DataTable.TableName &", Error Message: ") + e.Errors.Message.ToString() & ", Source: ") + e.Errors.Source.ToString())
            e.[Continue] = True
        End If
   End Sub

Listing 9-4 and 9-5 show the code for the RowUpdated and RowUpdating event handlers. You call the RowUpdated event handler when a row is updated using the Update method of a data adapter, and you call RowUpdating when a row is updating.
Listing 9-4. The RowUpdated event handler

    ' RowUpdated event handler

    Private Sub oleDbDataAdapter1_RowUpdated(ByVal sender As Object,ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
        If e.Status = UpdateStatus.ErrorsOccurred Then
            MessageBox.Show(("Error Message: " & e.Errors.Message.ToString() & ", Source: ") + e.Errors.Source.ToString())
            e.Row.RowError = e.Errors.Message
            e.Status = UpdateStatus.SkipCurrentRow
        End If
   End Sub

As you can see from Listing 9-5, you can compare the StatementType member of OleDbRowUpdatingEventArgs to the StatementType enumeration to find out the statement type. The StatementType enumeration defines Select, Insert, Update, and Delete members.
Listing 9-5, The RowUpdating event handler

    Private Sub oleDbDataAdapter1_RowUpdating(ByVal sender As Object,ByVal e As System.Data.OleDb.OleDbRowUpdatingEventArgs)
        ' Inserting
        If e.StatementType = StatementType.Insert Then

            ' Updating
        ElseIf e.StatementType = StatementType.Update Then

            ' Deleting
        ElseIf e.StatementType = StatementType.Delete Then

        ElseIf e.StatementType = StatementType.[Select] Then
        End If
   End Sub

Now, to test these events you can write code that fills a dataset and calls the update method of data adapter. I added an event handler for the button by double- clicking on it and writing the code shown in listing 9-6. As you can see, I created a new OleDbDataAdapter object and copied oleDbDataAdapter1 to it (the framework added oleDbDataAdapter1).
Listing 9-6.Calling DataAdapter's Fill and update methods

    Private Sub DataAdapterEventsTestBtn_Click(ByVal sender As Object,ByVal e As System.EventArgs)
        Dim conn As New OleDbConnection()
        Dim strDSN As String ="Provider=Microsoft.Jet.OLEDB.4.0;" &"Data Source=C:\NorthWind.mdb"
        conn.ConnectionString = strDSN
        Dim da As New OleDbDataAdapter()
        da = oleDbDataAdapter1

        ' Creating InsertCommand
        da.InsertCommand = New OleDbCommand("INSERT INTO Customers(CustomerID,CompanyName)" & "VALUES(?, ?)", conn)
        da.InsertCommand.Parameters.Add("@CustomerID", OleDbType.VarChar, 5, "CustomerID")
        da.InsertCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 30, "CompanyName")

        ' Opening Connection

        ' Create and Fill DataSet
        Dim custDSAs New DataSet()
        da.Fill(custDS, "Customers")

        ' Add a new data row and call Update method
        ' of data adapter
        Dim custRowAs DataRow = custDS.Tables("Customers").NewRow()
        custRow("CustomerID") ="NEWCO"
        custRow("CompanyName") ="New Company"
        da.Update(custDS, "Customers")

        ' Close the Connection
   End Sub

Note: Don't forget to add a reference to the System.Data.OleDb namespace.
You can also use the SelectCommand, UpdateCommand, and DeleteCommand objects to select, update, and delete date from the table.
Now, if you run the application, you'll see an insert message; if you click the button more than once, you'll get the error shown in Figure 9-10.
Figure 9-10. The error message generated by the RowUpdated event handler when trying to add duplicate employee records
Hope this article would have helped you in understanding working with DataAadapter Events in ADO.NET. See other articles on the website also for further reference.


More Articles

© 2020 DotNetHeaven. All rights reserved.