c# - 从 C# Winform 连接到 MS Access 数据库时出错

Question

我是一名学生程序员，我正在为一所小学校编写这个软件，这是我的第一个程序，下面的代码给我错误

syntax error in insert into statement

我知道连接字符串不是问题，因为我用它来插入到另外两个具有相同插入格式的表中。

我正在使用访问数据库。

违规代码是

connection.Open();

OleDbCommand command = new OleDbCommand();

command.Connection = connection;

command.CommandText = "insert into studentBillRecords (StudentName, Department, Level, AccomodationStatus, SemesterBill, PreviousBalance, TotalBill) values ('"+ txtSRstudentName.Text + "', '" + cmbSRDepartment.Text + "', '" + cmbSRLevel.Text + "', '" + cmbSRAccomodationStatus.Text + "', '" + txtSRSemesterBill.Text + "', '" + txtSRPreviousBalance.Text + "', '" + txtSRTotalBill.Text + "')";


 MessageBox.Show(command.CommandText);

command.ExecuteNonQuery();

connection.Close();

具有不同表名、列名和输入的相同代码适用于同一数据库中的另一个表，但不适用于这个表。

Answer 1

Level是access中的保留关键字。

也使用参数而不是连接字符串。试试这段代码，它使它更安全、更容易阅读:注意:我将 Level 列的名称更改为 StudentLevel，我认为它在您的表中尚不存在。

try
            {
                using (OleDbConnection connection = new OleDbConnection("my connection string"))
                {
                    //Open connection
                    connection.Open();

                    //Create new command
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    //Create command text
                    cmd.CommandText =
                        "INSERT INTO studentBillRecords " +
                        "(StudentName, Department, StudentLevel, AccomodationStatus, SemesterBill, PreviousBalance, TotalBill) VALUES " +
                        "(@StudentName, @Department, @StudentLevel, @AccomodationStatus, @SemesterBill, @PreviousBalance, @TotalBill)";

                    // Add names paremeters
                    cmd.Parameters.AddRange(new OleDbParameter[]
                    {
                       new OleDbParameter("@StudentName", txtSRstudentName.Text),
                       new OleDbParameter("@Department", cmbSRDepartment.Text),
                       new OleDbParameter("@StudentLevel", cmbSRLevel.Text),
                       new OleDbParameter("@AccomodationStatus", cmbSRAccomodationStatus.Text),
                       new OleDbParameter("@SemesterBill", txtSRSemesterBill.Text),
                       new OleDbParameter("@PreviousBalance", txtSRPreviousBalance.Text),
                       new OleDbParameter("@TotalBill", txtSRTotalBill.Text)
                   });

                    //Execute Query
                    cmd.ExecuteNonQuery();

                    //No need to close because we are using "using"
                }
            }
            catch (OleDbException ex)
            {
                //If an exception occurs let's print it out to console
                Console.WriteLine("ERROR: " + ex.ToString());
                throw;
            }

有关如何更改列名称的信息，请阅读: https://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx