用c#代码从另一个数据库插入ms access db

本文关键字：插入 ms access db 数据库 另一个 代码 | 更新日期: 2023-09-27 18:06:00

我使用以下代码将记录从一个数据库插入到另一个数据库，但它不起作用。我尝试了MS-ACCESS 2007的查询，它工作得很好，但它不工作时，从我的C#代码编程调用?

string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
    + "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
    + "FROM Questionnaires_Table IN '" + dialog.FileName + "' Where Branch_ID = " + textBox1.Text ;
dbConnDest.Open();

   OleDbDataAdapter dAdapter = new OleDbDataAdapter();
   OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);
   dAdapter.InsertCommand = cmd_insert;
   cmd_insert.ExecuteNonQuery();
dbConnDest.Close();

当我在ms access中取query_insert的内容时，它工作正常

抛出

INSERT INTO语法错误异常

cmd_insert.ExecuteNonQuery ();

编辑
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 WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Title = "select database";

            if ((dialog.ShowDialog() == DialogResult.OK) && (textBox1.Text == ""))
            {
                    MessageBox.Show("insert reference year", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {


                    OleDbConnection dbConnDest;
                    dbConnDest = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:'Users'SystemA.accdb;Persist Security Info=False;");
                    try
                    {

                        string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
                                               + "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
                                               + "FROM Questionnaires_Table1 IN '" + dialog.FileName + "' Where ReferenceYear = " + textBox1.Text + ";";


                        dbConnDest.Open();

                        OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);
                        try
                        {
                            cmd_insert.ExecuteNonQuery();
                        }
                        catch (Exception g)
                        {
                            MessageBox.Show(g.ToString());
                        }

                        textBox2.Text = query_insert.ToString();
                        dbConnDest.Close();
                    }
                    catch (Exception h)
                    {
                        MessageBox.Show(h.ToString());
                    }

                }

        }
    }
}
编辑

用c#代码从另一个数据库插入ms access db

我发现了一个不同的语法在这个微软论坛

  INSERT INTO [AccessTable] SELECT * FROM [MS Access;DATABASE=D:'My Documents'db2.mdb].[Table2]

所以你可以试试这个

string query_insert = "INSERT INTO Questionnaires_Table " +
    "(BranchName,Factor,Region,Branch_ID,[Current_Date],No_Employees) " +
    "SELECT BranchName,Factor,Region,Branch_ID,[Current_Date],No_Employees " +
    "FROM [MS Access;DATABASE=" + dialog.FileName  + "].Questionnaires_Table " +
    "Where Branch_ID = @branch";
dbConnDest.Open();
OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);
cmd_insert.Parameters.AddWithValue("@branch", textBox1.Text);
cmd.ExecuteNonQuery();

用"Provider=Microsoft.ACE.OLEDB.12.0;"测试

然而，通过更多的研究可以清楚地看到，给出的语法错误是由于保留关键字CURRENT_DATE的存在。这可以通过用方括号封装字段名来解决。

顺便说一下，一旦解决了CURRENT_DATE的问题，IN语法也可以工作。

您在query_insert中缺少" values "关键字。

我不太熟悉ms访问查询，但我认为你不应该使用OleDbDataAdapter。代码应该类似于以下内容:

string query_insert = "INSERT INTO Questionnaires_Table(BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees) "
    + "SELECT BranchName,Factor,Region,Branch_ID,Current_Date,No_Employees "
    + "FROM Questionnaires_Table IN '" + dialog.FileName + "' Where Branch_ID = " + textBox1.Text ;
dbConnDest.Open();
OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);
cmd_insert.ExecuteNonQuery();
dbConnDest.Close();

就像其他人说的，这是一个SQL语法问题。您缺少VALUES关键字，请查看:

INSERT INTO TABLENAME (COL1, COL2, COL2) VALUES (VAL1, VAL2, VAL3)

VALUES关键字在哪里?

相关文章：