Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1037
  • Last Modified:

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

0
chokka
Asked:
chokka
  • 5
  • 4
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
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:
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

Featured Post

Independent Software Vendors: 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!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now