VS 2017 [RESOLVED] Exception when clearing a datatable with a CheckBox-VBForums
Results 1 to 8 of 8

Thread: [RESOLVED] Exception when clearing a datatable with a CheckBox

  1. #1

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    168

    Resolved [RESOLVED] Exception when clearing a datatable with a CheckBox

    0
    down vote
    favorite

    I have a table with a bound DGV. The table has 12 columns, one of which is a check box. The table has data in it and there are no problems.
    I want to clear the table and then import data into the table from Excel. The problem is the minute I clear the table an exception occurs. I think his is because the check box has a null value.

    There are no problems with other tables, that do not have the checkbox. I can clear the table and import data from Excel.

    The Try...Catch procedure does not even catch the error.
    Code:
                      'Clear DGV
                        Try
                            DsAstroCatalogueData.tblMessierData.Clear()
                        Catch ex As Exception
                            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Finally
                            'Nothing
                        End Try
    I stepped through the code and had a break point on the clear statement. The moment I step out of the "DsAstroCatalogueData.tblMessierData.Clear()" the exception is thrown.

    Please assist.

    Full Sub code:
    Code:
    Private Sub MnuImportFromExcel_Click(sender As Object, e As EventArgs) Handles mnuImportFromExcel.Click
    
            Dim strExcelFilePath As String = ""
    
            Dim strCellValue As String = ""
    
            Dim strColumn1 As String = ""
            Dim strColumn2 As String = ""
            Dim strColumn3 As String = ""
            Dim strColumn4 As String = ""
            Dim strColumn5 As String = ""
            Dim strColumn6 As String = ""
            Dim strColumn7 As String = ""
            Dim strColumn8 As String = ""
            Dim strColumn9 As String = ""
            Dim strColumn10 As String = ""
            Dim boolColumn11 As Boolean = False
            Dim strColumn12 As String = ""
    
            Dim intColumnIndex As Integer = 0
            Dim intRowIndex As Integer = 0
    
            Dim intItemsAddedCount As Integer = 0
    
            statusCatalogProgressBar.Value = 0
    
            Try
                'Select Excel File
                With OpenFileDialogExcel()
                    .Filter = "Excel Files|*.xls;*.xlsx"
                    .Title = "Select- Excel Configuration File"
                    .FileName = ""
                End With
    
                If (OpenFileDialogExcel.ShowDialog() = DialogResult.OK) Then
                    strExcelFilePath = OpenFileDialogExcel.FileName
                Else
                    strExcelFilePath = ""
                End If
    
            Catch ex As Exception
                MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                'Nothing
            End Try
    
    
            If strExcelFilePath IsNot "" Then
    
                'Update Status Bar Text
                statusCatalogFrm.Text = "Importing Catalog from Excel..."
                statusCatalogProgressBar.Visible = True
                statusCatalogRightLabel.Visible = False
    
                Dim xlAppEyepieces As New Excel.Application
                Dim xlWorkBookEyepieces As Excel.Workbook
                Dim xlWorkSheetEyepieces As Excel.Worksheet
                Dim xlRangeEyepieces As Excel.Range
    
    
                Try
                    ' Open Excel Spreadsheet.
                    xlWorkBookEyepieces = xlAppEyepieces.Workbooks.Open(strExcelFilePath)
    
                    ' Loop over all sheets.
                    For i As Integer = 1 To xlWorkBookEyepieces.Sheets.Count
    
                        ' Get Worsheet Data.
                        xlWorkSheetEyepieces = CType(xlWorkBookEyepieces.Sheets(i), Worksheet)
    
    
                        ' Get Data Range.
                        xlRangeEyepieces = xlWorkSheetEyepieces.UsedRange
    
                        ' Load all cells into 2d array.
                        Dim objExcelArray(,) As Object = CType(xlRangeEyepieces.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))
    
                        'Close COM Objects
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRangeEyepieces)
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheetEyepieces)
                        ReleaseObject(xlRangeEyepieces)
                        ReleaseObject(xlWorkSheetEyepieces)
    
                        ' Scan the cells.
                        'If objExcelArray IsNot Nothing Then
    
                        ' Get bounds of the array.
                        Dim NumberOfRows As Integer = objExcelArray.GetUpperBound(0)  'Rows
                        Dim NumberOfColumns As Integer = objExcelArray.GetUpperBound(1)  'Columns
    
                        'Progress Bar Maximum Value
                        statusCatalogProgressBar.Maximum = NumberOfRows
    
                        'Clear DGV
                        Try
                            DsAstroCatalogueData.tblMessierData.Clear()
                        Catch ex As Exception
                            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                        Finally
                            'Nothing
                        End Try
    
                        ' Loop over all elements.
                        For intRowIndex = 0 To NumberOfRows - 2
    
                                For intColumnIndex = 1 To NumberOfColumns
    
                                    Try
                                        'Test for NULL Value
                                        If objExcelArray(intRowIndex + 2, intColumnIndex) IsNot Nothing Then
    
                                            strCellValue = objExcelArray(intRowIndex + 2, intColumnIndex).ToString
    
                                            Select Case intColumnIndex
                                                Case 1
                                                    strColumn1 = strCellValue
                                                Case 2
                                                    strColumn2 = strCellValue
                                                Case 3
                                                    strColumn3 = strCellValue
                                                Case 4
                                                    strColumn4 = strCellValue
                                                Case 5
                                                    strColumn5 = strCellValue
                                                Case 6
                                                    strColumn6 = strCellValue
                                                Case 7
                                                    strColumn7 = strCellValue
                                                Case 8
                                                    strColumn8 = strCellValue
                                                Case 9
                                                    strColumn9 = strCellValue
                                                Case 10
                                                    strColumn10 = strCellValue
                                                Case 11
                                                    boolColumn11 = False
                                                Case 12
                                                    strColumn12 = strCellValue
                                            End Select
    
                                        Else
                                            Select Case intColumnIndex
                                                Case 1
                                                    strColumn1 = ""
                                                Case 2
                                                    strColumn2 = ""
                                                Case 3
                                                    strColumn3 = ""
                                                Case 4
                                                    strColumn4 = ""
                                                Case 5
                                                    strColumn5 = ""
                                                Case 6
                                                    strColumn6 = ""
                                                Case 7
                                                    strColumn7 = ""
                                                Case 8
                                                    strColumn8 = ""
                                                Case 9
                                                    strColumn9 = ""
                                                Case 10
                                                    strColumn10 = ""
                                                Case 11
                                                    boolColumn11 = False
                                                Case 12
                                                    strColumn1 = ""
                                            End Select
    
                                        End If
    
                                    Catch ex As Exception
                                        MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                                    Finally
                                        'Nothing
                                    End Try
    
                                Next
    
                                'Add data to Data Table
                                'If (strColumn1 IsNot "") And (strColumn2) IsNot "" Then
                                With DsAstroCatalogueData
                                    .Tables("tblMessierData").Rows.Add()
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("NGC") = strColumn1
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("Messier") = strColumn2
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("ObjectType") = strColumn3
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("ObjectName") = strColumn4
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("Constellation") = strColumn5
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("Magnitude") = strColumn6
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("RightAscension") = strColumn7
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("Declination") = strColumn8
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("ObjectSize") = strColumn9
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("Comment") = strColumn10
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("Viewed") = boolColumn11
                                    .Tables("tblMessierData").Rows(intRowIndex).Item("ImageFile") = strColumn12
                                End With
    
                            intItemsAddedCount = (intRowIndex + 1)
    
                            'Update Staus Bar
                            statusCatalogFrm.Text = "Importing Catalog from Excel... [" & intItemsAddedCount & " of " & (NumberOfRows - 1) & " rows]"
                            statusCatalogProgressBar.Value = intItemsAddedCount
                            StatusStripMessierFrm.Update()
    
                            'End If
    
                            strCellValue = ""
    
                            Next
    
    
                        'End If
                    Next
    
                    dgvMessierData.Refresh()
    
                    'Data Import Completed
                    MessageBox.Show("Data Import Complete" & vbLf & vbLf & intItemsAddedCount &
                                " items added", "AstroCompanion - Import Catalog Data from Exel",
                                   MessageBoxButtons.OK, MessageBoxIcon.Information)
    
                    'Update Status Bar Text
                    statusCatalogFrm.Text = "Ready"
                    statusCatalogProgressBar.Value = 0
                    statusCatalogProgressBar.Visible = False
    
    
    
                    'Close all Excel items
                    xlAppEyepieces.Workbooks.Close()
                    xlAppEyepieces.Quit()
                    GC.Collect()
                    GC.WaitForPendingFinalizers()
    
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBookEyepieces)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlAppEyepieces)
    
                    ReleaseObject(xlWorkBookEyepieces)
                    ReleaseObject(xlAppEyepieces)
    
    
                Catch ex As Exception
                    MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
                Finally
                    GC.Collect()
                End Try
    
    
            End If
    
        End Sub
    I can fix what stupid does, but, I cannot fix stupid

  2. #2
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,527

    Re: Exception when clearing a datatable with a CheckBox

    I don't know of any CheckBox fields. Are you talking about a YesNo field in an Access database? There is no problem with clearing a datatable with a YesNo field. What is the error message? If you change ex.Message to ex.ToString you will get more information.

  3. #3

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    168

    Re: Exception when clearing a datatable with a CheckBox

    I don't know of any CheckBox fields.
    Data Table field is a Boolean. DGV Column is a CheckBox (ViewedDataGridViewCheckBoxColumn) bound to the Boolean field in the data table.
    I can fix what stupid does, but, I cannot fix stupid

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    3,527

    Re: Exception when clearing a datatable with a CheckBox

    What's the error message??? What type of database, SQL Server, Access...
    Don't see any Boolean field types either.

    edit - FYI a MS Access YesNo field type will have a data type of Boolean in your dataset.
    Last edited by wes4dbt; Feb 4th, 2018 at 03:46 PM.

  5. #5

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    168

    Re: Exception when clearing a datatable with a CheckBox

    Hi Wes

    Thanks for the effort. I found my problem. Been struggling with this all day. You know what they say if you are dumb you must suffer (that's me)

    I have another sub that updates the CheckBox, on the form, when it is changed. Namely "BindingSource.CurrentChanged". This sub was throwing the exception. I handled the error there.


    To tie up loose ends:
    It is an XML Data Set.

    In the code above there is a line:
    Code:
    Case 11
      boolColumn11 = False

    Which gets written to a boolean field in a data set:
    Code:
    .Tables("tblMessierData").Rows(intRowIndex).Item("Viewed") = boolColumn11
    Thanks for the assistance
    I can fix what stupid does, but, I cannot fix stupid

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,718

    Re: Exception when clearing a datatable with a CheckBox

    Quote Originally Posted by Slabs1960 View Post
    I have another sub that updates the CheckBox, on the form, when it is changed. Namely "BindingSource.CurrentChanged". This sub was throwing the exception. I handled the error there
    Do you mean that you're catching the exception? If so then that may not be an optimal solution. More often than not, you should avoid throwing the exception in the first place. If you can provide the relevant details, we may be able to suggest a better solution.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Addicted Member Slabs1960's Avatar
    Join Date
    Mar 2017
    Location
    Oranjemund, Namibia
    Posts
    168

    Re: Exception when clearing a datatable with a CheckBox

    Quote Originally Posted by jmcilhinney View Post
    Do you mean that you're catching the exception? If so then that may not be an optimal solution. More often than not, you should avoid throwing the exception in the first place. If you can provide the relevant details, we may be able to suggest a better solution.
    I am catching the exception.

    The problem was, when the Data Set was cleared, the Sub (separate) saw the change, and tried to update the checkbox. Since the value was NULL, the exception occurred. All I had to do is check for a NULL value, and set the CheckState to False, before updating the CheckBox.
    I can fix what stupid does, but, I cannot fix stupid

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,718

    Re: Exception when clearing a datatable with a CheckBox

    Quote Originally Posted by Slabs1960 View Post
    I am catching the exception.
    Quote Originally Posted by Slabs1960 View Post
    All I had to do is check for a NULL value, and set the CheckState to False, before updating the CheckBox.
    Those two statements seem to be at odds with each other. The second one seems to suggest that you're actually preventing the exception being thrown, which is generally the better option. If you're preventing the exception being thrown then you can't catch it.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width