Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Access databse
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();
Posted 25-Mar-13 7:42am
Edited 26-Mar-13 2:49am
v6
Comments
Maciej Los at 25-Mar-13 17:33pm
   
Insert space between: Questionnaires_Table(BranchName -> Questionnaires_Table (BranchName
pouroutis at 25-Mar-13 17:43pm
   
still nothing happen i try the query in access manually and works perfect
pouroutis at 25-Mar-13 19:15pm
   
the problem was Current_Date wants [ ]
Maciej Los at 26-Mar-13 7:53am
   
Does the tables are identical in both databases (the same structure and data types)?
pouroutis at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
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]
  Permalink  
v2
Comments
pouroutis at 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 at 25-Mar-13 13:07pm
   
Did you chek what value is returned by dialog.FileName?
pouroutis at 25-Mar-13 13:09pm
   
yes is correct
pouroutis at 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 at 25-Mar-13 13:41pm
   
See my updated solution ;)
Menon Santosh at 25-Mar-13 14:16pm
   
nice solution my +5
Maciej Los at 25-Mar-13 14:17pm
   
Thank you, Menon Santosh ;)
pouroutis at 25-Mar-13 15:52pm
   
still nothing happen
Maciej Los at 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 at 25-Mar-13 17:21pm
   
i update my question with code
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
v2
Comments
Maciej Los at 31-Mar-13 17:54pm
   
Good work!
+5!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

You've set up the InsertCommand but not executing it ... you also need the following
dAdapter.InsertCommand.ExecuteNonQuery();
  Permalink  
Comments
Maciej Los at 25-Mar-13 13:04pm
   
Are you sure?
cmd_insert.ExecuteNonQuery();
CHill60 at 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 at 26-Mar-13 2:56am
   
Maybe OP did changes after your suggestion...
;)
pouroutis at 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 at 25-Mar-13 13:25pm
   
did you know any other way to insert a record from one database table to other database table?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
  Permalink  
v2

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

  Print Answers RSS
0 OriginalGriff 250
1 Jochen Arndt 155
2 PIEBALDconsult 150
3 DamithSL 125
4 Afzaal Ahmad Zeeshan 120
0 OriginalGriff 5,695
1 DamithSL 4,591
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,190


Advertise | Privacy | Mobile
Web03 | 2.8.141220.1 | Last Updated 1 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100