Insert into Apostrophe

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

 

    

 

 

Answer 1

Double the apostrophe. 

"insert into " + TableName + " (_Name) values ('Jon''s school')"

---

David Morton - http://blog.davemorton.net/

Answer 2

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

Answer 3

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

}

{

Answer 4

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]

Answer 5

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)

Answer 6

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.

Answer 7

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

Answer 8

//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