
'Created By Database Hub, Inc.
'
'Date - 2004-12-07
'
'Email - mike@databasehub.com
'
'The Original Document Was Published At: 
'      http://www.dotnet247.com/247reference/a.aspx?u=http://www.codeproject.com/csharp/PracticalGuideDataGrids1.asp
'
'Note To Reader - This is a learning project for me. It was created so I could learn how to 
'work with the DataGrid, DataSet and DataTable Objects with a SQLite3 Database And CoreLab MySQLite.NET 
'There May Be Better Ways Of Performing These Tasks; What Is Presented Here Is A 
'Proof Of Concept For My Personal Use.
'mike@databasehub.com
'
Imports System.Text
Imports System.Environment
Imports System.IO
Imports DataGrid_DataSet_DataTable.ExceptionHandler


Public Class frmMain
    Inherits System.Windows.Forms.Form
    Dim DTbl As DataTable 'Module Level



#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponenTMOdel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents cmdTableMemObj As System.Windows.Forms.Button
    Friend WithEvents cmdRemoveCol As System.Windows.Forms.Button
    Friend WithEvents cmdCreateAddRows1 As System.Windows.Forms.Button
    Friend WithEvents cmdCreateAddRows2 As System.Windows.Forms.Button
    Friend WithEvents cmdChangeColumnVal As System.Windows.Forms.Button
    Friend WithEvents cmdLoadDataRow As System.Windows.Forms.Button
    Friend WithEvents cmdCreateTablePK As System.Windows.Forms.Button
    Friend WithEvents cmdDataTableDocs As System.Windows.Forms.Button
    Friend WithEvents cmdShowRowStates As System.Windows.Forms.Button
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents cmdDataSetDocs As System.Windows.Forms.Button
    Friend WithEvents AddTableToDS As System.Windows.Forms.Button
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents cmdDSFromSQL As System.Windows.Forms.Button
    Friend WithEvents cmdCreateDTFromSQL As System.Windows.Forms.Button
    Friend WithEvents cmdDataDrids As System.Windows.Forms.Button
    Friend WithEvents lblStatus As System.Windows.Forms.Label
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Dim resources As System.Resources.ResourceManager = New System.Resources.ResourceManager(GetType(frmMain))
        Me.Label1 = New System.Windows.Forms.Label
        Me.cmdTableMemObj = New System.Windows.Forms.Button
        Me.cmdRemoveCol = New System.Windows.Forms.Button
        Me.cmdCreateAddRows1 = New System.Windows.Forms.Button
        Me.cmdCreateAddRows2 = New System.Windows.Forms.Button
        Me.cmdChangeColumnVal = New System.Windows.Forms.Button
        Me.cmdLoadDataRow = New System.Windows.Forms.Button
        Me.cmdCreateTablePK = New System.Windows.Forms.Button
        Me.cmdDataTableDocs = New System.Windows.Forms.Button
        Me.cmdShowRowStates = New System.Windows.Forms.Button
        Me.Label2 = New System.Windows.Forms.Label
        Me.cmdDataSetDocs = New System.Windows.Forms.Button
        Me.AddTableToDS = New System.Windows.Forms.Button
        Me.Label3 = New System.Windows.Forms.Label
        Me.cmdDSFromSQL = New System.Windows.Forms.Button
        Me.cmdCreateDTFromSQL = New System.Windows.Forms.Button
        Me.cmdDataDrids = New System.Windows.Forms.Button
        Me.lblStatus = New System.Windows.Forms.Label
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label1.Location = New System.Drawing.Point(24, 20)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(432, 32)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "A Practical Guide to .NET DataTables, DataSets and DataGrids In VB.NET Code Inspi" & _
        "red By An Article On 'The Code Project' Site Written By Pete2004"
        Me.Label1.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
        '
        'cmdTableMemObj
        '
        Me.cmdTableMemObj.Location = New System.Drawing.Point(24, 88)
        Me.cmdTableMemObj.Name = "cmdTableMemObj"
        Me.cmdTableMemObj.Size = New System.Drawing.Size(164, 20)
        Me.cmdTableMemObj.TabIndex = 1
        Me.cmdTableMemObj.Text = "Create Table No PK"
        '
        'cmdRemoveCol
        '
        Me.cmdRemoveCol.Location = New System.Drawing.Point(24, 136)
        Me.cmdRemoveCol.Name = "cmdRemoveCol"
        Me.cmdRemoveCol.Size = New System.Drawing.Size(164, 20)
        Me.cmdRemoveCol.TabIndex = 2
        Me.cmdRemoveCol.Text = "Remove A Table Column"
        '
        'cmdCreateAddRows1
        '
        Me.cmdCreateAddRows1.Location = New System.Drawing.Point(24, 160)
        Me.cmdCreateAddRows1.Name = "cmdCreateAddRows1"
        Me.cmdCreateAddRows1.Size = New System.Drawing.Size(164, 20)
        Me.cmdCreateAddRows1.TabIndex = 3
        Me.cmdCreateAddRows1.Text = "Create And Add Rows 1"
        '
        'cmdCreateAddRows2
        '
        Me.cmdCreateAddRows2.Location = New System.Drawing.Point(24, 184)
        Me.cmdCreateAddRows2.Name = "cmdCreateAddRows2"
        Me.cmdCreateAddRows2.Size = New System.Drawing.Size(164, 20)
        Me.cmdCreateAddRows2.TabIndex = 4
        Me.cmdCreateAddRows2.Text = "Create And Add Rows 2"
        '
        'cmdChangeColumnVal
        '
        Me.cmdChangeColumnVal.Location = New System.Drawing.Point(24, 208)
        Me.cmdChangeColumnVal.Name = "cmdChangeColumnVal"
        Me.cmdChangeColumnVal.Size = New System.Drawing.Size(164, 20)
        Me.cmdChangeColumnVal.TabIndex = 5
        Me.cmdChangeColumnVal.Text = "Change A Columns Data"
        '
        'cmdLoadDataRow
        '
        Me.cmdLoadDataRow.Location = New System.Drawing.Point(24, 232)
        Me.cmdLoadDataRow.Name = "cmdLoadDataRow"
        Me.cmdLoadDataRow.Size = New System.Drawing.Size(164, 20)
        Me.cmdLoadDataRow.TabIndex = 6
        Me.cmdLoadDataRow.Text = "LoadDataRow Method"
        '
        'cmdCreateTablePK
        '
        Me.cmdCreateTablePK.Location = New System.Drawing.Point(24, 112)
        Me.cmdCreateTablePK.Name = "cmdCreateTablePK"
        Me.cmdCreateTablePK.Size = New System.Drawing.Size(164, 20)
        Me.cmdCreateTablePK.TabIndex = 7
        Me.cmdCreateTablePK.Text = "Create Table With PK"
        '
        'cmdDataTableDocs
        '
        Me.cmdDataTableDocs.Location = New System.Drawing.Point(56, 256)
        Me.cmdDataTableDocs.Name = "cmdDataTableDocs"
        Me.cmdDataTableDocs.Size = New System.Drawing.Size(100, 20)
        Me.cmdDataTableDocs.TabIndex = 8
        Me.cmdDataTableDocs.Text = "Data Table Docs"
        '
        'cmdShowRowStates
        '
        Me.cmdShowRowStates.Location = New System.Drawing.Point(24, 280)
        Me.cmdShowRowStates.Name = "cmdShowRowStates"
        Me.cmdShowRowStates.Size = New System.Drawing.Size(164, 20)
        Me.cmdShowRowStates.TabIndex = 9
        Me.cmdShowRowStates.Text = "Show Row States"
        '
        'Label2
        '
        Me.Label2.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label2.Location = New System.Drawing.Point(76, 64)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(68, 16)
        Me.Label2.TabIndex = 10
        Me.Label2.Text = "Data Tables"
        '
        'cmdDataSetDocs
        '
        Me.cmdDataSetDocs.Location = New System.Drawing.Point(292, 88)
        Me.cmdDataSetDocs.Name = "cmdDataSetDocs"
        Me.cmdDataSetDocs.Size = New System.Drawing.Size(100, 20)
        Me.cmdDataSetDocs.TabIndex = 11
        Me.cmdDataSetDocs.Text = "Data Sets Docs"
        '
        'AddTableToDS
        '
        Me.AddTableToDS.Location = New System.Drawing.Point(256, 112)
        Me.AddTableToDS.Name = "AddTableToDS"
        Me.AddTableToDS.Size = New System.Drawing.Size(172, 20)
        Me.AddTableToDS.TabIndex = 12
        Me.AddTableToDS.Text = "Add Table To Data Set"
        '
        'Label3
        '
        Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label3.Location = New System.Drawing.Point(304, 64)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(68, 16)
        Me.Label3.TabIndex = 13
        Me.Label3.Text = "Data Sets"
        '
        'cmdDSFromSQL
        '
        Me.cmdDSFromSQL.Location = New System.Drawing.Point(256, 136)
        Me.cmdDSFromSQL.Name = "cmdDSFromSQL"
        Me.cmdDSFromSQL.Size = New System.Drawing.Size(172, 20)
        Me.cmdDSFromSQL.TabIndex = 14
        Me.cmdDSFromSQL.Text = "Create Data Set With SQL"
        '
        'cmdCreateDTFromSQL
        '
        Me.cmdCreateDTFromSQL.Location = New System.Drawing.Point(24, 304)
        Me.cmdCreateDTFromSQL.Name = "cmdCreateDTFromSQL"
        Me.cmdCreateDTFromSQL.Size = New System.Drawing.Size(172, 20)
        Me.cmdCreateDTFromSQL.TabIndex = 19
        Me.cmdCreateDTFromSQL.Text = "Create Data Table With SQL"
        '
        'cmdDataDrids
        '
        Me.cmdDataDrids.Font = New System.Drawing.Font("Microsoft Sans Serif", 8.25!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cmdDataDrids.Location = New System.Drawing.Point(280, 240)
        Me.cmdDataDrids.Name = "cmdDataDrids"
        Me.cmdDataDrids.Size = New System.Drawing.Size(132, 20)
        Me.cmdDataDrids.TabIndex = 20
        Me.cmdDataDrids.Text = "Data Grids"
        '
        'lblStatus
        '
        Me.lblStatus.Location = New System.Drawing.Point(24, 348)
        Me.lblStatus.Name = "lblStatus"
        Me.lblStatus.Size = New System.Drawing.Size(416, 23)
        Me.lblStatus.TabIndex = 21
        '
        'frmMain
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(464, 381)
        Me.Controls.Add(Me.lblStatus)
        Me.Controls.Add(Me.cmdDataDrids)
        Me.Controls.Add(Me.cmdCreateDTFromSQL)
        Me.Controls.Add(Me.cmdDSFromSQL)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.AddTableToDS)
        Me.Controls.Add(Me.cmdDataSetDocs)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.cmdShowRowStates)
        Me.Controls.Add(Me.cmdDataTableDocs)
        Me.Controls.Add(Me.cmdCreateTablePK)
        Me.Controls.Add(Me.cmdLoadDataRow)
        Me.Controls.Add(Me.cmdChangeColumnVal)
        Me.Controls.Add(Me.cmdCreateAddRows2)
        Me.Controls.Add(Me.cmdCreateAddRows1)
        Me.Controls.Add(Me.cmdRemoveCol)
        Me.Controls.Add(Me.cmdTableMemObj)
        Me.Controls.Add(Me.Label1)
        Me.Icon = CType(resources.GetObject("$this.Icon"), System.Drawing.Icon)
        Me.Name = "frmMain"
        Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen
        Me.Text = "VB.NET DataTables, DataSets, DataGrids And SQLite3"
        Me.ResumeLayout(False)

    End Sub

