locked
"Specified cast is not valid" after dynamically altering the DataTable RRS feed

  • Question

  • Hi.

    What I'm trying to do is change the DataTable structure dynamically. I add an handler for changesSelected:

    AddHandler remoteProvider.ChangesSelected, AddressOf remoteProvider_ChangesSelected

    Then I perform some manipulation in this way:

    If (e.Context.DataSet.Tables.Contains("Table")) Then
                Dim table = e.Context.DataSet.Tables("Table")
                Dim newTable = table.Clone()
                newTable.Rows.Clear()
                Dim firstColumn As New DataColumn("First")
                firstColumn.DataType = GetType(SqlString)
                firstColumn.MaxLength = 10
                firstColumn.AllowDBNull = False
                Dim secondColumn As New DataColumn("Second")
                secondColumn.DataType = GetType(SqlInt32)
                secondColumn.AllowDBNull = False
                newTable.Columns.Add(firstColumn)
                newTable.Columns.Add(secondColumn)
                newTable.Columns.Remove(newTable.Columns("AColumn"))

    I get some values with an sql command and generate some rows for newTable. Then I do this:

                e.Context.DataSet.Tables.Remove(e.Context.DataSet.Tables("Table"))
                e.Context.DataSet.Tables.Add(newTable)

    I get a "Specified cast is not valid" exception during syncOrchestrator.Synchronize(). The target table to sync has the same structure of newTable, but firstColumn is nchar(10) and secondColumn int.

    That's the exception detail.

    System.InvalidCastException non è stata gestita
      Message=Cast specificato non valido.
      Source=Microsoft.Synchronization
      StackTrace:
           in Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ApplyBulkChanges(DataTable dataTable)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChangesInternal(DbSyncScopeMetadata scopeMetadata, IDbTransaction transaction, FailedDeleteDelegate_type failedDeleteDelegate, DataSet dataSet, ChangeApplicationType applyType)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, IDbTransaction applyTransaction, DataSet dataSet, DbSyncSession DbSyncSession, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.SingleTransactionApplyChangesAdapter.Apply(DataSet dataSet, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, DataSet dataSet, DbSyncSession dbSyncSession, Boolean commitTransaction)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, Object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics)
           in Microsoft.Synchronization.KnowledgeProviderProxy.ProcessChangeBatch(CONFLICT_RESOLUTION_POLICY resolutionPolicy, ISyncChangeBatch pSourceChangeManager, Object pUnkDataRetriever, ISyncCallback pCallback, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)
           in Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTION_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)
           in Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)
           in Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed)
           in Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()
           in Microsoft.Synchronization.SyncOrchestrator.Synchronize()
           in ConsoleApplication1.prova3.Main(String[] args) in C:\Documents and Settings\m.bucchi\Documenti\Visual Studio 2010\Projects\SyncFx_esperimenti\SyncFx_esperimenti\prova3.vb:riga 78
      InnerException:

    I could post the full code if it can help.

    Monday, July 30, 2012 10:26 AM

Answers

