locked
Insert into Apostrophe RRS feed

  • Question

  • Hi ,
    After several times to send this text ( Jon's school ) to database sql ... using c# code .. but the failing is result .
    the fieldName  I send is :  " _Name"  and the Data Type field is nvarchar .. Length field is  50 .. and the syntex is :
     " insert into   "  + TableName +  "  ( _Name) values ('Jon's school') "
    thanks.
    Ashraf

     

        

     

     

    Monday, April 27, 2009 5:16 PM

Answers

  • Use the parameter classes rather then trying to insert the value directly into the SQL string.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by Harry Zhu Wednesday, April 29, 2009 4:00 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Monday, April 27, 2009 6:04 PM
  • Double the apostrophe. 

    "insert into " + TableName + " (_Name) values ('Jon''s school')"
    David Morton - http://blog.davemorton.net/
    • Proposed as answer by Harry Zhu Wednesday, April 29, 2009 4:00 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Monday, April 27, 2009 5:21 PM
    Moderator
  • You seem to miss the table name in your last SQL. Assuming you have

    CREATE TABLE Foo (
     job_id int,
     job_desc varchar(50),
     min_lvl int,
     max_lvl int
    )


    This works fine:
    insert into Foo (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)
    • Proposed as answer by codevanced Friday, May 1, 2009 10:28 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Friday, May 1, 2009 10:28 AM
  • Yours:

      string
    sql = " insert into (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)" ;

    Solved:



    string sql = " insert into  TABLENAME  (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)" ;

    You forgot table's name in query.
    • Proposed as answer by Harry Zhu Monday, May 4, 2009 2:21 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Saturday, May 2, 2009 12:09 AM

All replies

  • Double the apostrophe. 

    "insert into " + TableName + " (_Name) values ('Jon''s school')"
    David Morton - http://blog.davemorton.net/
    • Proposed as answer by Harry Zhu Wednesday, April 29, 2009 4:00 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Monday, April 27, 2009 5:21 PM
    Moderator
  • Use the parameter classes rather then trying to insert the value directly into the SQL string.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by Harry Zhu Wednesday, April 29, 2009 4:00 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Monday, April 27, 2009 6:04 PM
  • Pls . Note :
    I use double apostrophes as your advice but still same problem ...
    Can pls u using this code and advice me to the solve :

    using

    System;

    using

    System.Collections.Generic;

    using

    System.ComponentModel;

    using

    System.Data;

    using

    System.Data.OleDb;

    using

    System.Drawing;

    using

    System.Text;

    using

    System.Windows.Forms;

    namespace

    ProblemInsert

     


    public

     

    partial class Form1 : Form

    {

     

    public Form1()

     


    private

    {

    InitializeComponent();

    }

     


    private

    {

     

    void button1_Click(object sender, EventArgs e)

     



    OleDbConnection

    conn = new OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=pubs;Data Source="+ ServerName );

     

     

    string sql = " insert into (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)";

     

     

    OleDbCommand cmd = new OleDbCommand(sql, conn);

     

    conn.Open();

    cmd.ExecuteNonQuery();

    conn.Close();
    }
    }

    }

    {

    Friday, May 1, 2009 10:04 AM
  • John is right. Use cmd.Parameters.AddWithValue to avoid the problem of apostrophe. 
    Ganesh Ranganathan
    [Please mark the post as answer if you find it helpful]
    Friday, May 1, 2009 10:22 AM
  • You seem to miss the table name in your last SQL. Assuming you have

    CREATE TABLE Foo (
     job_id int,
     job_desc varchar(50),
     min_lvl int,
     max_lvl int
    )


    This works fine:
    insert into Foo (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)
    • Proposed as answer by codevanced Friday, May 1, 2009 10:28 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Friday, May 1, 2009 10:28 AM
  • Yours:

      string
    sql = " insert into (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)" ;

    Solved:



    string sql = " insert into  TABLENAME  (job_id,job_desc,min_lvl,max_lvl) values (15,'john''school',25,25)" ;

    You forgot table's name in query.
    • Proposed as answer by Harry Zhu Monday, May 4, 2009 2:21 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    Saturday, May 2, 2009 12:09 AM
  • string sql = "INSERT INTO TableName (job_id,  job_desc, min_lvl, max_lvl) VALUES (@JobId, @JobDesc, @MinLvl, @MaxLvl)";

    Then use the parameter classes.

    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    • Proposed as answer by Harry Zhu Monday, May 4, 2009 2:21 AM
    • Marked as answer by Harry Zhu Tuesday, May 5, 2009 8:07 AM
    • Unmarked as answer by Ashraf Khalifah Sunday, May 31, 2009 4:28 AM
    • Marked as answer by Ashraf Khalifah Sunday, May 31, 2009 4:28 AM
    • Unmarked as answer by Ashraf Khalifah Sunday, May 31, 2009 4:28 AM
    Saturday, May 2, 2009 1:49 AM
  • //Example of a parameterized query

    string sql = "INSERT INTO TableName (job_id,  job_desc, min_lvl, max_lvl) VALUES (@JobId, @JobDesc, @MinLvl, @MaxLvl)";
    using (SqlCommand cmd = new SqlCommand(sql, conn))
    {
        cmd.Parameters.AddWithValue("@JobID", 15);
        cmd.Parameters.AddWithValue("@JobDescription", "Computer Programmer");
        cmd.Parameters.AddWithValue("@MinLvl", 25);
        cmd.Parameters.AddWithValue("@MaxLvl", 35);
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

    Hope this helps.
    John Grove - TFD Group, Senior Software Engineer, EI Division, http://www.tfdg.com
    Sunday, May 31, 2009 5:29 PM