While working on an e-commerce project recently, I came across a situation where I had to input a string, like, Exclusive Men's Wear in a textbox and store the value as it is in an SQL Server table. Now, look at the string value carefully; there is is a single quote within the string Men's.
The INSERT statement, after the app picks up the values from the textbox looked like this.
INSERT INTO myTable (Some_ID, Category) VALUES (6, 'Exclusive Men's Wear')SQL Server will throw an error, since it does not see a closing bracket after the first single quote (i.e., after the letter n in Men's). Although, it’s a very common issue and I have faced it many times before, I made a mistake. The mistake was, not doubling it up.
The above statement should be,
INSERT INTO myTable (Some_ID, Category) VALUES (6, 'Exclusive Men''s Wear')See, now the string Men''s has two single quotes. This is fine. SQL Server will execute the statement and will insert the values in the table.
Remember, it is not a double quote, but two single quotes.
In case, you are extracting the values from an <input> box or a textbox using Asp.Net, you must replace the single quote with two quotes. Use the replace() method in Asp.Net to replace the quote.
The Textbox
<asp:TextBox ID="category" runat="server"></asp:TextBox>
C# Code
category.Text.Replace("'", "''").Trim();Visual Basic Code
Trim(Replace(category.Value, "'", "''"))