使用INSERT INTO的语法错误 [英] Syntax error using INSERT INTO

问题描述

<->

疯狂，因为我真的不了解语法错误.

我已经检查了表名和字段名，它们都很好，但是由于某种原因不接受插入".另一方面，选择"和更新"正在运行.
我很困惑.

在此先感谢

继承人代码:

<->Hi,

Getting crazy with a syntax error that I´m really not understanding.

I''ve checked table and field names and it''s all good, but for some reason doesn''t accept the "Insert". On the other hand Selects and Updates are working.
I''m confused.

Thanks in advance

heres the code:

public void ExportToDB(FastOrder_T3.RequestDetails[] Incoming)
{

    OleDbConnection dbConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @Properties.Settings.Default.DBPath);

    try
    {
        dbConnection.Open();
        OleDbCommand dbCommand;

        foreach (RequestDetails item in Incoming)
        {
            string QueryDB = "INSERT INTO Details (Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) "
            +"VALUES ('" + item.Order + "', '" + item.ProductCode + "', '" + item.ProductName + "', '" + item.Tray + "', '" + item.Index + "', '" + item.Depth + "', '" + item.DeviceID + "', '" + item.IO + "');";
            dbCommand = new OleDbCommand(QueryDB, dbConnection);
            dbCommand.ExecuteNonQuery();
            dbCommand.Dispose();
        }



        dbConnection.Close();
    }


    catch(OleDbException e)
    {

        dbConnection.Close();
    }

}

base {System.Data.Common.DbException} = {在插入INSERT INTO时出错".}

ErrorCode = -2147217900

错误= {System.Data.OleDb.OleDbErrorCollection}

QueryDB的最终结果="INSERT INTO Details(Order，ProductCode，ProductName，Tray，Index，Depth，DeviceID，IO)VALUES(''0001''，''1234566''，''Teste 4''，''3 ''，''1''，''1''，''1''，''IN'');"

我复制/粘贴了字段名称，以防万一我丢失了一些东西.它们都设置为文本.

base {System.Data.Common.DbException} = {"Erro de sintaxe na instrução INSERT INTO."}

ErrorCode = -2147217900

Errors = {System.Data.OleDb.OleDbErrorCollection}

Final result of QueryDB = "INSERT INTO Details(Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) VALUES(''0001'',''1234566'',''Teste 4'',''3'',''1'',''1'',''1'',''IN'');"

I copy/pasted the field names in case i was missing something. They are all set to text.

推荐答案

我建​​议使用参数化查询-看起来更整洁，并且可以解决问题:

I would suggest using parameterised queries - it would look a lot neater, and may get rid of the problem:

string QueryDB = "INSERT INTO Details (Order, ProductCode, ProductName, Tray, Index, Depth, DeviceID, IO) VALUES (@OR, @PC, @PN, @TR, @IN, @DP, @DID, @IO)";
dbCommand = new OleDbCommand(QueryDB, dbConnection);
dbCommand.Parameters.AddWithValue("@OR", item.Order);
dbCommand.Parameters.AddWithValue("@PC", item.ProductCode);
dbCommand.Parameters.AddWithValue("@PN", item.ProductName);
dbCommand.Parameters.AddWithValue("@TR", item.Tray);
dbCommand.Parameters.AddWithValue("@IN", item.Index);
dbCommand.Parameters.AddWithValue("@DP", item.Depth);
dbCommand.Parameters.AddWithValue("@DID", item.DeviceID);
dbCommand.Parameters.AddWithValue("@IO", item.IO)l
dbCommand.ExecuteNonQuery();

如果不能解决问题，可能会使单引号更容易阅读！

[edit]糟糕！ [/edit]

If it doesn''t cure the problem, it may make it easier to read without the single quotes!

[edit]Oops! Should have been AddWithValue rather than Add: the later is depreciated.[/edit]

尝试将订购"和索引"字段的名称括在方括号内，如下所示:INSERT INTO Details ([Order], ProductCode, ProductName, Tray, [Index], Depth, DeviceID, IO)

您可以使用原始提供的代码或OriginalGriff建议的更改来做到这一点.

Try surrounding the name of the Order and Index fields with brackets like this: INSERT INTO Details ([Order], ProductCode, ProductName, Tray, [Index], Depth, DeviceID, IO)

You can do this with the code as you originally presented it or with the changes OriginalGriff proposed.

这篇关于使用INSERT INTO的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！