Datatable.select Error In Like Operator
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow error in like operator the string pattern is invalid c# the company Business Learn more about hiring developers or posting ads with us Stack
Error In Like Operator The String Pattern ' 0-9 ' Is Invalid
Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error in Like operator and escaping special characters up vote 1 down vote favorite I'm getting the exception: Error in Like operator: the string pattern 'Blah [Blah Blah]' is invalid. It's occuring where I'm using a datatable's .Select method like so: DataRow[] rows = table.Select("X Like '" + y + "'"); I've seen from reading other posts that as suspected, it's the square brackets. The posts I've read say to escape the squares brackets but most of them focus on the source of the problem being a hard coded string the developer can easily escape. I'm reading the data in from a database. I could do something like y.replace("[", "\[") which I'm guessing would fix the issue for square brackets. But then I'm wondering, what if elsewhere in the DB there is a string with an * etc. How do others deal with this? Can I call something that will make a string safe by escaping all possibly illegal characters or is it a case of manually replacing them with escaped equivalents? Thanks Simon c# escaping character like share|improve this question asked Oct 28 '13 at 13:47 Simon 921825 1 A parameterized query would solve this problem as well as other, way more serious ones. –Frédéric Hamidi Oct 28 '13 at 13:48 Good point, although I didn't fully explain. The data has previously been read from the Db (using a data reader and populating domain objects). For this particular task (providing data for a third party chart) it needs to be in a datatable so the datatable is constructed in memory and populated from the domain objects properties (via a helper class). The select is being done during this process: it does a select to see if there is already a row in the datatable for a given item oneach ite
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up DataTable select method with single quote http://stackoverflow.com/questions/19636483/error-in-like-operator-and-escaping-special-characters conflict C# up vote 1 down vote favorite I recently found when I do a LINQ select under a field that contains an apostrophe, it makes my application to throw an exception. DataRow[] newDr = this.ds.Tables["TableName"].Select("Name = '" + drData["Name"].ToString() + "'"); If drData["Name"] = "The business's money" I got an exception of "Syntax error: Missing operand after 'S' operator " Can anyone tell me how http://stackoverflow.com/questions/18663605/datatable-select-method-with-single-quote-conflict-c-sharp to preserve it and no replace it or remove it, please? c# linq select datarow apostrophe share|improve this question edited Sep 6 '13 at 18:02 asked Sep 6 '13 at 17:45 Maximus Decimus 1,02341638 1 This is a textbook example of what happens when you do not sanitize user input –lc. Sep 6 '13 at 17:48 FWIW, the documentation refers to this page explaining exactly how and what to escape –lc. Sep 6 '13 at 17:53 @lc.: there's nothing wrong with the input. –siride May 25 '15 at 22:58 @siride Technically, yes, there is nothing "wrong" with the input, and it may very well be within the expected range of valid input (on the design end at least). But blindly executing it in a query transforms this seemingly innocuous input to have disastrous effects. However you slice it, you need to take care the string does not contain any characters that would be misinterpreted, which IMHO is all part of sanitization process. Whether this means disallowing ' characters entirely, encoding them, or escaping them is entirely up to the architecture/business design. –lc. May 26 '15 at 0:57 add a co
to Page... Thread Tools Display Modes Aug 13th, 2013, 10:00 AM #1 mykhayl Newbie http://www.programmingforums.org/post246090.html Join Date: Feb 2013 Location: Milano Posts: 8 Rep Power: 0 DataTable Select method error Hi guys, i have a little problem with Select method of http://www.xtremevbtalk.com/-net-general/324535-datatable-select-operator.html DataTable. I suppose it's a bug but every information is appreciated. With this code: (Toggle Plain Text) DataTable t = new DataTable(); t.Columns.Add("one"); t.Rows.Add("hello"); t.Select("one like '%h%l%o%'"); error in DataTable t = new DataTable(); t.Columns.Add("one"); t.Rows.Add("hello"); t.Select("one like '%h%l%o%'"); In the last line of code we have a simple "like". This line throw an EvaluateException with message Additional information: Error in Like operator: the string pattern '%h%l%o%' is invalid. In the other tests, every "like query" with % in the middle of the pattern error in like throw this exception Why? (Tested of .NET 3.5, .NET 4) __________________ "There's no point in being exact about something if you don't even know what you're talking about." JOHN VON NEUMANN mykhayl View Public Profile Find More Posts by mykhayl Aug 13th, 2013, 12:36 PM #2 Momerath Programmer Join Date: Feb 2011 Posts: 44 Rep Power: 0 Re: DataTable Select method error Try changing your second line to (Toggle Plain Text) t.Columns.Add(new DataColum("one", typeof(String))); t.Columns.Add(new DataColum("one", typeof(String))); Momerath View Public Profile Find More Posts by Momerath Aug 13th, 2013, 1:38 PM #3 mykhayl Newbie Join Date: Feb 2013 Location: Milano Posts: 8 Rep Power: 0 Re: DataTable Select method error With your solution I have the same error, because by default the datacolumn is of string type. __________________ "There's no point in being exact about something if you don't even know what you're talking about." JOHN VON NEUMANN mykhayl View Public Profile
Express editions) > .NET General > Problem with DataTable.Select and LIKE operator Thread Tools Display Modes #1 08-03-2012, 07:56 AM VBobCat Freshman Join Date: Aug 2009 Location: São Paulo, Brazil Posts: 38 Problem with DataTable.Select and LIKE operator Hello People! My application has several ComboBox whose DataSource properties are always DataTable with two columns, one contains the PrimaryKey, and its name ("PK") is set in ComboBox.ValueMember property, and the other contains text descriptions, and its name ("ITEM") is set in ComboBox.DisplayMember property. So far, so good. Well, the lists of options are huge, so I created an auxiliary DialogBox which the user can open for each ComboBox. This DialogBox has a TextBox and a ListBox, and OK and Cancel buttons as well. The ListBox.DataSource is initially set to be the same as the ComboBox in which the user called for help. My intention is to provide a list whose content narrows as the user inputs some text, in order to ease the search for one specific item. For that, I did this: Code: Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged Dim Filtro As String = "ITEM like '%" + TextBox1.Text.Trim.ToUpper & "%'" Try ListBox1.DataSource = ListaBase.Select(Filtro).CopyToDataTable Catch ex As Exception ListBox1.DataSource = ListaBase End Try If ListBox1.Items.Count > 1 Then ListBox1.ClearSelected() End Sub * ListaBase is the same datatable provided to be the DataSource for that paticular ComboBox. This works, as long as I keep the filter as simple as that: '%(what the user typed)%', and the list will narrow to show only entries whose description contains the exact typed expression, whatever stands before and after. It is already some help, but what I really wanted to have is this: - if the user types more than one word, it will find descriptions even if there are other words between those typed (it is good enough if it matches only when the words appear in the same order, for there is a sense in it); - if there are diacritics involved (they are common in Portuguese), the search will match near correspondences, for instance: if description has the word "execução" and user types "execucao", they match; and vice-versa, that is, if user types correctly a word that was put in database without the proper diacritics. For the first accomplishment, I tried replacing each space in user's typ