none
DataTable.Merge Method raising Constraint Exception

    Question

  • So i was given to believe that the manner of the DataAdapter, specifically a Typed TableAdapter created via the DataSet Designer, was self-aware in the Fill() process regarding existing rows within the table. 

    I am curious if Merge is supposed to follow suit. 

    Basically, I have my Dataset, with multiple tables and views.  On the current main Form I load up the dataset and begin using table adapters to fill() the few that I need immediately.  This basically allows the form to load. 

    However, there are a few tables that are not directly involved with the main processing of the application, and though their data is relevent in the database, it is not, shall we say, pertinent to load unless the user specifically requests to view that table, which I handle via a menu.   The menu spawns a form with a DataGridView on it (as a SizeableToolWindow) which then proceeds to load the data from the DB into the table passed to it from the main form, but it does this rather indirectly.

    First:

    Dataset
       Table1
       Table2
       Table3
       View1
       View2
       View3

    clss View3Form
     inherits TableForm(Of DataSet.View3DataTable)

    end class

    So using the generics in this circumstance allowed me to precode all the basic stuff, but then type specific the handling of the individual tables (for sorting and other such things).

    The indirect Loader is handled via a class which uses Asynchronous and Paginated loaded systems to gather the data for larger tables.  In a table with 1 or 2 thousand rows this is superfluous, but as the tables get upwards of 40 thousand and more (some are already over 150,000) this asynchronous loading is essential. 

    How it works, is I use the statement:

    SELECT <COLUMNS> FROM (
       SELECT <COLUMNS>, ROW_NUMBER() over (ORDER BY <PAGECOLUMN>) RowNum
       FROM <TABLE>
    ) tbl

    Now, for the Page based loading, and even during the asynchronous load which loads successively each page in the table, there is a WHERE clause which is formatted like this:

    Format ("WHERE RowNum BETWEEN {0} AND {1}", (Page * PageSize) + 1, (Page + 1) * PageSize)
    Which equates to RowNum BETWEEN 1 and 1000, BETWEEN 1001 AND 2000, etc given a pagesize of 1000 rows.   I am using the BackGroundWorker class to handle my asynchronous processing as it was the easiest and cleanest with already synchronized progress event handling, so I run the loop of pages from 0 to PageCount, lets say 5 for the sake of argument, and at each progress step I Report progress, which passes the filled table with the current 1000 rows of the page loaded to the main thread which Merges it into the datatable passed to the Loader class.  (I will include relevant code after the explanation). 
    Now, For View3 this is fine because View3 is empty until it gets to the table viewing form (and subsequent page loader for filling it).  But for say Table1, which needs to be filled for other processes in the main form, but is never directly viewed, when I go to load it in the TableForm it basically bombs at the Merge statement on a failed unique Constraint.  Handling the exception I found that it has actually added all the same rows a second time into the datatable, so no check was made to verify that the row already existed so there was no need to merge it.  I was under the impression that the Fill() and Merge() (based upon the help documentation for the methods) evaluate the existing rows and will not add new on a match, it will either ignore or overwrite depending upon the FillLoadOption of PreserveChanges (or the boolean parameter in the Merge() method).  This apparently at the moment is not the case.  Any suggestions on getting the merge to recognize a row already exists in the data table?

    Code:
    Table Form Code & 1 Decendent Form for Example
    '':=========================================================================
    '': File: TableForm.vb
    '': Author: J"SD"a'RR
    '': Created: 7/17/09
    '': Copyright: © 2009 CorbiPlastics, LLC
    '': Purpose: None
    '':=========================================================================
    
    ''' <summary>
    ''' TODO: Documentation
    ''' </summary>
    ''' <typeparam name="TTable"></typeparam>
    ''' <remarks></remarks>
    Public Class TableForm(Of TTable As DataTable)
    #Region "-------------::< Fields & Constants >::-------------"
       Private WithEvents _loader As TablePageLoader(Of TTable)
       Private _table As TTable
       Private _hooked As Boolean = False
       Private _closing As Boolean = False
    #End Region
    
    #Region "-------------::< Class Properties >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <value></value>
       ''' <returns></returns>
       ''' <remarks></remarks>
       Public Property Table() As TTable
          Get
             Return _table
          End Get
          Set(ByVal value As TTable)
             If Not Object.Equals(_table, value) Then
                If _table.isValid() Then UnHookTable()
                _table = value
                HookTable()
                If _hooked Then
                   Me.Text = My.ResX_Captions.CAP_Tables.FmtStr(_table.TableName)
                End If
             End If
          End Set
       End Property
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <value></value>
       ''' <returns></returns>
       ''' <remarks></remarks>
       Public ReadOnly Property Loader() As TablePageLoader(Of TTable)
          Get
             Return _loader
          End Get
       End Property
    #End Region
    
    #Region "-------------::< Class Constructors >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="Table"></param>
       ''' <remarks></remarks>
       Public Sub New(ByVal Table As TTable)
          MyClass.new()
          InitializeLoader(Table)
       End Sub
    #End Region
    
    #Region "-------------::< Protected Methods >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="Tbl"></param>
       ''' <remarks></remarks>
       Protected Overridable Sub InitializeLoader(ByVal Tbl As TTable)
          _loader = New TablePageLoader(Of TTable)(My.Application.ConnectionMgr, Tbl)
          Me.Table = Tbl
       End Sub
    #End Region
    
    #Region "-------------::< Private Methods >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Public Sub UnHookTable()
          If _hooked Then
             bind_Table.Disconnect()
             bind_Table.DataSource = Nothing
             _loader.Table = Nothing
             _hooked = False
          End If
       End Sub
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Public Sub HookTable()
          If Not _hooked Then
             _loader.Table = _table
             bind_Table.Reconnect(_table)
             _hooked = _table.isValid()
          End If
       End Sub
    
    #Region "==[Control Event Handlers]=="
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="e"></param>
       ''' <remarks></remarks>
       Private Sub TableForm_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
          If Not _closing.Assign(e.CloseReason <> CloseReason.UserClosing) Then Me.Hide()
          e.Cancel = Not _closing
       End Sub
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="e"></param>
       ''' <remarks></remarks>
       Private Sub TableForm_VisibleChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.VisibleChanged
          If Not _closing Then
             If Me.Visible Then
                If _loader.isValid() Then
                   HookTable()
                   _loader.Reload()
                End If
             Else : UnHookTable()
             End If
          End If
       End Sub
    #End Region
    
    #Region "==[Field Event Handlers]=="
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="e"></param>
       ''' <remarks></remarks>
       Private Sub _loader_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles _loader.LoadComplete
          sp_ProgBar.Visible = False
          StatusBar.Refresh()
       End Sub
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="e"></param>
       ''' <remarks></remarks>
       Private Sub _loader_LoadProgress(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles _loader.LoadProgress
          If Not sp_ProgBar.Visible Then sp_ProgBar.Visible = True
          sp_ProgBar.Value = e.ProgressPercentage
       End Sub
    #End Region
    #End Region
    
    End Class
    
    
    ''' <summary>
    ''' TODO: Documentation
    ''' </summary>
    ''' <remarks></remarks>
    Public Class ActiveCalcForm
       Inherits TableForm(Of ReconcileDataSet.qry_CalcReconcileDataTable)
    #Region "-------------::< Class Constructors >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="Table"></param>
       ''' <remarks></remarks>
       Public Sub New(ByVal Table As ReconcileDataSet.qry_CalcReconcileDataTable)
          MyBase.new(Table)
       End Sub
    #End Region
    
    #Region "-------------::< Public Methods >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="Tbl"></param>
       ''' <remarks></remarks>
       Protected Overrides Sub InitializeLoader(ByVal Tbl As ReconcileDataSet.qry_CalcReconcileDataTable)
          MyBase.InitializeLoader(Tbl)
          Me.Loader.ResetOrderColumn(Tbl.ServiceDateColumn.ColumnName, SqlClient.SortOrder.Descending)
       End Sub
    #End Region
    End Class
    

    Table Page Loader Class (I've extracted teh Offending Method OnProgress() so it can be found easier)
    '':=========================================================================
    '': File: DataTablePageLoader.vb
    '': Author: J"SD"a'RR
    '': Created: Today
    '': Copyright: © 2009 CorbiPlastics, LLC
    '': Purpose: None
    '':=========================================================================
    
    Imports System
    Imports System.Data
    Imports System.Data.SqlClient
    Imports System.Data.Linq
    Imports System.ComponentModel
    Imports System.Threading
    
    ''' <summary>
    ''' TODO: Documentation
    ''' </summary>
    ''' <remarks></remarks>
    Public Class TablePageLoader(Of T As DataTable)
       Implements IDisposable
    
    #Region "-------------::< Events & Delegates >::-------------"
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Public Event LoadProgress As ProgressChangedEventHandler
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Public Event LoadComplete As EventHandler
    #End Region
    
    #Region "-------------::< Fields & Constants >::-------------"
       Private Const SQLBASE As String = "SELECT {0} FROM ()"
       Private Const SQL_INNER As String = "SELECT {0}, ROW_NUMBER() OVER(ORDER BY {1}) RowNum FROM {2}"
       Private Const SQL_PAGES As String = "SELECT ((COUNT(*) / {0}) + (CASE (COUNT(*) % {0}) WHEN 0 THEN 0 ELSE 1 END)) Pages FROM {1}"
       Private Const SQL_GETPAGE As String = "SELECT {0} FROM ({1}) tbl WHERE RowNum BETWEEN {2} AND {3}"
       Private Const SQL_CONDITIONAL As String = "SELECT DISTINCT (RowNum / {0}) Pages FROM ({1}) tbl WHERE {2}"
    
       Private Const SQL_ROWNUM As String = ", ROW_NUMER() OVER(ORDER BY {0}) RowNum"
    
       Private _mgr As SqlConnectionMgr = Nothing
       Private _con As SqlConnection = Nothing
       Private _cmd As SqlCommand
       Private _adap As SqlDataAdapter
       Private WithEvents _async As BackgroundWorker
       Private _loaded As New FlufCollection(Of Integer)()
       Private WithEvents _columns As OrderColumnProperties
       Private _tbl As T
       Private _tblName As String
       Private _sqlInner As String
    
       Private _pageSize As Integer = 1000
       Private _pageCount As Integer = -1
       Private _curPage As Integer = -1
    
       Private _typedTable As Boolean = False
       Private _genByTable As Boolean = False
       Private _autoGen As Boolean = True
       Private _validTable As Boolean = False
       Private disposedValue As Boolean = False      ' To detect redundant calls
    #End Region
    
    #Region "-------------::< Class Properties >::-------------"
       Public Property Table() As T
          Get
             Return _tbl
          End Get
          Set(ByVal value As T)
             SetTable(value)
          End Set
       End Property
    
       Public Property TableName() As String
          Get
             Return _tblName
          End Get
          Set(ByVal value As String)
             SetTable(value)
          End Set
       End Property
    
       Public Property AutoGenColumns() As Boolean
          Get
             Return _autoGen
          End Get
          Set(ByVal value As Boolean)
             _autoGen = value
          End Set
       End Property
    
       Public Property PageSize() As Integer
          Get
             Return _pageSize
          End Get
          Set(ByVal value As Integer)
             If _pageSize <> value Then
                _pageSize = value
                MakeDirty()
             End If
          End Set
       End Property
    
       Public ReadOnly Property PageCount() As Integer
          Get
             Return GetPageCount()
          End Get
       End Property
    
       Public ReadOnly Property Columns() As OrderColumnProperties
          Get
             If Not _columns.isValid() Then _columns = New OrderColumnProperties()
             Return _columns
          End Get
       End Property
    
       Protected Friend ReadOnly Property Connection() As SqlConnection
          Get
             Return GetConnection()
          End Get
       End Property
    
       Protected ReadOnly Property SQL_Schema() As String
          Get
             Return SQLConst.SQL_Select.FmtStr(SQLConst.SQL_All, TableName)
          End Get
       End Property
    
       Protected ReadOnly Property SQLInner() As String
          Get
             If _sqlInner.isEmpty() Then _sqlInner = SQL_INNER.FmtStr(Columns.ColumnString, Columns.OrderString, TableName)
             Return _sqlInner
          End Get
       End Property
    
       Protected ReadOnly Property SQLPages() As String
          Get
             Return SQL_PAGES.FmtStr(Me.PageSize, TableName)
          End Get
       End Property
    
       Protected ReadOnly Property SQLGetPage(ByVal page As Integer) As String
          Get
             Return SQL_GETPAGE.FmtStr(Columns.ColumnString, SQLInner, (page * Me.PageSize) + 1, (page + 1) * Me.PageSize)
          End Get
       End Property
    
       Protected ReadOnly Property Command(ByVal CmdText As String) As SqlCommand
          Get
             If _cmd.isValid Then _cmd.CommandText = CmdText Else _cmd = New SqlCommand(CmdText, Connection)
             Return _cmd
          End Get
       End Property
    
       Protected ReadOnly Property SQLConditional(ByVal condition As String) As String
          Get
             Return SQL_CONDITIONAL.FmtStr(Me.PageSize, SQLInner, condition)
          End Get
       End Property
    #End Region
    
    #Region "-------------::< Class Constructors >::-------------"
       Public Sub New(ByVal Connection As SqlConnection)
          MyBase.new()
          _con = Connection
          Initialize()
       End Sub
    
       Public Sub New(ByVal Manager As SqlConnectionMgr)
          MyBase.new()
          _mgr = Manager
          Initialize()
       End Sub
    
       Public Sub New(ByVal Manager As SqlConnectionMgr, ByVal Table As T)
          MyClass.new(Manager)
          SetTable(Table)
       End Sub
    
       'Public Sub New(ByVal Connection As SqlConnection, ByVal Table As T)
       '   MyClass.new(Connection)
       '   SetTable(Table)
       'End Sub
    
       'Public Sub New(ByVal Connection As SqlConnection, ByVal TableName As String)
       '   MyClass.new(Connection)
       '   SetTable(TableName)
       'End Sub
    
       Public Sub New(ByVal Manager As SqlConnectionMgr, ByVal TableName As String)
          MyClass.new(Manager)
          SetTable(TableName)
       End Sub
    #End Region
    
    #Region "-------------::< Public Methods >::-------------"
       Public Sub GetPage(ByVal PageNumber As Integer)
          If _validTable AndAlso PageNumber.Between(0, PageCount) Then
             Try
                _adap = New SqlDataAdapter(Command(SQLGetPage(PageNumber))) With {.FillLoadOption = LoadOption.PreserveChanges}
                _adap.Fill(_tbl)
             Finally
                _adap.Free()
             End Try
          End If
       End Sub
    
       Public Sub NextPage()
          If _validTable Then
             _curPage += 1
             GetPage(_curPage)
          End If
       End Sub
    
       Public Sub Load(ByVal Condition As String)
          If _validTable AndAlso Condition.isValid() AndAlso _mgr.isValid() AndAlso (Not _async.isValid()) Then
             Dim pages() As Integer
             Dim tbl As New DataTable("Tmp")
             Try
                _adap = New SqlDataAdapter(Command(SQLConditional(Condition)))
                _adap.Fill(tbl)
                pages = (From r In tbl Select CType(r(0), Integer)).ToArray()
                _async = New BackgroundWorker()
                _async.WorkerReportsProgress = True
                _async.WorkerSupportsCancellation = True
                _async.RunWorkerAsync(New AsyncArgStruct(_mgr.GetConnection, Columns.ColumnString, SQLInner, pages, PageCount, PageSize, False))
             Catch ex As Exception
                _adap.Free()
             End Try
          End If
       End Sub
    
       Public Sub LoadAsync()
          If _validTable AndAlso _mgr.isValid() AndAlso (Not _async.isValid()) Then
             _async = New BackgroundWorker()
             _async.WorkerReportsProgress = True
             _async.WorkerSupportsCancellation = True
             _async.RunWorkerAsync(New AsyncArgStruct(_mgr.GetConnection, Columns.ColumnString, SQLInner, _loaded.ToArray, PageCount, PageSize, True))
          End If
       End Sub
    
       Public Sub ClearOrder()
          If _validTable Then
             For i As Integer = 0 To Columns.Count - 1
                Columns(i) = SortOrder.Unspecified
             Next
          End If
       End Sub
    
       Public Sub ResetOrderColumn(ByVal ColumnName As String, ByVal Order As SortOrder)
          ClearOrder()
          Columns(ColumnName) = Order
       End Sub
    
       Public Sub Reload()
          If _validTable AndAlso _mgr.isValid() Then
             _loaded.Clear()
             LoadAsync()
          End If
       End Sub
    #Region " IDisposable Support "
       ''' <summary>
       ''' This code added by Visual Basic to correctly implement the disposable pattern.
       ''' </summary>
       ''' <remarks></remarks>
       Public Sub Dispose() Implements IDisposable.Dispose
          ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
          Dispose(True)
          GC.SuppressFinalize(Me)
       End Sub
    #End Region
    #End Region
    
    #Region "-------------::< Protected Methods >::-------------"
       Protected Overridable Sub Initialize()
          _typedTable = GetType(T).BaseType.IsGenericType AndAlso (GetType(T).BaseType.GetGenericTypeDefinition Is GetType(TypedTableBase(Of )))
       End Sub
    
       Protected Overridable Function GetPageCount() As Integer
          If _validTable Then
             If (_pageCount <= 0) Then
                Try
                   _pageCount = CType(Command(SQLPages).ExecuteScalar, Integer)
                Catch
                   _pageCount = -1
                End Try
             End If
             Return _pageCount
          Else : _pageCount = -1
          End If
          Return -1
       End Function
    
       Protected Overridable Sub GenerateColumns()
          If _autoGen Then
             Columns.Clear()
             If (Not ((_typedTable OrElse _genByTable) AndAlso _tbl.isValid())) AndAlso _tblName.isValid() Then
                Try
                   _adap = New SqlDataAdapter(SQL_Schema, Me.Connection)
                   _adap.FillSchema(_tbl, SchemaType.Source)
                Finally
                   _adap.Free()
                End Try
             End If
             If _tbl.isValid() AndAlso (_tbl.Columns.Count > 0) Then
                For Each column As DataColumn In _tbl.Columns
                   Columns.Add(column.ColumnName, SortOrder.Unspecified)
                Next
                Columns(0) = SortOrder.Ascending
             End If
          End If
       End Sub
    
       Protected Function GetConnection() As SqlConnection
          If _con.isValid() Then
             If _con.State <> ConnectionState.Open Then
                Try
                   _con.Open()
                Catch
                   _con = Nothing
                End Try
             End If
          End If
          If ((Not _con.isValid()) OrElse (_con.State <> ConnectionState.Open)) AndAlso _mgr.isValid() Then
             Try
                _con = _mgr.GetConnection(False)
             Catch
                _con = Nothing
             End Try
          End If
          Return _con
       End Function
    
       Protected Sub SetTable(ByVal Name As String)
          If Name.isValid() AndAlso (Name <> Me._tblName) Then
             SetTable(CType(Activator.CreateInstance(GetType(T), New Object() {Name}), T))
          End If
       End Sub
    
       Protected Overridable Sub SetTable(ByVal Table As T)
          If Table.isValid() AndAlso (Not Table.Equals(_tbl)) Then
             _tbl = Table
             _tblName = _tbl.TableName
             _genByTable = (_tbl.Columns.Count > 0)
             GenerateColumns()
             MakeDirty()
             _validTable = _tbl.isValid() AndAlso (Me.Columns.Count > 0)
          End If
       End Sub
    
       Protected Overridable Sub OnComplete(ByVal e As RunWorkerCompletedEventArgs)
          RaiseEvent LoadComplete(Me, EventArgs.Empty)
          _async.Free()
       End Sub
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <param name="disposing"></param>
       ''' <remarks></remarks>
       Protected Overridable Sub Dispose(ByVal disposing As Boolean)
          If Not Me.disposedValue Then
             If disposing Then
             End If
          End If
          Me.disposedValue = True
       End Sub
    #End Region
    
    #Region "-------------::< Private Methods >::-------------"
       Private Sub MakeDirty()
          _sqlInner = ""
          _pageCount = -1
    
       End Sub
    
       Private Sub _columns_ItemSet(ByVal sender As Object, ByVal e As ItemInsertEventArgs(Of System.Data.SqlClient.SortOrder)) Handles _columns.ItemSet
          MakeDirty()
       End Sub
    
       Private Sub _async_DoWork(ByVal sender As Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles _async.DoWork
          Dim struct As AsyncArgStruct = CType(e.Argument, AsyncArgStruct)
          Dim adap As SqlDataAdapter = Nothing
          Dim cmd As SqlCommand = Nothing
          Try
             Dim tbl As T
             cmd = New SqlCommand()
             cmd.Connection = struct.Connection
             adap = New SqlDataAdapter(cmd)
             For page As Integer = 0 To struct.PageCount - 1
                tbl = CType(Activator.CreateInstance(GetType(T), New Object() {}), T)
                If struct.AreLoaded <> struct.Pages.Contains(page) Then
                   cmd.CommandText = SQL_GETPAGE.FmtStr(struct.Columns, struct.SQL, (page * struct.PageSize) + 1, (page + 1) * struct.PageSize)
                   adap.Fill(tbl)
                   _async.ReportProgress((page + 1) * 100 \ struct.PageCount, New AsyncProgStruct(page, tbl))
                End If
             Next
          Catch ex As Exception
             e.Result = ex
          Finally
             struct.Connection.Close()
             _adap.Free()
             cmd.Free()
          End Try
       End Sub
    
       Private Sub _async_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles _async.ProgressChanged
          OnProgress(e)
       End Sub
    
       Private Sub _async_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles _async.RunWorkerCompleted
          OnComplete(e)
       End Sub
    #End Region
    
    #Region "==[ Nested Types ]=="
    
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Public Class OrderColumnProperties
          Inherits FlufKeyedCollection(Of String, SortOrder)
    #Region "-------------::< Public Methods >::-------------"
          Public Function OrderString() As String
             Return (From s In Keys Where Me(s) <> SortOrder.Unspecified _
                    Select s.Bracket() & " " & SQLConst.SQLSORT(CType(Me(s), Integer) + 1)).ToArray().Join(", ")
          End Function
    
          Public Function ColumnString() As String
             Return Keys.ToArray().Frame(BRACKETS).Join(", ")
          End Function
    #End Region
       End Class
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Private Structure AsyncArgStruct
          Public Columns As String
          Public SQL As String
          Public Pages() As Integer
          Public PageCount As Integer
          Public Connection As SqlConnection
          Public AreLoaded As Boolean
          Public PageSize As Integer
    
          Public Sub New(ByVal Connect As SqlConnection, ByVal Cols As String, ByVal Inner As String, ByVal PageArray() As Integer, ByVal TotalPages As Integer, ByVal Size As Integer, ByVal Loaded As Boolean)
             Columns = Cols
             SQL = Inner
             Pages = PageArray
             PageCount = TotalPages
             Connection = Connect
             AreLoaded = Loaded
             PageSize = Size
          End Sub
       End Structure
    
       ''' <summary>
       ''' TODO: Documentation
       ''' </summary>
       ''' <remarks></remarks>
       Private Structure AsyncProgStruct
          Public Table As T
          Public Page As Integer
    
          Public Sub New(ByVal CurPage As Integer, ByVal Tbl As T)
             Table = Tbl
             Page = CurPage
          End Sub
       End Structure
    #End Region
    End Class
    
    OnProgress Method - The Line with the Merge() method call is where the error occurs
    Protected Overridable Sub OnProgress(ByVal e As ProgressChangedEventArgs)
          Dim struct As AsyncProgStruct = CType(e.UserState, AsyncProgStruct)
          _loaded.Add(struct.Page)
          Try
             _tbl.Merge(struct.Table, True)
             struct.Table.Free()
          Catch ex As Exception
             Console.WriteLine(ex.ToString)
          End Try
          RaiseEvent LoadProgress(Me, New ProgressChangedEventArgs(e.ProgressPercentage, Nothing))
       End Sub


    I apologize as I have not yet gotton to much documentation on this class as I am still working out the kinks and debugging like crazy.

    Thanks
    Jaeden "Sifo Dyas" al'Raec Ruiner


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    Wednesday, August 05, 2009 7:23 PM

Answers

  • Ahh,

    it wasn't in the merging it was in the Dataset.  I discovered in my Reflector examinations that the Merger internal class uses the PrimaryKey property of the datatable to use for validity and redundancy checks between datatable objects.  I had assumed that the Primary key was set in my dataset, but as I investigated during a step through, the PrimaryKey collection had a count of 0. 
    As it turned out the Unique Constraint for the Primary Key column had been created, but was not specifically flagged as the PrimaryKey for that datatable.  ONce I made that correction, the merge worked fine without a hitch. 

    Once again, it goes to show the source of the symptom isn't always the source of the problem. 

    Thanks anyway,

    Jaeden "Sifo Dyas" al'Raec Ruiner
    "Never Trust a computer. Your brain is smarter than any micro-chip."
    • Marked as answer by JaedenRuiner Thursday, August 06, 2009 7:00 PM
    Thursday, August 06, 2009 7:00 PM