Solved

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

Posted on 2006-10-31
9
2,669 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
0
Comment
Question by:apuma
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 39

Expert Comment

by:appari
ID: 17848036
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


0
 
LVL 39

Assisted Solution

by:appari
appari earned 150 total points
ID: 17848037
small change

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

Expert Comment

by:ericwong27
ID: 17848202

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)
0
 
LVL 34

Expert Comment

by:Sancler
ID: 17848630
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
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 5

Accepted Solution

by:
proten earned 250 total points
ID: 17849642
The way I usually copy one data row to another is use the ItemArray property:

dr1.ItemArray = dr2.ItemArray
0
 
LVL 6

Assisted Solution

by:Jayadev Nair
Jayadev Nair earned 100 total points
ID: 17849786
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.
0
 
LVL 5

Expert Comment

by:proten
ID: 17849985
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.  
0
 
LVL 1

Author Comment

by:apuma
ID: 17851349
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.




0
 
LVL 1

Author Comment

by:apuma
ID: 17851647
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now