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,676 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studios 1 89
Iteration Help (Asp.net VB) 5 37
How to open a different table using the same bindingsource - vb.net 3 63
Find date of 2nd Thursday of each month 3 34
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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