' DataSetAddModRemoveRows.vb.txt
Imports System
Imports System.Data
Imports db = System.Data.OleDb

Module Module1

    ' File: DataSetAddModRemoveRows.cs(.txt)
    ' (Re)written  By: Dan Garlen February 2004
    '  as part of a C# ADO.NET demonstration series
    '  Requires: ConsoleUtilityRoutines.cs & C:\TestData\Northwind.mdb

    Private strSQLchanges As String = "SELECT * FROM [Customers WHERE [Contact Name] 'ALFKI'"
    Private strSQL As String = "SELECT * FROM [Customers]"
    Private strMDB As String = "C:\TestData\Northwind.mdb"
    Private strDB As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data source =" + strMDB

    Public Sub Main()
        Title("ADO Lab")
        ChangeData()
        'AddData()
        'DeleteData()
        Footer()
    End Sub

    'Loop through DataSet and display field
    Sub ShowDataSet(ByVal dsArg As DataSet)
        Dim i As Integer
        For i = 0 To 10
            Dim dr As DataRow = dsArg.Tables("MyTableName").Rows(i)
            Dim strField As String = dr.ItemArray(1).ToString()
            WL("Item #1, Row[" + i.ToString() + "] = " + strField)
        Next
    End Sub


    Sub Title(ByVal strItem As String)
        Console.WriteLine("Starting: " + strItem + "...")
        WL("")
        AnyKey()
        WL("")
    End Sub

    Sub Footer()
        WL("")
        Console.WriteLine("Execution Complete.")
        WL("")
        AnyKey()
    End Sub

    Sub WL(ByVal strItem As String)
        Console.WriteLine(strItem)
    End Sub

    Sub RL()
        Console.ReadLine()
    End Sub

    Sub SuccessMsg(ByVal msg As String)
        Console.WriteLine("Success: [" + msg + "]")
        Console.ReadLine()
    End Sub

    Sub FailMsg(ByVal msg As String)
        Console.WriteLine("Failure: [" + msg + "]")
        Console.ReadLine()
    End Sub

    Public Sub AnyKey()
        WL("")
        Console.Write("Press enter key to continue...  ")
        RL()
    End Sub

    Public Enum CustomerFields As Integer
        ID = 0
        CompanyName = 1
        ContactName
    End Enum



    Private Sub ChangeData()
        Dim cn As db.OleDbConnection = New db.OleDbConnection(strDB)
        Dim da As db.OleDbDataAdapter = New db.OleDbDataAdapter(strSQL, cn)
        Dim cb As db.OleDbCommandBuilder = New db.OleDbCommandBuilder(da)
        Dim ds As DataSet = New System.Data.DataSet("myTable")

        cn.Open()
        da.Fill(ds, "myTable")

        Dim dr As DataRow
        Dim Records As Integer = ds.Tables("myTable").Rows.Count
        Dim i As Integer
        For i = 0 To Records - 1

            dr = ds.Tables("myTable").Rows(i)
            Dim IDBefore As String = dr.ItemArray(CustomerFields.ID).ToString
            Console.Write("ID = [" + IDBefore + "]   ")
            Dim CompanyBefore As String = dr.ItemArray(CustomerFields.CompanyName).ToString

            WL("Company = [" + CompanyBefore + "]")

            If (IDBefore = "ALFKI") Then
                dr = ds.Tables("myTable").Rows(i)
                dr.BeginEdit()
                Dim CompanyAfter As String = Now.ToLongTimeString
                dr(CustomerFields.CompanyName) = CompanyAfter
                dr.EndEdit()
                da.Update(ds, "myTable")
                Title("Changing: " + IDBefore)
                Dim NewCompany As String = dr.ItemArray(CustomerFields.CompanyName).ToString()
                WL("New CompanyName = [" + NewCompany + "]")
                Footer()
            End If

        Next

    End Sub


    Private Sub DeleteData()
        Dim ModID As String = "ABC06" '// must be 5 Chars
        Dim cn As db.OleDbConnection = New db.OleDbConnection(strDB)
        Dim da As db.OleDbDataAdapter = New db.OleDbDataAdapter(strSQL, cn)
        Dim cb As db.OleDbCommandBuilder = New db.OleDbCommandBuilder(da)
        Dim ds As DataSet = New System.Data.DataSet("myTable")

        cn.Open()
        da.Fill(ds, "myTable")

        Dim dr As DataRow
        Dim Success As Boolean = True
        Dim RecordFound As Boolean = False

        Dim Records As Integer = ds.Tables("myTable").Rows.Count
        Dim i As Integer
        For i = 0 To Records - 2
            dr = ds.Tables("myTable").Rows(i)
            Dim ID As String = dr.ItemArray(CustomerFields.ID).ToString()
            Console.Write("ID = [" + ID + "]   ")
            Dim Company As String = dr.ItemArray(CustomerFields.CompanyName).ToString()
            WL("Company = [" + Company + "]")
            If ModID = ID Then
                Try
                    dr.Delete()
                    da.Update(ds, "myTable")
                    SuccessMsg("Delete")
                    RecordFound = True
                Catch ex As Exception
                    Success = False
                    FailMsg("Delete")
                    WL("DB Error" + ex.Message.ToString())
                End Try


            End If

        Next
        If RecordFound = False Then
            FailMsg("Record Not Found.")
        End If
    End Sub

    Private Sub AddData()


        Dim NewID As String = "ABC07"          '// must be 5 Chars
        Dim NewCompanyName As String = "My Co6"
        Dim NewContactName As String = "Any Guy 6"
        Dim cn As db.OleDbConnection = New db.OleDbConnection(strDB)
        Dim da As db.OleDbDataAdapter = New db.OleDbDataAdapter(strSQL, cn)
        Dim cb As db.OleDbCommandBuilder = New db.OleDbCommandBuilder(da)
        Dim ds As DataSet = New System.Data.DataSet("myTable")

        cn.Open()
        da.Fill(ds, "myTable")
        Dim dr As DataRow
        Dim Success As Boolean = True
        Try

            dr = ds.Tables("myTable").NewRow
            ds.Tables("myTable").Rows.Add(dr)
            dr.BeginEdit()
            dr(CustomerFields.ID) = NewID
            dr(CustomerFields.CompanyName) = NewCompanyName
            dr(CustomerFields.ContactName) = NewContactName
            dr.EndEdit()
            da.Update(ds, "myTable")
            SuccessMsg("Add")
        Catch ex As Exception
            Success = False
            FailMsg("Add")
            WL("DB Error" + ex.Message.ToString())
        End Try

        Dim i As Integer
        Dim Records As Integer = ds.Tables("myTable").Rows.Count
        For i = 0 To Records - 1

            dr = ds.Tables("myTable").Rows(i)
            Dim ID As String = dr.ItemArray(CustomerFields.ID).ToString()

            Console.Write("ID = [" + ID + "]   ")

            Dim Company As String = dr.ItemArray(CustomerFields.CompanyName).ToString()
            WL("Company = [" + Company + "]")
            If ((NewID = ID) And Success) Then
                Title("The above item has been added.")
            End If

        Next
    End Sub

End Module