excelinsertsyntax-erroroledbexception

OleDbException syntax error in insert Statment

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.

Solution

  • 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.