locked
Load DataRow from DataTable to Typed DataTable RRS feed

  • Question

  • I have an application were I have to work with subset data tables of a strongly typed data table (dsBatches).  To get a subset, I clone the typed data table, filter & sort the typed data table, then use the DataView.ToTable method to create the subset table (dtSubset).  Because I'm working with subsets I have to keep the data from dtSubset and dsBatches in-sync.  So I created a RowChanged event for dtSubset which will load the changed row into the typed data table.  But I'm getting this exception "Unable to cast object of type 'NBB_BatchesRow' to type 'System.IConvertible'.Couldn't store <Nutrishy.dsBatches+NBB_BatchesRow> in BatchID Column.  Expected type is Int32." on this line of code:  Me.dsBatches.NBB_Batches.LoadDataRow(newRow, False).

    1.)  Does this seem like an appropriate way to keep data from dtSubset and dsBatches in-sync or is there a better way?

    2.)  How can I fix this exception?

        Private Function GetSubset() As String
    
            Try
    
                ' apply the current filter and sort strings
                With Me.dv
                    .Sort = Me.strSort
                    .RowFilter = Me.strFilter
                End With
    
                ' Create New datatable to prevent old datatable events from firing for better performance.
                ' Use the Clone method to apply the master table primary key (and other schemas) to the new subset table
                Me.dtSubset = New DataTable
                Me.dtSubset = Me.dsBatches.Clone
    
                ' Get a subset data table from the newly sorted & filtered DataView and use the DataTable as the DataGridView.DataSource
                ' We can't use the DataView as the DataSource because we don't want rows to be filtered out or sorting automatically if the user modifies datarows that don't match the current Filter and Sort strings
                Me.dtSubset.Merge(dv.ToTable)
    
                ' if the master table contains row errors we need to transfer the error text to the subset table
                If dsBatches.HasErrors Then
    
                    Dim pkValues As New List(Of Object)
    
                    ' loop thru each datarow that contains an error
                    For Each dr As DataRow In dsBatches.GetErrors
                        pkValues.Clear()
    
                        ' get the primary key values of the datarow that contains the error
                        For Each key As DataColumn In dsBatches.PrimaryKey
                            pkValues.Add(dr(key.ColumnName))
                        Next
    
                        ' see if dr is in the subset, if so add the error
                        Dim rw As DataRow = Me.dtSubset.Rows.Find(pkValues.ToArray())
                        If rw IsNot Nothing Then rw.RowError = dr.RowError
                    Next dr
                End If
    
                ' AcceptChanges because all rows are marked as Added when using the DataView.ToTable method
                Me.dtSubset.AcceptChanges()
    
                ' set the dgv datasource, this will fire the DataGridView's DataSourceChanged & DataBindingComplete events
                Me.DataGridView.DataSource = Me.dtSubset
    
                Return "Success"
    
            Catch ex As Exception
                Call modPublicProcedures.AppErrorHandler(Me.GetType.Name, System.Reflection.MethodBase.GetCurrentMethod().Name, ex)
                Return "Failed"
            End Try
    
        End Function
    
    
        Private Sub dtSubset_RowChanged(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs) Handles dtSubset.RowChanged
    
            ' update the master table with the update
            Me.dsBatches.NBB_Batches.BeginLoadData()
            Dim newRow() As DataRow = Me.dtSubset.Select(String.Format("{0} = {1}", Me.dsBatches.NBB_Batches.BatchIDColumn.ColumnName, e.Row(Me.dsBatches.NBB_Batches.BatchIDColumn.Ordinal)))
            Me.dsBatches.NBB_Batches.LoadDataRow(newRow, False)
            Me.dsBatches.NBB_Batches.EndLoadData()
    
            ' try to update the database here
            If e.Action = DataRowAction.Change Then
                If CInt(Me.adapter.Update(Me.dsBatches)) = 1 Then
                    ' if successful
                    e.Row.AcceptChanges()
                Else
                    MsgBox("Unsuccessful update")
                    e.Row.RejectChanges()
                End If
            End If
    
        End Sub
    


    Ryan

    Monday, August 27, 2012 1:49 PM

