Test SQL Connection

    Private Sub TestSqlConnection(ByVal TableNameString As String, ByVal ConnectionString As String)
        Debug.WriteLine("Generic SQL connection test started.")
        Debug.WriteLine("This subroutine will attempt to create a record in a SQL table, then delete that record.")
        Debug.WriteLine("If no errors are generated in this subroutine, the test was successful.")
        Dim SelectString As String = String.Format("sp_pkeys [{0}]", TableNameString)
        Dim DataAdapter As New System.Data.SqlClient.SqlDataAdapter(SelectString, ConnectionString)
        Dim DatabaseCommandBuilder As New System.Data.SqlClient.SqlCommandBuilder(DataAdapter)
        DatabaseCommandBuilder.QuotePrefix = "["
        DatabaseCommandBuilder.QuoteSuffix = "]"
        Dim KeysDataTable As New Data.DataTable
        SelectString = String.Format("sp_pkeys [{0}]", TableNameString)
        DataAdapter.Fill(KeysDataTable)
        If KeysDataTable.Rows.Count <> 1 Then
            MsgBox(String.Format("This subroutine can only test a SQL table with 1 key field.  {0}The [{1}] table has {2} key fields.", _
            ControlChars.CrLf, TableNameString, KeysDataTable.Rows.Count))
            Exit Sub
        End If
        Dim KeyColumnName As String = KeysDataTable.Rows(0).Item("COLUMN_NAME")
        Dim TestDataTable As New Data.DataTable
        SelectString = String.Format("Select * from [{0}]", TableNameString)
        DataAdapter.SelectCommand = New SqlClient.SqlCommand(SelectString, New SqlClient.SqlConnection(ConnectionString))
        DataAdapter.Fill(TestDataTable)
        Debug.WriteLine("Test dataset filled.")
        Dim NewTestDataRow As Data.DataRow = Nothing
        NewTestDataRow = TestDataTable.NewRow
        For Each TestDataColumn As Data.DataColumn In TestDataTable.Columns
            Dim ColumnName As String = TestDataColumn.ColumnName
            If TestDataColumn.DataType Is System.Type.GetType("System.DateTime") Then
                NewTestDataRow.Item(ColumnName) = Now
            Else
                NewTestDataRow.Item(ColumnName) = TestDataTable.Rows.Count
            End If
        Next TestDataColumn
        Dim NewRowKey = NewTestDataRow.Item(KeyColumnName)
        TestDataTable.Rows.Add(NewTestDataRow)
        DataAdapter.Update(TestDataTable)
        Debug.WriteLine("Test dataset updated (row added).")
        TestDataTable.Clear()
        DataAdapter.Fill(TestDataTable)
        Dim DataColumnArray() As Data.DataColumn = {TestDataTable.Columns(KeyColumnName)}
        TestDataTable.PrimaryKey = DataColumnArray
        Dim RowToDelete As Data.DataRow = TestDataTable.Rows.Find(NewRowKey)
        TestDataTable.Rows(TestDataTable.Rows.IndexOf(RowToDelete)).Delete()
        Dim TableChanges As Data.DataTable = TestDataTable.GetChanges
        DataAdapter.Update(TestDataTable)
        Debug.WriteLine("Test dataset updated (row deleted).")
        Debug.WriteLine("Generic SQL connection test ended.")
    End Sub