Compare 2 datatables and bind end result to datagrid problem

[sous2817]

Hello everyone,

I'm pulling data from two SQLCE databases (Default_Assessments and Current_Assessments) and putting the results in to two seperate datatables. I'm then comparing one datatable to the other and I'd like to add in the missing values from the Default_Assessments datatable. The code works if the current_Assessments datatable is empty, but if there are values in it, I get a "ContraintException was unhandled" error. Specifically: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." Setting .EnforceContraints = False" doesn't solve it, and neither does clearing the table before the code is run. I also have a sneaking suspiscion that there's an easier way to go about this, but I can't seem to figure it out. Nonetheless, any help is appreciated. Here's my code:

Private Sub CheckBox1_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        Dim con As SqlCeConnection = New SqlCeConnection("Data Source =" & fileLocation & "\Resources\Assessments.sdf")
        con.Open()
  
        'creates table for current items
        Dim adapter As New SqlCeDataAdapter("Select * from Current_Assessments", con)
        Dim list_Current As New DataSet("list_Current")
        Dim table_Current As New DataTable("table_Current")
        list_Current.Tables.Add("table_Current")
  
        ' creates primary key
        Dim colArray As DataColumn() = New DataColumn(0) {}
        Dim col1 As New DataColumn()
        colArray(0) = col1
        table_Current.Columns.Add(col1)
        table_Current.PrimaryKey = colArray
  
        ' this doesn't seem to do anything
        list_Current.EnforceConstraints = False
  
        ' fills table
        adapter.Fill(table_Current)
  
        ' creates table for default items
        adapter = New SqlCeDataAdapter("Select * from Default_Assessments", con)
        Dim list_Default As New DataSet()
        Dim table_Default As New DataTable
        list_Default.Tables.Add(table_Default)
        adapter.Fill(table_Default)
  
        If CheckBox1.IsChecked Then
            'loops through default values and checks if they're in the current list, if not in list, adds them
            For i = 0 To table_Default.Rows.Count - 1
                Dim foundRow As DataRow = table_Current.Rows.Find(table_Default(i).Item(0).ToString)
                If foundRow Is Nothing Then
                    Dim NR As DataRow = table_Current.NewRow
                    NR(0) = table_Default(i).Item(0).ToString
                    NR(1) = CInt(table_Default(i).Item(1))
                    table_Current.Rows.Add(NR)
                End If
            Next
        Else
            For i = 0 To table_Default.Rows.Count - 1
                Dim foundRow As DataRow = table_Current.Rows.Find(table_Default(i).Item(0).ToString)
                If Not foundRow Is Nothing Then
                    ' still working this bit out...
                End If
            Next
        End If
  
        ' pushes table up to sqlce database
        For i = 0 To table_Current.Rows.Count - 1
            Dim cmd As SqlCeCommand = New SqlCeCommand("Insert into Current_Assessments Values ('" & table_Current(i).Item(0).ToString & "'," & CInt(table_Current(i).Item(1)) & ")", con)
            cmd.ExecuteNonQuery()
        Next
  
        'reads sqlce database and binds dataset to datagrid
        adapter = New SqlCeDataAdapter("Select * from Current_Assessments", con)
        Dim bind As New DataSet
        adapter.Fill(bind, "MyDataBinding")
        DataGridControl1.DataContext = bind
        con.Close()
        con.Dispose()
    End Sub

 

[jmcilhinney]

First things first, what constraints do you have on that table and which one are you violating?

 

[sous2817]

Sorry, should have been more specific. I don't explicitly set a constraint. The only thing I can think of is the Primary Key setting.

The code errors out on this line:

adapter.Fill(table_Current)

A bit of a back story, I couldn't get this line to work without setting a primary key in the data table:

Dim foundRow As DataRow = table_Current.Rows.Find(table_Default(i).Item(0).ToString)

So I added in this bit:

  Dim colArray As DataColumn() = New DataColumn(0) {}         
Dim col1 As New DataColumn()         
colArray(0) = col1        
 table_Current.Columns.Add(col1)       
 table_Current.PrimaryKey = colArray

And the table_Current populated as expected (as long as it was empty). When I uncheck the checkbox (which should remove the "Default" items from the current_table list) I get the "ContraintException was unhandled" error" on the line I listed above. Additionally, if I populate some items in to table_Current and then tick the checkbox (which should add in the default items), I get the same error on the same line.

The table_Default has a whopping 4 lines in it and only 2 columns (but could grow to about 10 lines). table_Current starts with 0 lines and could end up with as many as 35 or so entries (also with only 2 columns).

Also, I'm willing to bet that my method isn't the best way to go about it. If there's another way to tackle the problem, I'm all for learning...this approach is the end result of a few hours of hacking and by no means am I married to using it.

 

[sous2817]

I ended up going a different route with much better success. I built up a collection, looped through the collection to remove duplicates, and then bound the collection to a datagrid. All in all, a lot less code and a lot easier to manage!