#End Region

    Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        AddForm()
        frmMainRef = Me
    End Sub

    Private Sub cmdTablesMemObj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdTableMemObj.Click
        Try
            'Start With New Table
            If IsNothing(DTbl) = True Then
                'Do Nothing
            Else
                DTbl.Dispose()
            End If

            DTbl = CreateTableMemoryObjectNoPK()

            DisplayObjectsUsingForLoop()

            DisplayObjectsUsingColumnNames()

        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub DisplayObjectsUsingForLoop()

        Try
            Dim ColInfo As New StringBuilder
            ColInfo.AppendFormat("Column Number" & vbTab & "Column" & vbTab & vbTab & "Column Header" & vbTab & "DataType" & NewLine)
            Dim j As Integer
            'Display The Table Objects With For Loop - 0 Thru 7 
            For j = 0 To DTbl.Columns.Count - 1
                'Apply Formatting Depending On Column Type - Not Necessary Here But Shows How
                If DTbl.ReferenceEquals(DTbl.Columns(j).DataType(), System.Type.GetType("System.Int64")) Then
                    '                 Column Number              Column               Column Header               DataType       Nbr           Column                Column Header                   DataType                        
                    ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, j, DTbl.Columns(j).ColumnName, DTbl.Columns(j).Caption, DTbl.Columns(j).DataType().ToString())
                ElseIf DTbl.ReferenceEquals(DTbl.Columns(j).DataType(), System.Type.GetType("System.Int32")) Then
                    ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, j, DTbl.Columns(j).ColumnName, DTbl.Columns(j).Caption, DTbl.Columns(j).DataType().ToString())
                Else : DTbl.ReferenceEquals(DTbl.Columns(j).DataType(), System.Type.GetType("System.String"))
                    ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, j, DTbl.Columns(j).ColumnName, DTbl.Columns(j).Caption, DTbl.Columns(j).DataType().ToString())
                End If
            Next
            MessageBox.Show(ColInfo.ToString(), "Column Info Using For Loop", MessageBoxButtons.OK, MessageBoxIcon.Information)

        Catch 'Any Exception
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Displaying The Objects Using A For Loop!", _
                "The Application Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException

        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub DisplayObjectsUsingColumnNames()
        Try
            'Display The Table Objects Using Column Names
            Dim ColInfo As New StringBuilder
            ColInfo.AppendFormat("Column Number" & vbTab & "Column" & vbTab & vbTab & "Column Header" & vbTab & "DataType" & NewLine)
            Dim j As Integer
            ColInfo = New StringBuilder
            ColInfo.AppendFormat("Column Number" & vbTab & "Column" & vbTab & vbTab & "Column Header" & vbTab & "DataType" & NewLine)
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("ID").Ordinal, DTbl.Columns("ID").ColumnName, DTbl.Columns("ID").Caption, DTbl.Columns("ID").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("clecID").Ordinal, DTbl.Columns("clecID").ColumnName, DTbl.Columns("clecID").Caption, DTbl.Columns("clecID").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("ilecID").Ordinal, DTbl.Columns("ilecID").ColumnName, DTbl.Columns("ilecID").Caption, DTbl.Columns("ilecID").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("Resale").Ordinal, DTbl.Columns("Resale").ColumnName, DTbl.Columns("Resale").Caption, DTbl.Columns("Resale").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("UNEP").Ordinal, DTbl.Columns("UNEP").ColumnName, DTbl.Columns("UNEP").Caption, DTbl.Columns("UNEP").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("TaskOrder").Ordinal, DTbl.Columns("TaskOrder").ColumnName, DTbl.Columns("TaskOrder").Caption, DTbl.Columns("TaskOrder").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("Task").Ordinal, DTbl.Columns("Task").ColumnName, DTbl.Columns("Task").Caption, DTbl.Columns("Task").DataType.ToString())
            ColInfo.AppendFormat("{0}" & vbTab & vbTab & "{1}" & vbTab & vbTab & "{2}" & vbTab & vbTab & "{3}" & NewLine, DTbl.Columns("Descr").Ordinal, DTbl.Columns("Descr").ColumnName, DTbl.Columns("Descr").Caption, DTbl.Columns("Descr").DataType.ToString())

            MessageBox.Show(ColInfo.ToString(), "Column Info Using Column Names", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch 'Any Exception
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Displaying The Objects Using Column Names!", _
                "The Application Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException

        Finally
            'No Finally Needed
        End Try

    End Sub
    Private Sub cmdRemoveCol_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemoveCol.Click
        Try
            'Start With New Table
            If IsNothing(DTbl) = True Then
                DTbl = CreateTableMemoryObjectNoPK()
            Else
                DTbl.Dispose()
                DTbl = CreateTableMemoryObjectNoPK()
            End If

            If DTbl.Columns.Contains("Descr") Then
                If DTbl.Columns.CanRemove(DTbl.Columns("Descr")) Then
                    DTbl.Columns.Remove("Descr")
                End If
            End If

            DisplayObjectsUsingForLoop()

            'Now Recreate The Table Obj Again
            DTbl.Dispose()
            DTbl = CreateTableMemoryObjectNoPK()

            'DisplayObjectsUsingForLoop()
        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdCreateAddRows1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateAddRows1.Click
        Try
            'Use Column Names Directly
            Dim DRow As DataRow

            'Start With New Table
            If IsNothing(DTbl) = True Then
                DTbl = CreateTableMemoryObjectNoPK()
            Else
                DTbl.Dispose()
                DTbl = CreateTableMemoryObjectNoPK()
            End If

            DRow = DTbl.NewRow()

            DRow("ID") = 1
            DRow("clecID") = 1
            DRow("ilecID") = 1
            DRow("Resale") = 1
            DRow("UNEP") = 0
            DRow("TaskOrder") = 1
            DRow("Task") = "AddBSDABResaleUSOCInfo"
            DRow("Descr") = "Add All USOCs"
            DTbl.Rows.Add(DRow) 'Add To Data Table

            DTbl.AcceptChanges()

            DisplayRowData()
        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try

    End Sub


    Private Sub cmdCreateAddRows2_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateAddRows2.Click
        Try
            Add2Rows()
        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try


    End Sub

    Private Sub Add2Rows()
        Try
            'Load Data From Array That Contains The Values
            Dim DRow As DataRow
            Dim Arry(2, 7) As String 'Contains Two Rows

            'Start With New Table
            If IsNothing(DTbl) = True Then
                DTbl = CreateTableMemoryObjectNoPK()
            Else
                DTbl.Dispose()
                DTbl = CreateTableMemoryObjectNoPK()
            End If

            'Add Values To Array
            Arry(0, 0) = 1  'ID
            Arry(0, 1) = 1  'clecID
            Arry(0, 2) = 1  'ilecID
            Arry(0, 3) = 1  'Resale
            Arry(0, 4) = 0  'UNEP
            Arry(0, 5) = 1  'TaskOrder
            Arry(0, 6) = "AddBSDABResaleUSOCInfo"
            Arry(0, 7) = "Add All USOCs"

            Arry(1, 0) = 2
            Arry(1, 1) = 1
            Arry(1, 2) = 1
            Arry(1, 3) = 1
            Arry(1, 4) = 0
            Arry(1, 5) = 2  'TaskOrder
            Arry(1, 6) = "AddBSDABResaleOCCInfo"
            Arry(1, 7) = "Add All Other Charges And Credits"


            Dim DCol As DataColumn
            Dim RowCtr As Integer
            Dim ColCtr As Integer

            For RowCtr = 0 To Arry.GetUpperBound(0) - 1
                ColCtr = 0
                DRow = DTbl.NewRow()
                For Each DCol In DTbl.Columns
                    If DCol.DataType() Is System.Type.GetType("System.String") Then
                        DRow(DCol) = Arry(RowCtr, ColCtr).ToString
                    ElseIf DCol.DataType() Is System.Type.GetType("System.Int64") Then
                        DRow(DCol) = CLng(Arry(RowCtr, ColCtr))
                    ElseIf DCol.DataType() Is System.Type.GetType("System.Int32") Then
                        DRow(DCol) = CInt(Arry(RowCtr, ColCtr))
                    End If
                    ColCtr += 1
                Next
                DTbl.Rows.Add(DRow)
            Next

            DTbl.AcceptChanges()

            DisplayRowData()
        Catch 'Any Exception
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Adding Two Rows To The Data Table!", _
                "The Application Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException

        Finally
            'No Finally Needed
        End Try


    End Sub
    Private Sub DisplayRowData()
        Dim DCol As DataColumn
        Dim DRow As DataRow
        Dim ThisRow As New StringBuilder

        Try
            For Each DRow In DTbl.Rows
                For Each DCol In DTbl.Columns
                    ThisRow.Append(CStr(DRow(DCol)) & " ~ ")
                Next
                MsgBox("This Row Contains - " & ThisRow.ToString)
                ThisRow.Length = 0 'Clear Current Contents
            Next
        Catch 'Any Exception
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Displaying The Row Data!", _
                "The Application Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
            Throw New Exceptions.JCException

        Finally
            'No Finally Needed
        End Try
    End Sub

    Private Sub cmdChangeColumnVal_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdChangeColumnVal.Click
        Dim DRow As DataRow

        Try
            Add2Rows()

            'Increment The TaskOrder Fields
            Dim RowCtr As Integer
            For RowCtr = 0 To DTbl.Rows.Count - 1
                DTbl.Rows(RowCtr).Item("TaskOrder") += 1
            Next

            DTbl.AcceptChanges()

            'Another Way To Increment The TaskOrder Fields
            'For RowCtr = 0 To DTbl.Rows.Count - 1
            '    DRow = DTbl.Rows(RowCtr)
            '    DRow.Item("TaskOrder") += 1
            'Next
            'DTbl.AcceptChanges()


            DisplayRowData()
        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdLoadDataRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLoadDataRow.Click
        Dim ID As Integer = 3
        Dim clecID As Integer = 1
        Dim ilecID As Integer = 1
        Dim Resale As Integer = 3
        Dim UNEP As Integer = 0
        Dim TaskOrder As Integer = 4
        Dim Task As String = "AddBSDABResaleLocalUsage"
        Dim Descr As String = "Add Local Usage Charges"

        Try
            'Start With New Table
            If IsNothing(DTbl) = True Then
                DTbl = CreateTableMemoryObjectNoPK()
            Else
                DTbl.Dispose()
                DTbl = CreateTableMemoryObjectNoPK()
            End If

            'Add A New Row To The Table
            Dim DRow As DataRow
            DTbl.BeginLoadData()
            DRow = DTbl.LoadDataRow(New Object() {ID, clecID, ilecID, Resale, UNEP, TaskOrder, Task, Descr}, False) 'Does Not Commit
            DTbl.EndLoadData()

            DisplayRowData()
            DTbl.AcceptChanges() 'Changes Have To Be Committed Before An Existing Row Can Be Changed

            'If "Create And Add Rows 2" Has Been Run
            'NOTE - If you created the table WithOut a primary key - this adds a new row
            '       If you created the table With a primary key - it modifies the existing row
            If DTbl.Rows.Count > 1 Then 'Modify The Second Row
                ID = 2
                clecID = 1
                ilecID = 1
                Resale = 1
                UNEP = 0
                TaskOrder = 5  'TaskOrder
                Task = "AddBSDABResaleOCCInfo"
                Descr = "Add All Other Charges And Credits"
                Dim ThisStr() As String = {ID, clecID, ilecID, Resale, UNEP, TaskOrder, Task, Descr}
                DTbl.BeginLoadData()
                DRow = DTbl.LoadDataRow(CObj(ThisStr), True) 'Commits Changes
                DTbl.EndLoadData()
            End If

            DisplayRowData()
        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdCreateTablePK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateTablePK.Click
        Try
            'If Exists - Dispose OF First
            If IsNothing(DTbl) = False Then
                DTbl.Dispose()
            End If

            MsgBox("Creating Table With PK. Haven't Determined Why This Takes As Long As It Does.", MsgBoxStyle.Information, "Press OK And Be Patient.")

            DTbl = CreateTableMemoryObjectPK()

            DisplayObjectsUsingForLoop()

            DisplayObjectsUsingColumnNames()
        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdDataTableDocs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataTableDocs.Click
        Try
            System.Diagnostics.Process.Start("DataTableDocs.htm")
        Catch
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Displaying The Data Table Docs!", _
                "The Documentation Cannot Be Displayed.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdShowRowStates_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdShowRowStates.Click
        Try
            'If Exists - Dispose OF First
            If IsNothing(DTbl) = False Then
                DTbl.Dispose()
            End If
            'Create Data Table
            DTbl = CreateTableMemoryObjectPK()

            'Has DTbl Been Created
            If IsNothing(DTbl) Then
                MsgBox("The Data Table Object Has To Be Created First! Press The 'Create Table Memory Object First.", MsgBoxStyle.Information, "Create Table Object First")
                Exit Sub
            End If

            'New Row
            Dim DRow As DataRow
            DRow = DTbl.NewRow()
            DRow("ID") = 1
            DRow("clecID") = 1
            DRow("ilecID") = 1
            DRow("Resale") = 1
            DRow("UNEP") = 0
            DRow("TaskOrder") = 1
            DRow("Task") = "AddBSDABResaleUSOCInfo"
            DRow("Descr") = "Add All USOCs"

            MsgBox("Row State Before Adding To Table Is: " & DRow.RowState)

            DTbl.Rows.Add(DRow)
            MsgBox("Row State After DTbl.Rows.Add(DRow): " & DRow.RowState)

            DTbl.AcceptChanges()
            MsgBox("Row State After DTbl.AcceptChanges(): " & DRow.RowState)

            DTbl.Rows.RemoveAt(0)
            MsgBox("Row State After DTbl.Rows.RemoveAt(0): " & DRow.RowState)

            'Add The Row Back And AcceptChanges
            DTbl.Rows.Add(DRow)
            DTbl.AcceptChanges()
            MsgBox("Row State After DTbl.Rows.Add(DRow) And DTbl.AcceptChanges(): " & DRow.RowState)

            DTbl.Rows(0).Item("TaskOrder") += 1
            MsgBox("Row State After Changing A Column Value - DTbl.Rows(0).Item('TaskOrder') += 1: " & DRow.RowState)

            DTbl.RejectChanges()
            MsgBox("Row State After DTbl.RejectChanges(): " & DRow.RowState)

            DTbl.Rows(0).Delete()
            MsgBox("Row State After Deleting Row(): " & DRow.RowState)

        Catch 'Any Exception
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Displaying Row States Data!", _
                "The Application Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)

        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub AddTableToDS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddTableToDS.Click

        Try
            'If Exists - Dispose OF First
            If IsNothing(DTbl) = False Then
                DTbl.Dispose()
            End If
            'Create Data Table
            DTbl = CreateTableMemoryObjectNoPK()

            'Add Rows

            'Load Data From Array That Contains The Values
            Dim DRow As DataRow
            Dim Arry(2, 7) As String 'Contains Two Rows


            'Add Values To Array
            Arry(0, 0) = 1  'ID
            Arry(0, 1) = 1  'clecID
            Arry(0, 2) = 1  'ilecID
            Arry(0, 3) = 1  'Resale
            Arry(0, 4) = 0  'UNEP
            Arry(0, 5) = 1  'TaskOrder
            Arry(0, 6) = "AddBSDABResaleUSOCInfo"
            Arry(0, 7) = "Add All USOCs"

            Arry(1, 0) = 2
            Arry(1, 1) = 1
            Arry(1, 2) = 1
            Arry(1, 3) = 1
            Arry(1, 4) = 0
            Arry(1, 5) = 2  'TaskOrder
            Arry(1, 6) = "AddBSDABResaleOCCInfo"
            Arry(1, 7) = "Add All Other Charges And Credits"


            Dim DCol As DataColumn
            Dim RowCtr As Integer
            Dim ColCtr As Integer

            For RowCtr = 0 To Arry.GetUpperBound(0) - 1
                ColCtr = 0
                DRow = DTbl.NewRow()
                For Each DCol In DTbl.Columns
                    If DCol.DataType() Is System.Type.GetType("System.String") Then
                        DRow(DCol) = Arry(RowCtr, ColCtr).ToString
                    ElseIf DCol.DataType() Is System.Type.GetType("System.Int64") Then
                        DRow(DCol) = CLng(Arry(RowCtr, ColCtr))
                    ElseIf DCol.DataType() Is System.Type.GetType("System.Int32") Then
                        DRow(DCol) = CInt(Arry(RowCtr, ColCtr))
                    End If
                    ColCtr += 1
                Next
                DTbl.Rows.Add(DRow)
            Next

            DTbl.AcceptChanges()


            'Create DataSet
            Dim DSet As New DataSet
            'Add The Table To It
            DSet.Tables.Add(DTbl)

            MsgBox("Table Created, DataSet Created And Table Added To It.")

        Catch 'Any Exception
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Add The Data To The Data Table!", _
                "The Application Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdDSFromSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDSFromSQL.Click
        Dim DSet As DataSet

        Try
            'MsgBox("This Routine Has Problems! You Can Step Through The Code And See That The Retrieved Data Is Not In The Same Sequence As The Columns In The Select Statement.", MsgBoxStyle.Information, "Aborting Sub.")
            'Exit Sub

            'Start With New Table
            If IsNothing(DTbl) = True Then
                'DTbl = CreateTableMemoryObjectNoPK()
            Else
                DTbl.Dispose()
                'DTbl = CreateTableMemoryObjectNoPK()
            End If

            DTbl = CreateTableMemoryObjectNoPK()
            DSet = CreateDSFromSQL(DTbl) 'Pass Table Object

            'DataGrid1.DataSource = DSet
            'Now Have An ADO.NET DataSet That Contains A Data Table And Data From The SQLite Table

            'Multiple Data Tables Can Be Added To The Data Set
            'DataSet ds = new DataSet();
            'ds.Tables.Add(dt1);
            'ds.Tables.Add(dt2);
            '...
            'ds.Tables.Add(dtn);

        Catch ex As Exceptions.JCException
            lblStatus.Text = "Do Any Display And Form Setting Needed For User Here."

        Finally
            'No Finally Needed
        End Try

    End Sub

    Private Sub cmdDataSetDocs_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataSetDocs.Click
        Try
            System.Diagnostics.Process.Start("DataSetDocs.htm")
        Catch
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Displaying The Data Set Docs!", _
                "The Documentation Cannot Be Displayed.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
        Finally
            'No Finally Needed
        End Try
    End Sub

    'Private Sub cmdAttachTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAttachTable.Click

    '    DTbl = CreateTableMemoryObjectNoPK()
    '    AddDataToDTableFromSQL(DTbl)

    '    'This Displays Data All Out Of Sequence In The DataGrid
    '    DataGrid1.DataSource = DTbl

    'End Sub

    Private Sub cmdCreateDTFromSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCreateDTFromSQL.Click
        Dim DSet As DataSet
        Try
            'Start With New Table
            If IsNothing(DTbl) = True Then
                'DTbl = CreateTableMemoryObjectNoPK()
            Else
                DTbl.Dispose()
                'DTbl = CreateTableMemoryObjectNoPK()
            End If

            DTbl = CreateTableMemoryObjectNoPK()
            AddDataToDTableFromSQL(DTbl)

            'Now Have An ADO.NET Data Table And Data From The SQLite Table
            MsgBox("Now Have An ADO.NET Data Table And Data From The SQLite Table", MsgBoxStyle.Information, "Press OK To Close.")

        Catch ex As Exceptions.JCException
            HandledExceptionManager.ShowDialog("An Unexpected Error Occurred Creating The Data Table From SQLite Database!", _
                "App Cannot Continue.", _
                "Please Contact Tech Support", _
                "Tech Support Phone Numbers", _
                HandledExceptionManager.EHBtn.OK, _
                HandledExceptionManager.EHIcon.Stp, _
                0)
        Finally
            'No Finally Needed
        End Try


    End Sub

    Private Sub cmdDataDrids_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDataDrids.Click
        Try
            Dim GrdFrm As New frmDataGrid
            GrdFrm.ShowDialog()
            GrdFrm.Close()
            GrdFrm.Dispose()
        Catch
        Finally
        End Try
    End Sub

    Private Sub frmMain_Disposed(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Disposed
        SubForm()
        frmMainRef = Nothing
    End Sub
End Class
