Net-informations.com

How to handle single quotes in sql

ADO.NET provides database connectivity between relational and non-relational systems through a common set of components. While we are inserting data into the database table we came across the messages like..

Incorrect syntax near '..'. Unclosed quotation mark after the character string ')'.

The error displaying because we are try to insert single quoted character using in sql statements. For ex: We want to insert a string like "Microsoft's" , the system shows the above error messages, because we are trying to insert a single quoted character using in 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')

For avoiding each time adding another single quote to the string , here we implement a function to replace the string with two single quotes.






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("'", "''");
        }

    }
}





net-informations.com (C) 2019    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.
SiteMap  | Terms  | About