访问c#数据库OleDbException

本文关键字：OleDbException 数据库 访问 | 更新日期: 2023-09-27 18:05:24

我尝试编写代码访问数据库，但在粗体字处出现OldDbException错误。我应该如何更改我的单词，以便在运行时不会出现任何错误？

    private void submitbutton_Click(object sender, EventArgs e)
    {
        availabilitytabControl.SelectedTab = orderlisttabPage;
        OleDbConnection myAccessConn = myAccessConnection();
        OleDbCommand myAccessCommand = new OleDbCommand();
        DataSet myDataSet = new DataSet();
        try
        {
          int i;
            myAccessConn.Open();
            String insert ="insert into Particulars (Title,FirstName,LastName,Nationality,PassportNumber,PhoneNumber) VALUES(";

            for (i = 0; i < 100; i++)
            {
                myAccessCommand.CommandText = insert;
                String title = titlecomboBox.Items[i].ToString();
                String firstname = firstnametextBox.Text;
                String lastname = lastnametextBox.Text;
                String nationality = nationalitycomboBox.Items[i].ToString();
                String passportno = passporttextBox.Text;
                String phoneno = phonenotextBox.Text;
                myAccessCommand = new OleDbCommand(insert,myAccessConn);
                OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
                **myAccessCommand.ExecuteNonQuery();**
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.'n{0}", ex.Message);
            return;
        }
        finally
        {
            myAccessConn.Close();
        }

    }

访问c#数据库OleDbException

据我所见，您从未在查询的VALUES(..)部分中添加值。

我的建议是：；

  • 在VALUES部分中定义参数
  • 在for循环中使用myAccessCommand.Parameters.Add添加您的值
  • 执行您的查询
  • Clear()您的参数，就在您想要为下一个循环插入值之前
  • 您的OleDbDataAdapter部分是不必要的，因为您试图在其中放入INSERT语句

您应该始终使用参数化查询。这种字符串串联对SQL注入攻击是开放的。还可以使用using语句来处理数据库连接和命令。

using(var myAccessConn = myAccessConnection());
using(var myAccessCommand = myAccessConn.CreateCommand())
{
    myAccessCommand.CommandText = @"insert into Particulars (Title,FirstName,LastName,Nationality,PassportNumber,PhoneNumber) 
                                    VALUES(?, ?, ?, ?, ?, ?)";
    for (i = 0; i < 100; i++)
    {
        myAccessCommand.Parameters.Clear();
        myAccessCommand.Parameters.AddWithValue("?", titlecomboBox.Items[i].ToString());
        myAccessCommand.Parameters.AddWithValue("?", firstnametextBox.Text);
        myAccessCommand.Parameters.AddWithValue("?", lastnametextBox.Text);
        myAccessCommand.Parameters.AddWithValue("?", nationalitycomboBox.Items[i].ToString());
        myAccessCommand.Parameters.AddWithValue("?", passporttextBox.Text);
        myAccessCommand.Parameters.AddWithValue("?", phonenotextBox.Text); 
        myAccessConn.Open();
        myAccessCommand.ExecuteNonQuery();
    }
}

我用了AddWithValue方法作为例子，但你没有。此方法有时可能会产生意外结果。使用Add方法重载可以指定参数类型及其大小。

您可能需要这样修改代码：

(让我注意到，这只是一个测试代码，以检查它是否解决了您的问题，您必须使用parameter.add((来构造命令，以确保代码的安全(

private void submitbutton_Click(object sender, EventArgs e){
    availabilitytabControl.SelectedTab = orderlisttabPage;
    OleDbConnection myAccessConn = myAccessConnection();
    OleDbCommand myAccessCommand = new OleDbCommand();
    DataSet myDataSet = new DataSet();
    try
    {
      int i;
        myAccessConn.Open();
        String insert ="insert into Particulars (Title,FirstName,LastName,Nationality,PassportNumber,PhoneNumber) VALUES(";

        for (i = 0; i < 100; i++)
        {
            String title = titlecomboBox.Items[i].ToString();
            String firstname = firstnametextBox.Text;
            String lastname = lastnametextBox.Text;
            String nationality = nationalitycomboBox.Items[i].ToString();
            String passportno = passporttextBox.Text;
            String phoneno = phonenotextBox.Text; 
            insert  += "'"+ firstname +"','"+ lastname+"','"+nationality + "','"+ passportno +"','"+ phoneno +"')";

            myAccessCommand = new OleDbCommand(insert,myAccessConn);
            OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
            **myAccessCommand.ExecuteNonQuery();**
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.'n{0}", ex.Message);
        return;
    }
    finally
    {
        myAccessConn.Close();
    }
}
相关文章：