Solved

Filter multiple row of records  from a DataTable in the Dataset ( Vb.Net / C#)

Posted on 2015-01-19
10
827 Views
Last Modified: 2015-02-11
I have to remove few row of records from p_tree_categories table in my dataset.

My current code filters only one row of record which is not equal to 30003039.

I have to filter 30003039 to 30003045. Alternatively, i have a column name DESCRIPTION in which i have to filter which is not equal to %SCSTV%.

I tried with wild char - %% Dataset is not supporting that feature.
I tried entering multiple row

PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003039'"))
PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003040'"))
PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003041'"))
..

Dataset is not supporting and repeating the records.

How to filter multiple row of records from a datatable in the dataset.





        Dim tempDs As DataSet = ds
        Dim PTDs As DataSet = New DataSet()

        PTDs.Merge(ds.Tables("p_pay_cmp_cursor"))
        PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003039'"))
        PTDs.Merge(ds.Tables("p_prod_override_cursor"))

Open in new window

0
Comment
Question by:chokka
[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
  • 5
  • 4
10 Comments
 
LVL 15

Expert Comment

by:x77
ID: 40558407
DataTable.Select and DataView Filters allow "Like" on Strings.

Sample:      "CodTra Like '%C11%' And Dur>=301 and Dur<=359"
Note: Do'nt support Between, you need use  >= ... and ... <=
It support also query on related tables when there are relations.

It Suppors some function SubString by sample:
SubString : 1 for first char.

 SubString(CodTra,3,3) = 'C11'
0
 

Author Comment

by:chokka
ID: 40558488
Can you please help to frame the syntax. I didnt get , by your explaination.
0
 

Author Comment

by:chokka
ID: 40558493
How to combine these three line of syntax into one line.

I dont want to use - SCR_DPY_SEQ <> '30003039' AND SCR_DPY_SEQ <> '30003040' SCR_DPY_SEQ <> '30003041'

Because, i have to filter atleast 30 numbers which are not consecutive.

PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003039'"))
PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003040'"))
PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003041'"))
 

Open in new window

0
Technology Partners: 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 15

Expert Comment

by:x77
ID: 40558628
You can use    in     clause, and combine with    not   :

  not  (SCR_DPY_SEQ in ('30003039','30003040','30003041'))

Note: you can use expression using  (, ), and, or , not ...
0
 
LVL 11

Expert Comment

by:louisfr
ID: 40559544
You can use Where to filter in several steps
Dim rows As IEnumerable(Of DataRow) = ds.Tables("p_tree_categories").Select()
rows = rows.Where(Function(row) Not row["SCR_DPY_SEQ"].Equals("30003039"))
rows = rows.Where(Function(row) Not row["SCR_DPY_SEQ"].Equals("30003040"))
rows = rows.Where(Function(row) Not row["SCR_DPY_SEQ"].Equals("30003041"))
rows = rows.Where(Function(row) Not row["SCR_DPY_SEQ"].Equals("30003042"))
PTDs.Merge(rows.ToArray())

Open in new window

0
 

Author Comment

by:chokka
ID: 40560017
@x77, i am having a difficulty on writing the syntax. Can you please frame the whole line of syntax.

PTDs.Merge(ds.Tables("p_tree_categories").Select("SCR_DPY_SEQ <> '30003039'"))


not  (SCR_DPY_SEQ in ('30003039','30003040','30003041'))

Open in new window

0
 
LVL 15

Accepted Solution

by:
x77 earned 500 total points
ID: 40560135
Note than parents for not is not required, I put it for clarity.

    Private Sub Prueba() 
        Dim t As New DataTable
        t.Columns.Add("SCR_DPY_SEQ")
        t.Rows.Add("30003039")
        t.Rows.Add("30003039a")
        Debug.Print(t.Select("not  (SCR_DPY_SEQ in ('30003039','30003040','30003041'))").Length.ToString)
        Debug.Print(t.Select("not  SCR_DPY_SEQ in ('30003039','30003040','30003041')").Length.ToString)
    End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:x77
ID: 40560205
I tested the "Not In" clause an it is ok also:
Dim t As New DataTable
t.Columns.Add("SCR_DPY_SEQ")
t.Rows.Add("30003039")
t.Rows.Add("30003040")
t.Rows.Add("30003041")
t.Rows.Add("xxxx")
Dim rows = t.Select("SCR_DPY_SEQ not in ('30003039','30003040','30003041')")
For Each r In rows
    Debug.Print(r(0).ToString)
Next

Open in new window

Result in Debug Window: xxxx
0
 

Author Comment

by:chokka
ID: 40565676
Thanks @x77, i have to test it and update the comment
0
 

Author Closing Comment

by:chokka
ID: 40604816
Thanks
0

Featured Post

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!

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

735 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