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

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

chokkaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
x77Connect With a Mentor Commented:
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
 
x77Commented:
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
 
chokkaAuthor Commented:
Can you please help to frame the syntax. I didnt get , by your explaination.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
chokkaAuthor Commented:
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
 
x77Commented:
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
 
louisfrCommented:
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
 
chokkaAuthor Commented:
@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
 
x77Commented:
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
 
chokkaAuthor Commented:
Thanks @x77, i have to test it and update the comment
0
 
chokkaAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.