We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How To:  execute a .Select on a dataset(table) and then replace the table with the contents of the .select

apuma
apuma asked
on
Medium Priority
2,777 Views
Last Modified: 2008-01-09
I have tried this a few different ways with temporary datasets( to recieve the dreaded "This column already belongs to a dataset" error)   deleting the dataset object and rebuilding it (same error)   and this is the best way so far that i have found but i get a strange casting exception

 Private Sub LblUnable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LblUnable.Click
        Me.Cursor = Cursors.WaitCursor
        Application.DoEvents()
        Dim dr() As DataRow 'instantiate datarow array
        ReDim dr(dsItems.Tables(0).Select("Used = 0 AND MSRP = 0").Length - 1) 'set dimension
        dsItems.Tables(0).Select("Used = 0 AND MSRP = 0").CopyTo(dr, 0) ' fill array with datarows
        dsItems.Clear() ' clear pre=existing rows
        dsItems.Tables(0).Rows.Add(dr)  'fill with array of  rows  '  line producing the exception'

EX.Message = "Additional information: System.InvalidCastException: Specified cast is not valid.
   at System.Convert.ToInt32(Object value)
   at System.Data.Common.Int32Storage.Set(Int32 record, Object value)
   at System.Data.DataColumn.set_Item(Int32 record, Object value)Couldn't store <System.Data.DataRow> in ItemID Column.  Expected type is Int32."

When i break and try to look at the contents of dr() it throws a "Datarownotpartofatable" (something like that) exception

Theres got to be a better way to do this.
Thx in advance for any suggestions.
A. Puma
Comment
Watch Question

Commented:
try changing the following line

dsItems.Tables(0).Rows.Add(dr)

with

for rowIdx as integer = 0 to in dr.length -1
       dim drSelect as datarow = dr(rowidx)
       dsItems.Tables(0).Rows.Add(drSelect)
next


Commented:
small change

for rowIdx as integer = 0 to dr.length -1
       dim drSelect as datarow = dr(rowidx)
       dsItems.Tables(0).Rows.Add(drSelect)
next

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Commented:

Is you just need to filter out the data, you can simply using dataview

        Dim lDataView As New DataView(dsItems.Tables(0))
        lDataView.RowFilter = "Used = 0 AND MSRP = 0"

        dsItems.Tables.Clear()
        dsItems.Tables.Add(lDataView.ToTable)

Commented:
I think I'd tackle this the other way round.  That is, rather using select to identifying the rows to keep and then trying to copy those out and copy them back, I would identify the rows to remove and then just remove them.

        Dim dv As New DataView
        dv.Table = dsItems.Tables(0)
        dv.RowFilter = "Used <> 0 OR MSRP <> 0"
        For i As Integer = dv.Count - 1 To 0 Step -1
            dv(i).Delete()
        Next
        dsItems.Tables(0).AcceptChanges

Roger
Commented:
The way I usually copy one data row to another is use the ItemArray property:

dr1.ItemArray = dr2.ItemArray
Jayadev NairSr.Software Engineer
Commented:
Hi

Let me point out the reason of exception first.
    DataTable.Rows.Add() expects a datarow or array or objects in a datarow
    i.e. at a time you can only add a single row with that method. You tried to add Array or Rows which threw the exception.
    dsItems.Tables(0).Rows.Add(dr) ' dr is DataRow Array

Query:
Hope you are trying to delete all rows from the table of a dataset which are not matching your criteria.

Suggested Solution:
If the query I understood is right, You can make it by slightly changing your own code:

Dim rows() as System.Data.DataRow = dsItems.Tables(0).Select("Used = 0 AND MSRP = 0")
dsItems.Tables(0).Rows.Clear()
For each row as System.Data.DataRow In rows
  dsItems.Tables(0).Rows.Add(row.ItemArray)
Next


Thanks.

Commented:
Be aware that the select statement is pretty heavy on resources.  It is relatively long to run, especially if you are doing it multiple times.  

Author

Commented:
appari -   that suggestion correctly replaced the rows   but lost all data -  
once this line executes:
dsItems.Clear() ' clear pre=existing rows

then       dr(0).item(0)      Run-time exception thrown : System.Data.RowNotInTableException - This row has been removed from a table and does not have any data.  BeginEdit() will allow creation of new data in this row.      


EricWong -   I want to stay away from dataviews as there is Importing-exporting code and others  that would have to be modified

SanCler -   I agree and my previous code was looping through the rows, checking the condition and removing them.  - it was very slow .  typical application would be deleting 5100 out of 5150 rows (apx)  I figured it would be faster to deal with the 50 staying

compued- I believe you are correct about that exception, appari's code proved that the one row ata time method worked fine.   however the clearing of the dataset those rows "reside" in   looks to be killing the data

Proten-  I am only running one .select   and my other method was running far too slow,  but I aqm trying to increase performance.




Author

Commented:
Found something that works

        Me.Cursor = Cursors.WaitCursor
        Application.DoEvents()
        Dim dr() As DataRow
        Dim dstemp As New DataSet
        ReDim dr(dsItems.Tables(0).Select("Used = 0 AND MSRP = 0").Length - 1)
        dsItems.Tables(0).Select("Used = 0 AND MSRP = 0").CopyTo(dr, 0)
        dstemp = dsItems.Clone
        Dim i As Integer
        For i = 0 To dr.Length - 1
            dstemp.Tables(0).Rows.Add(dr(i).ItemArray)
        Next
        dsItems.Clear()
        For i = 0 To dstemp.Tables(0).Rows.Count - 1
            dsItems.Tables(0).Rows.Add(dstemp.Tables(0).Rows(i).ItemArray)
        Next
        dstemp = Nothing
        Me.Cursor = Cursors.Default
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.