insert into ms access db from another database with c# code

Question

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();

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();

EDIT

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());
                    }



                }


        }

    }
}

EDIT

possible duplicate of [insert into ms access database](http://stackoverflow.com/questions/15618165/insert-into-ms-access-database) — Daniel A. White, Mar 25 '13 at 17:12

score 1 · Answer 1 · answered Mar 25 '13 at 17:12

1

you are missing 'values' keyword in query_insert.

answered Mar 25 '13 at 17:12

XTGX

114
9

2

the values is the SELECT query – user2183831 Mar 25 '13 at 17:15

Steve · Accepted Answer · 2013-03-25T23:03:33.937

1

I have found a different syntax in this Microsoft forum

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

so you could try this

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();

Tested with "Provider=Microsoft.ACE.OLEDB.12.0;"

However, with more research it is clear that 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.

By the way, the IN syntax works as well once cured the problem of CURRENT_DATE.

edited Mar 25 '13 at 23:03

answered Mar 25 '13 at 17:42

Steve

213,761
22
232
286

i use IN because the source database is different from the destination database – user2183831 Mar 25 '13 at 17:53
i'm using MS ACCESS 2007 .accdb i test this but still nothing happens thank you anyway for your courage – user2183831 Mar 25 '13 at 19:12
I have tested the code above (MS Access;.....) and it works with two different database and with ACE provider. – Steve Mar 25 '13 at 21:48
of course no, I have set up two different databases with a simple table and some data. Then I have done the transfer with the syntax like the one above. The important thing is: REMOVE the IN, add [MS Access;DATABASE=.......YOUR FILE.....; – Steve Mar 25 '13 at 21:54
my databases has different structure does this made the problem? – user2183831 Mar 25 '13 at 21:56
No because you have specified the fields to grab from the external table through the SELECT. I was thinking, do you put the Whole pathname with the backslash separator in that IN ..... ? – Steve Mar 25 '13 at 21:58
i try your example and when i get the variable query_string and put it manually in ms access works perfect too but not in my program – user2183831 Mar 25 '13 at 22:00
If you like I could look at your project. Share a link to a public repository like dropbox, skydrive, gdrive, mediafire or whatever you like. – Steve Mar 25 '13 at 22:07
where do you want to send it?? – user2183831 Mar 25 '13 at 22:20
Upload to a sharing site like mediafire, skydrive, gdrive,dropbox and post the link here – Steve Mar 25 '13 at 22:21
give me your email its easier – user2183831 Mar 25 '13 at 22:23
I can't believe it. CURRENT_DATE is a reserved keyword. Need to encapsulate it with square brackets. I will update the answer. By the way, works also with the IN – Steve Mar 25 '13 at 22:50
thank you very much that was the problem i couldn't imagine that answer for this problem – user2183831 Mar 25 '13 at 22:55
i can't vote for you but you got my vote anyway thank you very much – user2183831 Mar 25 '13 at 22:58
It was fun, now I go to sleep. Thanks anyway (however it's stressing that the most upvoted answer is clearly wrong, well, let's go) – Steve Mar 25 '13 at 23:01
+1 for the revelation that CURRENT_DATE is a reserved keyword, did not know that! – JMK Mar 26 '13 at 00:40

score 0 · Answer 3 · answered Mar 25 '13 at 17:14

0

I'm not really familiar with ms access queries but I think you shouldn't use OleDbDataAdapter. The code should look similar to following:

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();

answered Mar 25 '13 at 17:14

Denys Denysenko

7,598
1
20
30

did you know any other way to insert a record from one database table to other database table? – user2183831 Mar 25 '13 at 17:25
@user2183831, yes but I didn't use ms-access for a while. What I'm wandering is it is there any way to set db schema explicitly in access? Because it looks like you're inserting into the same table from which you query. – Denys Denysenko Mar 25 '13 at 17:29
it just the same table name – user2183831 Mar 25 '13 at 17:52

score 0 · Answer 4 · answered Mar 25 '13 at 18:29

0

Like others said, thats a SQL syntax problem. You are missing the VALUES keyword, look:

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

Where is your VALUES keyword?

answered Mar 25 '13 at 18:29

Michel Vaz Ramos

131
1
5

@user2183831: First: try to write the VALUES keyword, like: INSERT INTO (COL1,COL2,COL3) VALUES (SELECT (COL1,COL2,COL3) FROM...) Second: try to use SqlParam in your Command object instead of concatenating strings. Will be better to you to find the errors source. – Michel Vaz Ramos Mar 25 '13 at 19:10
i still got the same exception – user2183831 Mar 25 '13 at 19:43
VALUES is the SELECT query – user2183831 Mar 25 '13 at 19:54

insert into ms access db from another database with c# code

4 Answers4