CODEWITHSUNDEEP
c#sqlasp.netsql-server
user123456
user123456

Reputation: 17

Cannot insert text having ' (apostrophe) into SQL Server table

I'm trying to insert text into a SQL table using a textbox:

SqlCommand cmd = new SqlCommand("INSERT INTO Book(Title) VALUES ('" + textBoxTitle.Text + "','" +
"')", conn);

But if text contains apostrophe (ex. You'll...), it's showing: Incorrect syntax near 'll'.

Upvotes: 1

Views: 4654

Answers (2)

user6840341
user6840341

Reputation: 1

As an alternative if you are programming .Net you can use SqlBulkCopy to insert your data without escaping any characters.

https://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

As a bonus it is fast (faster than INSERT if you have a lot to insert).

Upvotes: 0

Soner Gönül
Soner Gönül

Reputation: 98750

First of all, you have two values in your VALUES part. One is textBoxTitle.Text and the other one is ''. But you provided just one column.

If that's true, you should delete '' part in your query. But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

If parameterized queries and statements creates any problem with single quote, use double single quotes for each.

Also use using statement to dispose your database connections and commands.

using(SqlConnection con = new SqlConnection(connString))
using(SqlCommand cmd = con.CreateCommand())
{
    cmd.CommandText = "INSERT INTO Book(Title) VALUES (@title)";
    cmd.Parameters.AddWithValue("@title", textBoxTitle.Text);
    con.Open();
    cmd.ExecuteNonQuery();
}

Upvotes: 3

Related Questions