将记录插入到 MSAccess

本文关键字：MSAccess 插入 记录 | 更新日期: 2023-09-27 18:33:59

有人可以向我解释为什么它不允许我将记录插入MS Access吗？出于某种原因，我已经尝试了人类可能的一切，但它不会让我做我需要做的事情。 我需要做的是允许从我的 C Sharp 程序写入记录，并希望按下一个按钮，该按钮会将值插入我的 MS Access 中。

void Insert_Record(object s, EventArgs e)
{
    string dbconnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"data source =BookCSharp.accdb";
    string dbcommand = "INSERT INTO BookKey, Title, Pages from Books;";
    OleDbConnection conn = new OleDbConnection(dbconnection);
    OleDbCommand comm = new OleDbCommand(dbcommand, conn);
    OleDbDataAdapter adapter = new OleDbDataAdapter(comm);          
    conn.Open();
    adapter.Fill(ds, "Books");
    conn.Close();
    DataTable dt = ds.Tables[0];
    //define a new record and place it into a new DataRow
    DataRow newRow   = dt. NewRow();
    newRow["BookKey"] = txtBookKey.Text;
    newRow["Title"]  = txtTitle.Text;
    newRow["Pages"]  = txtPages.Text;
    //add the new DataRow to DataTable 
    dt.Rows.Add(newRow);
    //update DB
    adapter.Update(ds, "Books");
    //accept changes
    ds.AcceptChanges();
    //update listBox1
    lstDisplayBooks.Items.Clear();
    foreach (DataRow row in ds.Tables[0].Rows)
    {
        lstDisplayBooks.Items.Add(row["BookKey"] + " " + row["Title"] + " (" + row["Pages"] + ")");
    }
    txtBookKey.Enabled = false;
    txtBookKey.Text = " ";
    txtTitle.Enabled = false;
    txtTitle.Text = " ";
    txtPages.Enabled = false;
    txtPages.Text = " ";
    btnInsert.Enabled = false;
}

将记录插入到 MSAccess

OleDbDataAdapter 需要一个 SELECT 查询和一个 OleDBCommandBuilder 来自动生成将在 Update 方法中使用的插入/更新/删除命令

void Insert_Record(object s, EventArgs e)
{
    string dbconnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"data source =BookCSharp.accdb";
    string dbcommand = "SELECT * FROM Books;";
    using(OleDbConnection conn = new OleDbConnection(dbconnection))
    using(OleDbCommand comm = new OleDbCommand(dbcommand, conn))
    {
         OleDbDataAdapter adapter = new OleDbDataAdapter(comm);
         OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
         // See comments below for these properties
         builder.QuotePrefix = "[";
         builder.QuoteSuffix = "]";
         conn.Open();            
         adapter.Fill(ds, "Books");
         DataTable dt = ds.Tables[0];
         DataRow newRow   = dt. NewRow();
         newRow["BookKey"] = txtBookKey.Text;
         newRow["Title"]  = txtTitle.Text;
         newRow["Pages"]  = txtPages.Text;
         dt.Rows.Add(newRow);
         builder.GetInsertCommand();
         adapter.Update(ds, "Books");
    }
    //update listBox1
    lstDisplayBooks.Items.Clear();
    foreach (DataRow row in ds.Tables[0].Rows)
    {
        lstDisplayBooks.Items.Add(row["BookKey"] + " " + row["Title"] + " (" + row["Pages"] + ")");
    }
    txtBookKey.Enabled = false;
    txtBookKey.Text = " ";
    txtTitle.Enabled = false;
    txtTitle.Text = " ";
    txtPages.Enabled = false;
    txtPages.Text = " ";
    btnInsert.Enabled = false;
}

OleDbCommandBuilder 链接中的示例包含如下示例。

该代码应该修复您的初始错误，但实际上在这种情况下不需要使用 OleDbDataAdapter。如果您的唯一目标只是向数据表添加新记录，并且不需要像 DataGridView 那样维护本地数据源，那么直接使用 OleDbCommand 和适当的书面 INSERT 和参数化查询要简单得多 

void Insert_Record(object s, EventArgs e)
{
    string dbconnection = "Provider=Microsoft.ACE.OLEDB.12.0;" + @"data source =BookCSharp.accdb";
    string dbcommand = "INSERT INTO Books (BookKey, Title, Pages) VALUES(?,?,?);";
    using(OleDbConnection conn = new OleDbConnection(dbconnection))
    using(OleDbCommand comm = new OleDbCommand(dbcommand, conn))
    {
         conn.Open();            
         comm.Parameters.AddWithValue("@p1", txtBookKey.Text);
         comm.Parameters.AddWithValue("@p2", txtTitle.Text);
         comm.Parameters.AddWithValue("@p3", txtPages.Text);
         comm.ExecuteNonQuery();
    }
    .....
相关文章：