使用c＃代码从另一个数据库插入ms访问数据库 [英] insert into ms access db from another database with c# code

问题描述

我使用以下代码将记录从一个数据库插入另一个数据库，但它不起作用。我在MS-ACCESS 2007中尝试了这个查询并且它工作正常但是当我从C＃代码以编程方式调用它时它不起作用吗？

I use the following code to insert a record from one database to another but it doesn''t work. I tried the query in MS-ACCESS 2007 and it works fine but it doesn''t work when called programmatically from my C# code?

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访问中获取`query_insert`的内容时，它工作正常

它抛出INSERT INTO语法错误行中的异常

cmd_insert.ExecuteNonQuery（）;

When I take the the content of `query_insert` in ms access, it works fine
It throws INSERT INTO syntax error exception in line
cmd_insert.ExecuteNonQuery();

推荐答案

请检查：

1）什么是由 dialog.FileName 返回。它应该是另一个数据库的完整文件名。

2）两个数据库的版本是否相等？如果没有，连接字符串可能会有所不同。





我用这种方式完成它并且它完美无缺：

Please, check:
1) what is returned by dialog.FileName. It should be the full filename of another database.
2) does version of both databases are equal? If not, connection string might be differ.


I have done it in this way and it works perfect:

using System;
using System.Collections.Generic;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            System.Data.OleDb.OleDbConnection oConn = null; System.Data.OleDb.OleDbCommand oComm = null;
            string sConn = String.Empty; System.Text.StringBuilder sComm = null ;
            string sDb1 = String.Empty; string sDb2 = String.Empty;
            int retVal = 0;
            try
            {
                sDb1 = "C:\\db1.mdb";
                sDb2 = "C:\\db2.mdb";
                sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sDb2 + ";";
                oConn = new System.Data.OleDb.OleDbConnection(sConn);
                oConn.Open();
                sComm = new StringBuilder();
                sComm.AppendLine("INSERT INTO tblUsers (ID, UserName)");
                sComm.AppendLine("SELECT *");
                sComm.AppendLine("FROM tblUsers IN '" + sDb1 + "'");
                sComm.AppendLine("WHERE ID = 1;");
                Console.WriteLine(sComm.ToString());
                oComm = new System.Data.OleDb.OleDbCommand(sComm.ToString(), oConn);
                retVal = oComm.ExecuteNonQuery();
                Console.WriteLine("Records affected: {0}", retVal.ToString());
                //Console.ReadKey();
            }
            catch (System.Data.OleDb.OleDbException ex)
            {
                Console.WriteLine(ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (oConn.State == System.Data.ConnectionState.Open ) oConn.Close();
                Console.ReadKey();
            }
        }
    }
}

控制台中显示的文字：

Text displayed in Console:

INSERT INTO tblUsers (ID, UserName)
SELECT *
FROM tblUsers IN 'C:\db1.mdb'
WHERE ID = 1;

Records affected: 1

检查数据库的连接字符串并添加try..catch [ ^ ]阻止。

http://www.connectionstrings.com/access-2007 [ ^ ]



[/ EDIT]

Check connection string to your database and add try..catch[^] block.
http://www.connectionstrings.com/access-2007[^]

[/EDIT]

给出的语法错误是由于存在保留关键字 CURRENT_DATE 。这可以解决用方括号封装字段名称。

the syntax error given is due to the presence of a reserved keyword CURRENT_DATE. This could be resolved encapsulating the field name with square brackets.

你已经设置了InsertCommand但没有执行它...你还需要以下

You''ve set up the InsertCommand but not executing it ... you also need the following

dAdapter.InsertCommand.ExecuteNonQuery();

这篇关于使用c＃代码从另一个数据库插入ms访问数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！