Syntax error in inserting to excel sheet

Question

I was trying to insert data into the second sheet in an excel file, but it gives syntax error. I tried inserting with the same syntax in sheet1 and it was working fine. The sheet name is correct, column names are correct. My code is

try
{
    System.Data.OleDb.OleDbConnection MyConnection;
    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
    string sql = null;
    MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='d:\\contacts_moorthy.xls';Extended Properties=Excel 8.0;");
    MyConnection.Open();
    myCommand.Connection = MyConnection;
    sql = "insert into [Sheet2$] (To,Message,SentOn) values ('" + phonetxtbox.Text + "','" + messagetxtbox.Text + "','"+DateTime.Now+"')";
    myCommand.CommandText = sql;
    myCommand.ExecuteNonQuery();
    MyConnection.Close();
}
catch (Exception ex)
{
     MessageBox.Show("Error :",ex);
}

Why are you not using the Excel automation interface in stead of the SQL approach? See for example support.microsoft.com/kb/302084 — Reinier Torenbeek
– Reinier Torenbeek, Commented Oct 18, 2012 at 6:10
@ReinierTorenbeek i am a fresher. i was working with this codes. Sure i will try the one u mentioned — Rathinamoorthy
– Rathinamoorthy, Commented Oct 18, 2012 at 6:48
I got the answer. If i don't specify the column names (To,Message,SentOn) it's working. i don'o why it is not working with the names,but the names are exact. — Rathinamoorthy
– Rathinamoorthy, Commented Oct 18, 2012 at 9:26

maldago · Accepted Answer · 2012-10-18 20:54:41Z

2

I think it is is in your connection string for the Jet Provider.

http://www.connectionstrings.com/excel

The connection string has n attribute called HDR. If you add "HDR=Yes;" to your connection string should indicates that the first row contains columnnames, which should solve your issue.

answered Oct 18, 2012 at 20:54

maldago

212 bronze badges

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

Comments

C. Helling · Accepted Answer · 2017-09-01 00:55:37Z

-1

In my personal experience I noticed that it does not allow saving in Excel when the column names are not indicated in brackets "[]", that is to say it would be ... ([To], [Message], [SentOn] ).

edited Sep 1, 2017 at 0:55

C. Helling

1,4006 gold badges24 silver badges37 bronze badges

answered Aug 31, 2017 at 21:49

LAZZ

11 bronze badge

Collectives™ on Stack Overflow

Syntax error in inserting to excel sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related