Click here to Skip to main content
15,889,034 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?

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

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();
Posted
Updated 26-Mar-13 1:49am
v6
Comments
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.

[EDIT]
I have done it in this way and it works perfect:
C#
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


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

[/EDIT]
 
Share this answer
 
v2
Comments
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 ;)
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.
 
Share this answer
 
v2
Comments
Maciej Los 31-Mar-13 17:54pm    
Good work!
+5!
You've set up the InsertCommand but not executing it ... you also need the following
dAdapter.InsertCommand.ExecuteNonQuery();
 
Share this answer
 
Comments
Maciej Los 25-Mar-13 13:04pm    
Are you sure?
cmd_insert.ExecuteNonQuery();
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
 
Share this answer
 
v2

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