How to handle single quotes in sql
ADO.NET facilitates seamless and efficient data exchange between relational and non-relational systems by using a standardized set of components. While we are inserting data into the database table we came across the messages like..
System.Data.SqlClient.SqlException: Incorrect syntax near ...
Incorrect syntax near '..'. Unclosed quotation mark after the character string ')'.
The error occurs when attempting to insert a single quoted character in SQL statements. For example, when trying to insert a string like "Microsoft's", the system displays the aforementioned error message due to the presence of a single quote within the SQL statement.
The solution for this problem is to replace any single quote with two quotes like "Microsoft''s" .
insert into tablename (field1) values('Microsoft''s')
To avoid the need to manually add an additional single quote to the string each time, we can implement a function that replaces the string with two single quotes. This function will automatically handle the conversion and ensure that the string is properly formatted for SQL statements.
public string convertQuotes(string str)
{
return str.Replace("'", "''");
}
Full Source
using System;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connetionString = null;
SqlConnection connection ;
SqlDataAdapter adapter = new SqlDataAdapter();
string sql = null;
string field1 = "Microsoft's";
string field2 = "C#";
connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
connection = new SqlConnection(connetionString);
sql = "insert into newtest (field1,field2) values('" + convertQuotes(field1) + "','" + convertQuotes(field2) + "')";
try
{
connection.Open();
adapter.InsertCommand = new SqlCommand(sql, connection);
adapter.InsertCommand.ExecuteNonQuery();
MessageBox.Show ("Row inserted !! ");
}
catch (Exception ex)
{
MessageBox.Show (ex.ToString());
}
}
public string convertQuotes(string str)
{
return str.Replace("'", "''");
}
}
}