Answers

  • Hi Ryan,

    I suggest you just use the dataView, if you just use the sub set as datasource. I made a simple sample:

        Dim dateDt As New DataTable
        Private Sub DataViewDatasourceForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            dateDt.Columns.Add(New DataColumn("Date1", Type.GetType("System.Int32")))
            dateDt.Columns.Add(New DataColumn("Date2", Type.GetType("System.Int32")))
            dateDt.Columns.Add(New DataColumn("DiffValue", Type.GetType("System.Decimal")))
            For i = 0 To 10
                dateDt.Rows.Add(i, (i - 5), 0)
            Next
    
            Dim view As DataView = dateDt.DefaultView
            view.RowFilter = "Date2 > 0"
            Me.DataGridView1.DataSource = view
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            For Each dr As DataRow In dateDt.Rows
                For Each c In dr.ItemArray
                    Console.Write(c & "  ")
                Next
                Console.WriteLine()
            Next
        End Sub

    This changes in the dataview are stored in the datatable immediately.

    If you keep your way, please don't use the importrow method, it produces the duplicate BatchID.

    Please find out the corresponding row in main table, and change each cell with the corresponding value from subset table.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Ryan0827 Friday, August 31, 2012 2:10 PM
    Tuesday, August 28, 2012 9:41 AM
  • Mike,

    I can't use a DataView and here is why.  If I execute this:

    Dim dv As DataView = dtMaster.DefaultView
    dv.RowFilter = "Column1 = 'A'"
    Me.DataGridView1.DataSource = dv

    Then the user edits a cell value in Column1 from "A" to "B", then that row is automatically removed from the DataGridView.  This is not very user friendly.  This is why I have to use a DataView to do the filter, then convert the DataView to a DataTable, then use the DataTable as the DataGridView.DataSource to eliminate this issue.

    But here is the fix to my issue.  I stopped using the ImportRow method as you suggested.

        Private Sub dtSubset_RowChanged(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs) Handles dtSubset.RowChanged
    
            ' try to update the database here
            If e.Action = DataRowAction.Change Then
    
                '' update the master table with the update
                Me.dsBatches.NBB_Batches.BeginLoadData()
                Me.dsBatches.NBB_Batches.LoadDataRow(e.Row.ItemArray, False)
                Me.dsBatches.NBB_Batches.EndLoadData()
    
                If CInt(Me.adapter.Update(Me.dsBatches.NBB_Batches)) = 1 Then
                    ' if successful
                    e.Row.AcceptChanges()
                    Me.dfsBatches.MasterDataTable.Merge(e.Row.Table, False)
                Else
                    MsgBox("Unsuccessful update")
                    e.Row.RejectChanges()
                End If
            End If
    
        End Sub

    Thanks again,


    Ryan

    • Marked as answer by Ryan0827 Friday, August 31, 2012 2:10 PM
    Friday, August 31, 2012 2:10 PM

All replies

  • Hi Ryan,

    I suggest you just use the dataView, if you just use the sub set as datasource. I made a simple sample:

        Dim dateDt As New DataTable
        Private Sub DataViewDatasourceForm_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            dateDt.Columns.Add(New DataColumn("Date1", Type.GetType("System.Int32")))
            dateDt.Columns.Add(New DataColumn("Date2", Type.GetType("System.Int32")))
            dateDt.Columns.Add(New DataColumn("DiffValue", Type.GetType("System.Decimal")))
            For i = 0 To 10
                dateDt.Rows.Add(i, (i - 5), 0)
            Next
    
            Dim view As DataView = dateDt.DefaultView
            view.RowFilter = "Date2 > 0"
            Me.DataGridView1.DataSource = view
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            For Each dr As DataRow In dateDt.Rows
                For Each c In dr.ItemArray
                    Console.Write(c & "  ")
                Next
                Console.WriteLine()
            Next
        End Sub

    This changes in the dataview are stored in the datatable immediately.

    If you keep your way, please don't use the importrow method, it produces the duplicate BatchID.

    Please find out the corresponding row in main table, and change each cell with the corresponding value from subset table.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Ryan0827 Friday, August 31, 2012 2:10 PM
    Tuesday, August 28, 2012 9:41 AM
  • Mike,

    I can't use a DataView and here is why.  If I execute this:

    Dim dv As DataView = dtMaster.DefaultView
    dv.RowFilter = "Column1 = 'A'"
    Me.DataGridView1.DataSource = dv

    Then the user edits a cell value in Column1 from "A" to "B", then that row is automatically removed from the DataGridView.  This is not very user friendly.  This is why I have to use a DataView to do the filter, then convert the DataView to a DataTable, then use the DataTable as the DataGridView.DataSource to eliminate this issue.

    But here is the fix to my issue.  I stopped using the ImportRow method as you suggested.

        Private Sub dtSubset_RowChanged(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs) Handles dtSubset.RowChanged
    
            ' try to update the database here
            If e.Action = DataRowAction.Change Then
    
                '' update the master table with the update
                Me.dsBatches.NBB_Batches.BeginLoadData()
                Me.dsBatches.NBB_Batches.LoadDataRow(e.Row.ItemArray, False)
                Me.dsBatches.NBB_Batches.EndLoadData()
    
                If CInt(Me.adapter.Update(Me.dsBatches.NBB_Batches)) = 1 Then
                    ' if successful
                    e.Row.AcceptChanges()
                    Me.dfsBatches.MasterDataTable.Merge(e.Row.Table, False)
                Else
                    MsgBox("Unsuccessful update")
                    e.Row.RejectChanges()
                End If
            End If
    
        End Sub

    Thanks again,


    Ryan

    • Marked as answer by Ryan0827 Friday, August 31, 2012 2:10 PM
    Friday, August 31, 2012 2:10 PM