Using SQL Search in c# page - problem with fields with ' ' in them
-
Hi I am using inline sql to search a table column. Some of the fields in the column have ' ' in them and whenever these fields are chosen it shows an error 'In correct Syntax near the text that is in the ' '. Is this a problem because it affects the sql syntax in the statement? My code goes something like 'select from table where field is LIKE '%" + name + "%'" Any help would be great. thanks Jamie
-
Hi I am using inline sql to search a table column. Some of the fields in the column have ' ' in them and whenever these fields are chosen it shows an error 'In correct Syntax near the text that is in the ' '. Is this a problem because it affects the sql syntax in the statement? My code goes something like 'select from table where field is LIKE '%" + name + "%'" Any help would be great. thanks Jamie
Could you post a bit of your code? This may help maybe... Regards Sebastian
It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland.
-
Hi I am using inline sql to search a table column. Some of the fields in the column have ' ' in them and whenever these fields are chosen it shows an error 'In correct Syntax near the text that is in the ' '. Is this a problem because it affects the sql syntax in the statement? My code goes something like 'select from table where field is LIKE '%" + name + "%'" Any help would be great. thanks Jamie
Try 'select from table where field is LIKE '%" + name.Replace("'", "''") + "%'" For a better, and safer, solution look into using parameters in your SQL.
-
Hi I am using inline sql to search a table column. Some of the fields in the column have ' ' in them and whenever these fields are chosen it shows an error 'In correct Syntax near the text that is in the ' '. Is this a problem because it affects the sql syntax in the statement? My code goes something like 'select from table where field is LIKE '%" + name + "%'" Any help would be great. thanks Jamie
Oh I see, the value of name can contain ''... DoctorMick already gave the answer. But just one advice: Pay attention to sql injection (http://unixwiz.net/techtips/sql-injection.html[^]). You could use preparedstatements within your code. This will be the better (and more secure) way. Regards Sebastian
It's not a bug, it's a feature! Check out my CodeProject article Permission-by-aspect. Me in Softwareland.
-
Try 'select from table where field is LIKE '%" + name.Replace("'", "''") + "%'" For a better, and safer, solution look into using parameters in your SQL.
Ive replaced the apostrophies and it doesnt show an error message but now it just wont find that field which is weird. The sql statement is searching a table for fields that match the description the user puts in. Is this because the apostrophies are not recognised in the field? thanks OK forget what I said above. I think the problem is that there are breaks in the text in the database field, i.e. a new line so this might be causing the error of not finding it. Is this a known problem when searching database fields that if there is a line break in the field it affects finding data? thanks
modified on Monday, June 29, 2009 8:26 AM
-
Hi I am using inline sql to search a table column. Some of the fields in the column have ' ' in them and whenever these fields are chosen it shows an error 'In correct Syntax near the text that is in the ' '. Is this a problem because it affects the sql syntax in the statement? My code goes something like 'select from table where field is LIKE '%" + name + "%'" Any help would be great. thanks Jamie
Try this
select from table where field is LIKE RTRIM(LTRIM('%" + name + "%'))"
Hope this helps :)
Niladri Biswas