All replies

  • not related to the error you're getting, but if you're clearing the datatable of the rows selected by Sync Fx and adding new rows manually, these rows no longer match the datarow state of the changes selected. the original datatable includes information if a row was inserted, updated or deleted.
    Monday, July 30, 2012 1:49 PM
  • not related to the error you're getting, but if you're clearing the datatable of the rows selected by Sync Fx and adding new rows manually, these rows no longer match the datarow state of the changes selected. the original datatable includes information if a row was inserted, updated or deleted.

    Thank you for the advice. But which are these informations? Because when I create the new rows I try to store the data from the previous ones... But I'm not sure if this is enough! Anyway, newRow is the row I'm creating, row is the one already existing in datatable and reader is the result of a sql SELECT... Is this code correct for don't lose "information if a row was inserted, updated or deleted"?

    newRow("Id") = CType(row("Id"), Int32)
    newRow("Name") = CType(row("Name"), String)
    newRow("Surname") = CType(row("Surname"), String)
    newRow("First") = CType(reader("First"), String)
    newRow("Second") = CType(reader("Second"), Int32)
    ' (???)
    newRow("sync_update_peer_timestamp") = row("sync_update_peer_timestamp")
    newRow("sync_update_peer_key") = row("sync_update_peer_key")
    newRow("sync_create_peer_timestamp") = row("sync_create_peer_timestamp")
    newRow("sync_create_peer_key") = row("sync_create_peer_key")

    Thank you very much!

    Tuesday, July 31, 2012 6:38 AM
  • Anyway, playing with the CType I solved the exception above...

    But now I get this one:

    "Failed to execute the command 'BulkInsertCommand' for table 'Customer'; the transaction was rolled back. Ensure that the command syntax is correct."

    Details:

    Microsoft.Synchronization.Data.DbSyncException non è stata gestita
      HelpLink=http://msdn.microsoft.com/sync
      Message=Failed to execute the command 'BulkInsertCommand' for table 'Cliente'; the transaction was rolled back. Ensure that the command syntax is correct.
      Source=Microsoft.Synchronization
      SyncSource=Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler
      StackTrace:
           in Microsoft.Synchronization.Data.ChangeHandlerBase.CheckZombieTransaction(String commandName, String table, Exception ex)
           in Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
           in Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ApplyBulkChanges(DataTable dataTable)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChangesInternal(DbSyncScopeMetadata scopeMetadata, IDbTransaction transaction, FailedDeleteDelegate_type failedDeleteDelegate, DataSet dataSet, ChangeApplicationType applyType)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, IDbTransaction applyTransaction, DataSet dataSet, DbSyncSession DbSyncSession, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.SingleTransactionApplyChangesAdapter.Apply(DataSet dataSet, Boolean commitTransaction, FailedDeleteDelegate_type failedDeleteDelegate, String batchFileName, ChangeApplicationAction& action)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ApplyChanges(DbSyncScopeMetadata scopeMetadata, DataSet dataSet, DbSyncSession dbSyncSession, Boolean commitTransaction)
           in Microsoft.Synchronization.Data.RelationalSyncProvider.ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, Object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics)
           in Microsoft.Synchronization.KnowledgeProviderProxy.ProcessChangeBatch(CONFLICT_RESOLUTION_POLICY resolutionPolicy, ISyncChangeBatch pSourceChangeManager, Object pUnkDataRetriever, ISyncCallback pCallback, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)
           in Microsoft.Synchronization.CoreInterop.ISyncSession.Start(CONFLICT_RESOLUTION_POLICY resolutionPolicy, _SYNC_SESSION_STATISTICS& pSyncSessionStatistics)
           in Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWaySyncHelper(SyncIdFormatGroup sourceIdFormats, SyncIdFormatGroup destinationIdFormats, KnowledgeSyncProviderConfiguration destinationConfiguration, SyncCallbacks DestinationCallbacks, ISyncProvider sourceProxy, ISyncProvider destinationProxy, ChangeDataAdapter callbackChangeDataAdapter, SyncDataConverter conflictDataConverter, Int32& changesApplied, Int32& changesFailed)
           in Microsoft.Synchronization.KnowledgeSyncOrchestrator.DoOneWayKnowledgeSync(SyncDataConverter sourceConverter, SyncDataConverter destinationConverter, SyncProvider sourceProvider, SyncProvider destinationProvider, Int32& changesApplied, Int32& changesFailed)
           in Microsoft.Synchronization.KnowledgeSyncOrchestrator.Synchronize()
           in Microsoft.Synchronization.SyncOrchestrator.Synchronize()
           in ConsoleApplication1.prova3.Main(String[] args) in C:\Documents and Settings\m.bucchi\Documenti\Visual Studio 2010\Projects\SyncFx_esperimenti\SyncFx_esperimenti\prova3.vb:riga 78
           in System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           in System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           in Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           in System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           in System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
           in System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           in System.Threading.ThreadHelper.ThreadStart()
      InnerException: System.Data.SqlClient.SqlException
           Class=14
           ErrorCode=-2146232060
           LineNumber=0
           Message=Violation of PRIMARY KEY constraint 'PK__#3E52440__3214EC073F466844'. Cannot insert duplicate key in object 'dbo.@changeTable'. The duplicate key value is (1).
    The data for table-valued parameter "@changeTable" doesn't conform to the table type of the parameter.
    The statement has been terminated.
           Number=2627
           Procedure=""
           Server=PC-STAGE\SQL2008R2
           Source=.Net SqlClient Data Provider
           State=2
           StackTrace:
                in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
                in System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
                in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
                in System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
                in System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
                in System.Data.SqlClient.SqlDataReader.get_MetaData()
                in System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                in System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
                in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
                in System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                in System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
                in System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
                in System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
                in Microsoft.Synchronization.Data.SqlServer.SqlChangeHandler.ExecuteTVPCommand(IDbCommand cmd, DataTable applyTable, DataTable failedRows)
           InnerException:

    What can be the reason?

    Tuesday, July 31, 2012 6:47 AM
  • the DataRow has a RowState property which indicates the Insert, Update or Delete status of the row being synched.

    see: http://jtabadero.wordpress.com/2011/03/07/manipulating-the-change-dataset-in-sync-fx/

    If you're just adding rows to the DataTable, then they will all be flagged as Inserts. you're losing updates and deletes.


    • Edited by JuneT Tuesday, July 31, 2012 7:54 AM
    • Marked as answer by ufobm Tuesday, July 31, 2012 12:25 PM
    Tuesday, July 31, 2012 7:52 AM
  • the DataRow has a RowState property which indicates the Insert, Update or Delete status of the row being synched.

    see: http://jtabadero.wordpress.com/2011/03/07/manipulating-the-change-dataset-in-sync-fx/

    If you're just adding rows to the DataTable, then they will all be flagged as Inserts. you're losing updates and deletes.


    It's the only difference? Unluckly there's not a "clone" method for a row... Am I right? I'm using the importRow method of the DataTable for now, that imports both data and state... But a better solution would be appreciated!

    Indeed with the actual solution I need to create a new DataTable, import the rows and then add the new table to the DataSet and delete the previous one!

    • Edited by ufobm Tuesday, July 31, 2012 12:31 PM
    Tuesday, July 31, 2012 12:29 PM