?
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
Medium Priority
?
2,689 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 600 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 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