OleDbException syntax error in insert Statment

Question

static string connStrCheckData = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("test.xlsx") + ";Extended Properties=Excel 12.0;";
 
static string      oledbConnCheckData = new OleDbConnection(connStrCheckData);

string adsName ="MagMall.com - subscription savings on 1,000's of magazines";

OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "insert into  [sheet1$] ( [column1]) values ('" + adsName.ToString().Trim() + "')";
cmd.Connection = oledbConnCheckData;

oledbConnCheckData.Open();

cmd.ExecuteNonQuery();

oledbConnCheckData.Close();

Errro : Syntax error (missing operator) in query expression ''MagMall.com - subscription savings on 1,000's of magazines')'.

Above Error occurs when I tried to insert: "MagMall.com - subscription savings on 1,000's of magazines" word.

Eddie Paz · Accepted Answer · 2012-03-23 06:36:29Z

1

As juergen said, you hve to escape the single apostrophe buy adding an additional one: 1,000''s (not \'). The double apostrophies is the correct way to pass the statement.

answered Mar 23, 2012 at 6:36

Eddie Paz

2,25116 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2012-03-23 06:11:27Z

0

You have to escape the ' in 1,000's like this 1,000\'s

answered Mar 23, 2012 at 6:11

juergen d

205k40 gold badges305 silver badges377 bronze badges

3 Comments

Shailesh Over a year ago

Can hyphen "-" generates any problem . And value of adsName changes every time in loop then how can i applied escape in variable .

Shailesh Over a year ago

I did like adsName.ToString().Trim().Replace("'", "\'") but still giving error .

juergen d Over a year ago

@Shailesh: Try adsName.ToString().Trim().Replace("'", "\\'")

Collectives™ on Stack Overflow

OleDbException syntax error in insert Statment

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related