CODEWITHSUNDEEP
c#sql-serverwinformsexception
Touseef Khan
Touseef Khan

Reputation: 443

exception on inserting single quote in c#

I am facing problem that. when I insert single quote in text field. on insertion it give exception of incorrect syntax near that particular field. why is it? does single quote has special meaning to sqlserver?

what if user what to enter word like don't , it's, or sometime by mistake enter single quote in start then it give exception. is there any sol to handle this? if single quote has issue with sqlserver.. then how to deal it?

Upvotes: 1

Views: 5486

Answers (5)

Pankaj
Pankaj

Reputation: 10115

using (System.Data.SqlClient.SqlConnection con = new SqlConnection("YourConnection string")) { 
    con.Open(); 
    SqlCommand cmd = new SqlCommand(); 
    string expression = "(newsequentiali'd())"; 
    cmd.CommandType = CommandType.StoredProcedure; 
    cmd.CommandText = "Your Stored Procedure"; 
    cmd.Parameters.Add("Your Parameter Name", 
                SqlDbType.VarChar).Value = expression;    
    cmd.Connection = con; 
    using (IDataReader dr = cmd.ExecuteReader()) 
    { 
        if (dr.Read()) 
        { 
        } 
    } 
}

Upvotes: 0

Will A
Will A

Reputation: 25008

SQL Server strings are enclosed (typically) in single quotes, so a single quote within a string will result in an error if you don't escape it prior to it being INSERTed.

Single quotes simply need to be doubled up, so the string Will''s World would result in Will's World making it's way into the data.

Upvotes: 6

FIre Panda
FIre Panda

Reputation: 6637

In SqlServer, if you want to insert string with quotes, use quotes twice before and after the string. For example you want to insert 'Hello', so insert it like '''Hello''' provided the field you want to insert in has string datatype like varchar etc.

Upvotes: 0

Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50752

use SqlParameter instead of string concatenation

This kind of expressions is worst thing you can do in your code, because at first you will have problem with data type convertion, and second the doors of Sql Injection is opem for hackers 

string someQuery = "Select * from SomeTbl Where SomeTbl.SomeColumn = '" + tbSomeBox.Text+ "'" ;

Instead of that just use this

string someQuery = "Select * from SomeTbl Where SomeTbl.SomeColumn = @param";
SqlCommand someCommand = new SqlCommand(someQuery, conn);
someCommand.AddParams("@param",tbSomeBox.Text);
...

Hope this helps

Upvotes: 9

CodeMan
CodeMan

Reputation: 1415

You will need to escape single quotes in SQL statements.

For example:

'don''t'

Upvotes: 0

Related Questions