Solved

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

Posted on 2015-01-19
10
901 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

627 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