Solved

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

Posted on 2015-01-19
10
843 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
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…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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