INSERT INTO语句语法错误

Question

我有一个问题，当试图从数据表插入行到Excel工作表。我一直得到语法错误，但当我插入sql字符串到mssql服务器没有问题验证sql语句。

这是我的代码:

public void InsertData(DataTable kpiData)
{
    string folderPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + @"KPIReports";
    string fileName = @"Return_Report - " + DateTime.Today.ToShortDateString() + ".xlsx";
    string[] files = Directory.GetFiles(folderPath);
    foreach (string file in files)
    {
        File.Delete(file);
    }
    File.Copy(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + @"ReportTemp.xlsx", folderPath + fileName);
    System.Data.OleDb.OleDbConnection connection;
    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
    string sql = null;
    connection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + folderPath + fileName + ";Extended Properties="Excel 12.0;ReadOnly=False;HDR=YES;"");
    connection.Open();
    myCommand.Connection = connection;
    foreach (DataRow row in kpiData.Rows)
    {
        string weight = row[11].ToString().Replace(',', '.');
        sql = "Insert into [Data$] (WeekNr, AccountNumber, Barcode, Barcode2, PickupDate, DeliveryCustID, DeliveryAlias, PickupCustID, PickupAlias, DeliveryAttentionName, Coli, Weight, Note, DeliveryType, " +
                    "Name, Street, HouseNo, Postal, City, DanxCode, Receiver, PODTime, OnTime, [Service]) Values('" + row[0].ToString() + "','" + row[1].ToString() + "','" + row[2].ToString() + "','" + row[3].ToString() + "','" + row[4].ToString() + "','"
                    + row[5].ToString() + "','" + row[6].ToString() + "','" + row[7].ToString() + "','" + row[8].ToString() + "','" + row[9].ToString() + "','" + row[10].ToString() + "','" + weight + "','" + row[12].ToString() + "','" + row[13].ToString() + "','"
                    + row[14].ToString() + "','" + row[15].ToString() + "','" + row[16].ToString() + "','" + row[17].ToString() + "','" + row[18].ToString() + "','" + row[19].ToString() + "','" + row[20].ToString() + "','" + row[21].ToString() + "','" + row[22].ToString() + "','" + row[23].ToString() + "')";
        myCommand.CommandText = sql;
        myCommand.ExecuteNonQuery();
    }
    myCommand.Dispose();
    connection.Close();
    releaseObject(myCommand);
    releaseObject(connection);
}

，这是SQL字符串:

Insert into [Data$] (WeekNr, AccountNumber, Barcode, Barcode2, PickupDate, DeliveryCustID, DeliveryAlias, PickupCustID, PickupAlias, DeliveryAttentionName, Coli, Weight, Note, DeliveryType, Name, Street, HouseNo, Postal, City, DanxCode, Receiver, PODTime, OnTime, [Service]) Values('20','44730629311','12626707007','0681739685','10-05-2014 15:22:13','xxxxx','xxxx','xxxxx','Asker','','1','0.2','','N','xxx','xxxx','111','0665','xxx','xxx','xxxx','13-05-2014 07:00:00','OT','Reverse')

我似乎找不到问题所在。我希望有人不能帮我。

Answer 1

我发现了这个问题。

问题是我没有注释列在括号里。因为Note是一个保留字，所以它必须用括号括起来，像这样:[Note]