使用OdbcConnection在INSERT INTO中的语法错误 [英] Syntax Error in INSERT INTO using OdbcConnection

问题描述

当我为MS Access数据库执行以下代码时，我得到4200 Syntax Error:

I get 4200 Syntax Error when I execute this code for a MS Access database:

protected void Button1_Click(object sender, EventArgs e)
    {
        using (OdbcConnection conn = new OdbcConnection(@"Dsn=ani;dbq=D:\anita\inventory\chemicals.accdb;defaultdir=D:\anita\inventory;driverid=25;fil=MS Access;maxbuffersize=2048;pagetimeout=5;uid=admin"))
        {
          conn.Open();
          string CommandText = "INSERT INTO SupplierDetails (ID, Supplier, Company, Address, State, Country, Pincode, PhoneNo, MobileNo, Email, Fax, RawMaterials, Note) VALUES (@ID, @Supplier, @Company, @Address, @State, @Country, @Pincode, @PhoneNo, @MobileNo, @Email, @Fax, @RawMaterials, @Note)";

            using (OdbcCommand cmd = new OdbcCommand(CommandText, conn))
            {
                cmd.Parameters.AddWithValue("@ID", TextBox3.Text);
                cmd.Parameters.AddWithValue("@Supplier", TextBox4.Text);
                cmd.Parameters.AddWithValue("@Company", TextBox1.Text);
                cmd.Parameters.AddWithValue("@Address", TextBox11.Text);
                cmd.Parameters.AddWithValue("@State", TextBox2.Text);
                cmd.Parameters.AddWithValue("@Country", TextBox5.Text);
                cmd.Parameters.AddWithValue("@Pincode", TextBox10.Text);
                cmd.Parameters.AddWithValue("@PhoneNo", TextBox6.Text);
                cmd.Parameters.AddWithValue("@MobileNo", TextBox7.Text);
                cmd.Parameters.AddWithValue("@Email", TextBox8.Text);
                cmd.Parameters.AddWithValue("@Fax", TextBox9.Text);
                cmd.Parameters.AddWithValue("@RawMaterials", TextBox12.Text);
                cmd.Parameters.AddWithValue("@Note", TextBox13.Text);
                cmd.ExecuteNonQuery();
            }
        }
    }

推荐答案

名为NOTE的字段与保留关键字.
用方括号将其封装

The field named NOTE has the same name of a reserved keyword in JET 4.0.
Encapsulate it with square brackets

string CommandText = "INSERT INTO SupplierDetails (ID, Supplier, Company, Address, " + 
                     "State, Country, Pincode, PhoneNo, MobileNo, Email, Fax, " + 
                     "RawMaterials, [Note]) VALUES (@ID, @Supplier, @Company, @Address, " + 
                     "@State, @Country, @Pincode, @PhoneNo, @MobileNo, @Email, " + 
                     "@Fax, @RawMaterials, @Note)";

编辑我看到您正在使用OdbcConnection，这将要求您使用问号(而不是带有@前缀的字符串)提供参数占位符.所以您的CommandText应该是:

EDIT I see you are using an OdbcConnection, this will require that your parameters placeholders will be supplied using question marks, not strings with @ prefix. So your CommandText should be:

string CommandText = "INSERT INTO SupplierDetails (Supplier, Company, Address, " + 
                     "State, Country, Pincode, PhoneNo, MobileNo, Email, Fax, " + 
                     "RawMaterials, [Note]) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

请注意，我还删除了ID字段及其参数占位符，因为您说它是一个AutoIncrement字段，因此您不能为此传递自己的值. 还要记住，在这种情况下，参数是通过参数在Parameter集合中的位置来识别的.因此，按照commandText期望的正确顺序添加它们非常重要.

Note how I have removed also the ID field and its parameter placeholder because you say it is an AutoIncrement field and therefore you can't pass your own value for that. Also remember that, in this scenario, the parameters are recognized by their position in the Parameter collection. Thus it is very important to add them in the correct order expected by the commandText.

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