asked on

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

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

appari

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

SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ericwong27

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)

Sancler

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

ASKER CERTIFIED SOLUTION

proten

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jayadev Nair

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

proten

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.

apuma

ASKER

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.

apuma

ASKER

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