SQL Query Problem in Inline Query in Asp.net

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" } }
    }