My sql table structure is this
ID DataName
1 Lipsum lorem
3 lipsum's lorem
My inline query in asp.net is that
select * from table where DataName like 'lipsum's lorem'
It gives the following errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''.
I don't want to create a stored procedure to prevent this, I want a solution to this using inline queries.
You need to escape the ' in 'lipsum's lorem'
'lipsum''s lorem'
But the real fix is to a use parameterised query, to prevent SQL injection.
SqlCommand.CommandText = "SELECT * FROM Table WHERE DataName = @DataName";
In your command object add a parameter for @DataName with its value.
SqlCommand.Parameters.AddWithValue("@DataName", Value);
Your query should look like...
select * from table where DataName like 'lipsum''s lorem'
As you need to escape the ' to ' to get it to work.
For more details check out this link: http://blog.sqlauthority.com/2008/02/17/sql-server-how-to-escape-single-quotes-fix-error-105-unclosed-quotation-mark-after-the-character-string/
If you use Command Parameters, check this link out:
You should replace the Single Quote with Double Quote
String.replace("'", "''")
That means put the search string in the string variable and replace the single quote with double quote
string str = "lipsum's lorem"
str.Replace("'", "''")
ID DataName
1 Lipsum lorem
3 lipsum's lorem
select * from table where DataName like 'lipsum''s lorem'
or
select * from table where DataName='lipsum''s lorem'
select * from table where DataName like'lipsum%' (To select both rows)
Use a parameterized query.
var command = new SqlCommand()
{
CommandText = "SELECT * FROM [table] WHERE DataName = @dataName",
Parameters = { { "@dataName", "lipsum's lorem" } }
}
'lipsum's lorem'come from user supplied input that you are concatenating into the query?