OleDBException错误INSERT [英] OleDBException error INSERT

问题描述

我构建了一个C#应用程序，您可以在其中登录并注册新帐户.但是，当我单击按钮新帐户"并填写必填字段时，会出现以下错误:

I build a c# application where you can login and can register a new account. But when I click on my button new account, and fill in the required fields I will get the following error:

vcom.ExecuteNonQuery(); -> OleDBException未处理.指令INSERT包含语法错误.

vcom.ExecuteNonQuery(); -> OleDBException was unhandled. The instruction INSERT contains a syntaxerror.

请参见下面的代码:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace Eindopdracht
{
    public partial class maak_account : Form
    {

        OleDbConnection vcon = new OleDbConnection(@"provider= microsoft.jet.oledb.4.0;data source=sample.mdb");

        public maak_account()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection vcon = new OleDbConnection(@"provider= microsoft.jet.oledb.4.0;data source=sample.mdb");
            vcon.Open();

            string test = string.Format("insert into inlog (PASSWORD, Username, leeftijd, gewicht) VALUES ('" + textBox2.Text + "','" + textBox1.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')");
            OleDbCommand vcom = new OleDbCommand(test, vcon);
            vcom.ExecuteNonQuery();
            MessageBox.Show("Uw gegevens zijn opgeslagen");
            vcom.Dispose();
        }
    }
}

推荐答案

语法错误的原因是因为Password恰好是列的名称，是保留关键字.

The reason for the syntax error is because Password, which happens to be the name of the column, is a reserved keyword.

insert into inlog ([PASSWORD], Username, leeftijd, gewicht)

• MS Access保留关键字
• MS Access Reserved Keywords

从MS Access文档中，

From MS Access Docs,

如果已使用保留字，则可以通过以下方式避免出现错误消息: 用方括号([])包围单词的每次出现.然而， 最好的解决方案是将名称更改为非保留字.

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

要进一步改进代码，

• 使用using语句正确处置对象
• 使用try-catch正确处理异常
• 参数化值以避免sql注入
• use using statement to properly dispose object
• use try-catch to properly handle exceptions
• parameterized the values to avoid sql injection

示例

string connStr = @"provider= microsoft.jet.oledb.4.0;data source=sample.mdb";
string test = "insert into inlog ([PASSWORD], Username, leeftijd, gewicht) VALUES (?, ?, ?, ?)";

using(OleDbConnection vcon = new OleDbConnection(connStr))
{
    using(OleDbCommand vcom = new OleDbCommand(test, vcon))
    {
        vcom.Parameters.AddWithValue("PASSWORD", textBox2.Text);
        vcom.Parameters.AddWithValue("Username", textBox1.Text);
        vcom.Parameters.AddWithValue("leeftijd", textBox3.Text);
        vcom.Parameters.AddWithValue("gewicht", textBox4.Text);
        try
        {
            vcon.Open();
            com.ExecuteNonQuery();
        }
        catch(OleDbException ex)
        {
            // do something with the exception
        }
    }
}

• 使用参数化查询
• Using Parameterized Query

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