Click here to Skip to main content
15,040,743 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 ;


   OleDbDataAdapter dAdapter = new OleDbDataAdapter();
   OleDbCommand cmd_insert = new OleDbCommand(query_insert, dbConnDest);

   dAdapter.InsertCommand = cmd_insert;


When I take the the content of `query_insert` in ms access, it works fine
It throws INSERT INTO syntax error exception in line
Updated 26-Mar-13 1:49am
Maciej Los 25-Mar-13 17:33pm
Insert space between: Questionnaires_Table(BranchName -> Questionnaires_Table (BranchName
pouroutis 25-Mar-13 17:43pm
still nothing happen i try the query in access manually and works perfect
pouroutis 25-Mar-13 19:15pm
the problem was Current_Date wants [ ]
Maciej Los 26-Mar-13 7:53am
Does the tables are identical in both databases (the same structure and data types)?
pouroutis 31-Mar-13 17:02pm
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.

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;
                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);
                sComm = new StringBuilder();
                sComm.AppendLine("INSERT INTO tblUsers (ID, UserName)");
                sComm.AppendLine("SELECT *");
                sComm.AppendLine("FROM tblUsers IN '" + sDb1 + "'");
                sComm.AppendLine("WHERE ID = 1;");
                oComm = new System.Data.OleDb.OleDbCommand(sComm.ToString(), oConn);
                retVal = oComm.ExecuteNonQuery();
                Console.WriteLine("Records affected: {0}", retVal.ToString());
            catch (System.Data.OleDb.OleDbException ex)
            catch (Exception ex)
                if (oConn.State == System.Data.ConnectionState.Open ) oConn.Close();

Text displayed in Console:
INSERT INTO tblUsers (ID, UserName)
FROM tblUsers IN 'C:\db1.mdb'

Records affected: 1

Check connection string to your database and add try..catch[^] block.[^]

pouroutis 25-Mar-13 13:06pm
versions are the same i get the query_insert content and try it in ms access and works perfect
Maciej Los 25-Mar-13 13:07pm
Did you chek what value is returned by dialog.FileName?
pouroutis 25-Mar-13 13:09pm
yes is correct
pouroutis 25-Mar-13 13:19pm
did you know any other way to insert a record from one database table to other database table?
Maciej Los 25-Mar-13 13:41pm
See my updated solution ;)
Menon Santosh 25-Mar-13 14:16pm
nice solution my +5
Maciej Los 25-Mar-13 14:17pm
Thank you, Menon Santosh ;)
pouroutis 25-Mar-13 15:52pm
still nothing happen
Maciej Los 25-Mar-13 16:08pm
Man, i can't do it for you. You need to debug your code. As i wrote past, check connection string, add try-catch block. If it won't help, post your full code.
pouroutis 25-Mar-13 17:21pm
i update my question with code
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.
Maciej Los 31-Mar-13 17:54pm
Good work!
You've set up the InsertCommand but not executing it ... you also need the following
Maciej Los 25-Mar-13 13:04pm
Are you sure?
CHill60 25-Mar-13 20:40pm
Yeah ... loads of changes since I noticed he didn't have that in there ... I actually (sad) copied the code and did a ctrl-F!! About to give a +5 for your patience if nothing else :-)
Maciej Los 26-Mar-13 2:56am
Maybe OP did changes after your suggestion...
pouroutis 25-Mar-13 13:04pm
i try it but it give me an exception for INSERT INTO syntax but i get the result of query_insert variable and put it in ms access and works fine
pouroutis 25-Mar-13 13:25pm
did you know any other way to insert a record from one database table to other database table?
1. There is no role of the dAdapter in your code
2. in place of dialog.FileName, there should be name of the source table.
3.File name and path will find its place in the connection string.check your connection string

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900