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,672 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

867 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

14 Experts available now in Live!

Get 1:1 Help Now