使用C#和oledb在insert to excel命令中括号无效? [英] Invalid bracketing in insert to excel command using c# and oledb?

问题描述

我正在使用OLEDB将一些记录存储到excel文件中

I am iserting some records to excel file using OLEDB

    foreach (TblSample rec in LstTblSample)
            {

                OleDbCommand cmdInsert = new OleDbCommand("insert into [sd$]" +
                                                   "([Lab Ref],[D.M.],PH,CP,ADF,NDF,DMD,ME,NIRASH,IFCOW,IFSHEEP,NH3,UFV,UFL,PDIE,PDIN,PDIA,LFU,CFU,SFU) values" +
                                                   "(" + "'" + rec.SampleNo + "'" + "," + "'" + rec.DryMatter + "'" + "," + "'" + rec.pH + "'" + "," +
                                                   "'" + rec.CrudeProtein + "'" + "," + "'" + "" + "'" + "," + "'" + rec.NDF + "'" + "," + "'" + rec.DMD + "'" + "," +
                                                   "'" + rec.ME + "'" + "," + "'" + rec.ASH + "'" + "," + "'" + rec.DMIntakeCattle + "'" + "," +
                                                   "'" + rec.DMIntakeSheep + "'" + "," + "'" + rec.NH3 + "'" + "," + "'" + rec.UFV + "'" + "," +
                                                   "'" + rec.UFL + "'" + "," + "'" + rec.PDIE + "'" + "," + "'" + rec.PDIN + "'" + "," + "'" + rec.PDIA + "'" + "," +
                                                   "'" + rec.LFU + "'" + "," + "'" + rec.CFU + "'" + "," + "'" + rec.SFU + "'" + ")", con);
                cmdInsert.ExecuteNonQuery();
            }

我的excel文件有一个名为D.M.的列.这是导致此错误的原因.我想将此列名称保留在excel中，因为它被另一软件使用.在C#代码中是否有任何解决方法可以解决此问题?

My excel file has a column named D.M. which is causing this error. I want to keep this column name in excel because, its used by another software. Is there any work around in C# code for insert to cope with this?

推荐答案

我看到的问题是此行中的关键字 values

Where I see a problem is the keyword values in this line

OleDbCommand cmdInsert = new OleDbCommand("insert into [sd$] values" +

通常在 INSERT 语句中指定列字段，然后调用 VALUES .或者，您可以调用 VALUES 而不命名列字段.在这种情况下，您将同时执行这两项操作.在 VALUES 上方，分配列之前会引起问题.

In the INSERT statement you usually specify the column fields before calling VALUES. Alternatively you can call VALUES without naming the column fields. In this instance you do both. Above VALUES BEFORE assigning the columns causes a problem.

一种选择是完全删除列声明，只需将插入[sd $]值(" +'" + rec.SampleNo + -即列和条目匹配.您还可以尝试将INSERT语句中的列重命名为[DM]，然后在插入数据后将excel电子表格中的列重命名为[DM]，作为常规任务，当然可以使用以下代码对其进行修改: http://dontbreakthebuild.com/2012/03/16/reading-excel-column-names-in-c-using-excel-interop/

One option is to remove the column declaration completely and just do insert into [sd$] values "(" + "'" + rec.SampleNo + - i.e. is the columns and entries match. You can also try rename the column in the INSERT statement to [DM] and then after the data was inserted rename the column in the excel spreadsheet to [D.M.] as a routine task using the following code modifying it of course: http://dontbreakthebuild.com/2012/03/16/reading-excel-column-names-in-c-using-excel-interop/

如果所有其他方法均失败，请使用打开XML SDK 2.0 尝试操作文档.

If all else fails make use of the Open XML SDK 2.0 to try and manipulate the document.

我出去了！祝你好运.

这篇关于使用C#和oledb在insert to excel命令中括号无效?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！