无效的INSERT INTO Sql命令 [英] Invalid INSERT INTO Sql Command

问题描述

运行此代码后，出现此运行时错误:
插入语句中的语法错误"

When this code is ran I get this runtime error:
"Syntax error in insert into statement"

void SaveData()
        {//do the real work here
            string insertCMD = "INSERT INTO Users (Username,Password,RegisterEmail) VALUES (@Username,@[Password],@RegisterEmail)";
            string ConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\RS_Studio_Login.mdb";
            using (OleDbConnection MyConn = new OleDbConnection(ConnStr))
            {
                try
                {//insertCommand.Parameters.Add("Time", OleDbType.Char).Value = strTime;
                    MyConn.Open();
                    OleDbCommand Cmd = new OleDbCommand(insertCMD, MyConn);
                    Cmd.Parameters.Add("@Username", OleDbType.Char).Value = textEdit1.Text;
                    Cmd.Parameters.Add("@[Password]", OleDbType.Char).Value = textEdit2.Text;
                    Cmd.Parameters.Add("@RegisterEmail", OleDbType.Char).Value = textEdit3.Text;
                    Cmd.ExecuteNonQuery();
                    MyConn.Close();
                    MessageBox.Show("User creation successful!");
                    this.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

推荐答案

根据MSDN文章 ^ ]，则OLEDB提供程序不支持参数的Sql样式.由于它不支持命名参数，因此它们表示为

According to MSDN article here[^], the OLEDB Provider does not support the Sql Style of Parameters. As it does not support the named parameters, they state as

Quote:

因此，将OleDbParameter对象添加到OleDbParameterCollection的顺序必须直接对应到命令文本中参数的问号占位符的位置.

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

将代码更改为如下所示:

Change your code to look like the following:

string insertCMD = "INSERT INTO Users (Usern,Passw,RegisterEmail) VALUES (?,?,?)";

和

and

insertCMD.Parameters.Add(textEdit1.Text);
insertCMD.Parameters.Add(textEdit2.Text);
insertCMD.Parameters.Add(textEdit3.Text);

这有帮助吗?

Does this help?

如果您的数据库表包含使用Microsoft Jet 4.0保留字的列名，则可能会出现此问题. PASSWORD是此处的关键字 [ MS支持:您可能会收到"INSERT INTO语句中的语法错误"错误消息 [ ^ ]

This problem may occur if your database table contains column names that use Microsoft Jet 4.0 reserved words.
PASSWORD is a KEYWORD here[^] and thus an error.

Change the column name to get away with the error.

Details, refer here: MS Support: You may receive a "Syntax error in INSERT INTO statement" error message [^]

这篇关于无效的INSERT INTO Sql命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！