Imports System.Data.SqlClient

Public Class Tester

    Public Sub OnRowUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)

        ' Check if any records were affected.
        ' If no records were affected, the statement did not execute as expected.
        If e.RecordsAffected() < 1 Then
            ' Find out the type of failed error.
            Select Case e.StatementType
                Case StatementType.Delete
                    lblResult.Text &= "Not deleted (ProductName: "
                Case StatementType.Insert
                    lblResult.Text &= "Not inserted (ProductName: "
                Case StatementType.Update
                    lblResult.Text &= "Not updated (ProductName: "
            End Select

            lblResult.Text &= e.Row("ProductName") & ")" & vbNewLine
            lblResult.Text &= "  It was already changed by another user."
            lblResult.Text &= vbNewLine

            ' Tell ADO.NET
            ' to ignore the problem and keep updating the other rows. 
            e.Status = UpdateStatus.SkipCurrentRow
        End If

    End Sub


    Private Sub cmdTest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTest.Click

        lblResult.Text = ""

        ' Define ADO.NET objects.
        Dim SQL As String = "SELECT * FROM Products"
        Dim Connect As String = "Data Source=localhost;Integrated Security=True;" & _
         "Initial Catalog=Northwind;"

        Dim con As New SqlConnection(Connect)
        Dim cmd As New SqlCommand(SQL, con)
        Dim adapter As New SqlDataAdapter(cmd)

        ' Create the CommandBuilder.
        Dim cb As New SqlCommandBuilder(adapter)
        ' Retrieve an updated DataAdapter.
        adapter = cb.DataAdapter

        ' Connect the event handler.
        AddHandler adapter.RowUpdated, AddressOf OnRowUpdated

        ' Create two DataSets... perfect for conflicting data.
        Dim dsNorthwind1 As New DataSet(), dsNorthwind2 As New DataSet()

        Try
            con.Open()

            ' Fill both DataSets.
            adapter.Fill(dsNorthwind1, "Products")
            adapter.Fill(dsNorthwind2, "Products")

            ' "Flip" the Discontinued field in the first row of dsNorthwind1.
            dsNorthwind1.Tables(0).Rows(0).Item("Discontinued") = Not dsNorthwind1.Tables(0).Rows(0).Item("Discontinued")

            ' Update the database
            adapter.Update(dsNorthwind1, "Products")

            ' Make a change in the second DataSet.
            dsNorthwind2.Tables(0).Rows(0).Item("ProductName") = "Jumbo Sea Monkeys"

            ' Try to update this row. Even though these changes don't conflict,
            ' the update will fail because the row has been changed.
            adapter.Update(dsNorthwind2, "Products")

        Catch err As Exception
            lblResult.Text &= err.Message
        Finally
            con.Close()
        End Try



    End Sub

End Class