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.

score 1 · Accepted Answer · answered Mar 23 '12 at 06:36

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 '12 at 06:36

Eddie Paz

2,219
16
11

score 0 · Answer 2 · answered Mar 23 '12 at 06:11

0

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

answered Mar 23 '12 at 06:11

juergen d

201,996
37
293
362

Can hyphen "-" generates any problem . And value of adsName changes every time in loop then how can i applied escape in variable . – Shailesh Mar 23 '12 at 06:15
I did like adsName.ToString().Trim().Replace("'", "\'") but still giving error . – Shailesh Mar 23 '12 at 06:19
@Shailesh: Try `adsName.ToString().Trim().Replace("'", "\\'")` – juergen d Mar 23 '12 at 06:40

OleDbException syntax error in insert Statment

2 Answers2