Escape block bracket in .Select

Question

I am trying filter the data out of a DataTable using the Select function

Dim dr() As DataRow = Nothing
Dim DtDataSource As DataTable

'value is populated here in DataTable (12,000 Records)

dr = DtDataSource.Select("[Name] like '[PAPER]*'") 'Gives Error

The error is thrown at the last line where I try to get data from the DataTable because I am using block brackets '[' and ']'.

In C# we could use the following code to get around it

dr = DtDataSource.Select("[Name] like '\[PAPER\]*' ESCAPE '\\'");

But if I use the same concept here, it gives the following error

Syntax error: Missing operand after 'ESCAPE' operator.

Now is there anyway through which I can get the data out of DataTable which start with the phrase [PAPER]?

try looking at this: https://www.tutorialspoint.com/vb.net/vb.net_character_escapes.htm — Mederic, May 19 '17 at 12:40
@Mederic thanks for the reply but i do not understand how a block bracket can be used in any of the cases listed in the website — Agent_Spock, May 19 '17 at 12:45
Take a look at this question http://stackoverflow.com/questions/10855/linq-query-on-a-datatable, it migth help you solving your problem — Quima, May 19 '17 at 12:46
sorry but i am new to VB.NET, can anyone show me how to use it in my case? — Agent_Spock, May 19 '17 at 12:48
Are you certain that the [] were preserved when the table column was named? — CDove, May 19 '17 at 12:55
@CDove yes sir the record was populated from SQL to the DataTable and then i use [] to search for the records — Agent_Spock, May 19 '17 at 12:57

Steve · Accepted Answer · 2017-05-19T13:29:34.233

2

You should Escape the square brackets in this way

 dr = DtDataSource.Select("[Name] like '[[]PAPER[]]%'")

An alternative is to use LINQ to extract the information from your DataTable

 rows = DtDataSource.AsEnumerable().
                     Where(Function(row) row.Field(OfType String)("Name").
                     StartsWith("[PAPER]")

 .... work with the datarow collection
 For Each row as DataRow in rows
    .....
 Next

edited May 19 '17 at 13:29

answered May 19 '17 at 12:54

Steve

213,761
22
232
286

Not working? Please explain, same error message or it simply doesn't return the records expected? – Steve May 19 '17 at 12:58
By the way, you say _records with ends with the word [PAPER]_ but, as is, the query searches records that START with the word [PAPER], if you want [PAPER] at the end of the column you should move the wildcard before [PAPER] – Steve May 19 '17 at 13:00
sorry by mistake i wrote at the end – Agent_Spock May 19 '17 at 13:02
yeah that did the trick. By mistake i used the wrong sequence of brackets – Agent_Spock May 19 '17 at 13:15
Good, however, if the search of these records could be isolated in the context I suggest to change the query that populates the DataTable to work only with the searched records returned by the database engine and not with runtime subselection of the DataTable content. – Steve May 19 '17 at 13:17

Jonathan Applebaum · Answer 2 · 2017-05-19T13:31:26.817

did you notice that your DataRow is an array of data rows? you declare: Dim dr() As DataRow = Nothing and not Dim dr As DataRow = Nothing (just making sure).
if you need a multiple rows that contains the string [Paper] use the third example and extract all the rows to a new DataTable.
if you need just one distinct row use the first \ second example. you can achieve the same result with linq\ lambda expiration:

Public Sub ex2()
    Dim dr As DataRow = Nothing
    Dim DtDataSource As DataTable

    ' 1. example with linq'
    dr = (From row In DtDataSource.AsEnumerable()
          Where row.Field(Of String)("Name").Contains("[PAPER]") Select row).FirstOrDefault()
    ' 2. example with lambda expiration'    
    dr = DtDataSource.AsEnumerable().Where(Function(x) x.Field(Of String)("Name").Contains("[PAPER]")).Select(Function(y) y).FirstOrDefault()

    ' 3. but if you want all the rows that containes [Paper] you can do it like that:'
    Dim tempDt As DataTable = DtDataSource.AsEnumerable().Where(Function(x) x.Field(Of String)("Name").Contains("[PAPER]")).Select(Function(y) y).CopyToDataTable()
End Sub

Of course also using Linq is a good approach, but please notice "SingleOrDefault" will throw if there are more than one matching record (very probable with a LIKE statement). Perhaps it is better FirstOrDefault. In any case Single/FirstOrDefault could be used with a lambda expression removing the necessity of the Where. Finally in the linq-to-objects examples you don't need to Select — Steve, May 19 '17 at 13:25
@Steve thanks probably `FirstOrDefault` is better, edited the answer. also example 3 is for multiple rows (very probable with a LIKE statement) — Jonathan Applebaum, May 19 '17 at 13:33

Escape block bracket in .Select

2 Answers2