Copy tables from one database to another on C#

Question

0.00/5 (No votes)

See more:

I wanna copy a table from on access database to another access database using c#
both database contain a table called "check" I want to copy check table from database1(backup) to database2(CheckDatabase)
backup location is on my D:\backup.accdb and Checkdatabase is on my DataDirectory
this is my code:

C#

private void button1_Click(object sender, EventArgs e)
        {
            //backup database
            OleDbConnection con1 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\backup.accdb;Persist Security Info=True");
            con1.Open();
            //main database
            OleDbConnection con2 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\CheckDatabase.accdb;Persist Security Info=True");
            con2.Open();
            string cmdstring = "INSERT INTO CheckDatabase.check SELECT * FROM backup.check";
            OleDbCommand cmd = new OleDbCommand(cmdstring, con1); ;
            cmd.ExecuteNonQuery();
            MessageBox.Show("restore complete");
            con1.Close();
            con2.Close();

1.when I run it, I get this error
System.Data.OleDb.OleDbException: 'Could not find file 'C:\Users\Admin\Desktop\HamrahPlus V2\HamrahPlus\bin\Debug\backup.mdb'.'
I made a specifice connection for each database but its still want to connect to my program roote to execute my query.
2.btw I dont know if this query will work or not!

I did change it to this but still not working:

C#

string destDb = @"|DataDirectory|\CheckDatabase.accdb";
            string sourceDb = @"D:\backup.accdb";

            OleDbConnection con = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={destDb};Persist Security Info=True");
            OleDbCommand cmd = new OleDbCommand($"INSERT INTO CheckDatabase.check SELECT * FROM [;DATABASE='{sourceDb}'].[check]", con);

            con.Open();
            cmd.ExecuteNonQuery();

What I have tried:

coding and searching around the web

Posted 19-Oct-22 8:47am

Ehsan Nabavi

Updated 21-Oct-22 1:23am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 19-Oct-22 15:22pm

You'll need to SELECT from one connection and INSERT on the other.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Ehsan Nabavi · Answer 1 · 2022-10-21T01:20:00

I made this code its working fine but when it get to the last row for insert I get this error because datagridview is empty now.
: 'Object reference not set to an instance of an object.' System.Windows.Forms.DataGridViewCell.Value.get returned null.

the whole insert query is working but at the same time this error pop up so I need to somehow stop the for loop before getting error

//select from backup database
            string sourceDb = @"D:\backup.accdb";
            OleDbConnection con1 = new OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={sourceDb};Persist Security Info=True");
            DataTable dt;
            con1.Open();
            OleDbDataAdapter adapter;
            dt = new DataTable();
            adapter = new OleDbDataAdapter("select * from [Mosavabeh]", con1);
            adapter.Fill(dt);
            dataGridView1.DataSource = dt;
            con1.Close();
            //insert into main database
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                OleDbConnection con2 = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\\CheckDatabase.accdb;Persist Security Info=True");
                con2.Open();
                string query = "Insert into [Mosavabeh] (ID,MosavabehDate,MosavabehNumber,LoanName,LoanType,LoanAmount)  VALUES(@ID, @MosavabehDate , @MosavabehNumber , @LoanName , @LoanType , @LoanAmount)";
                OleDbCommand cmd = new OleDbCommand(query);
                cmd = new OleDbCommand(query, con2);
                cmd.Parameters.AddWithValue("@ID", this.dataGridView1.Rows[i].Cells["ID"].Value.ToString());
                cmd.Parameters.AddWithValue("@MosavabehDate", this.dataGridView1.Rows[i].Cells["MosavabehDate"].Value.ToString());
                cmd.Parameters.AddWithValue("@MosavabehNumber", this.dataGridView1.Rows[i].Cells["MosavabehNumber"].Value.ToString());
                cmd.Parameters.AddWithValue("@LoanName", this.dataGridView1.Rows[i].Cells["LoanName"].Value.ToString());
                cmd.Parameters.AddWithValue("@LoanType", this.dataGridView1.Rows[i].Cells["LoanType"].Value.ToString());
                cmd.Parameters.AddWithValue("@LoanAmount", this.dataGridView1.Rows[i].Cells["LoanAmount"].Value.ToString());
                cmd.ExecuteNonQuery();
                con2.Close();
            }
            MessageBox.Show("restore complete");

Ehsan Nabavi · Answer 2 · 2022-10-21T01:23:00

Solution 2

I managed to fix this by making this

for (int i = 0; i == dataGridView1.Rows.Count; i++)

Posted 21-Oct-22 1:23am

Ehsan Nabavi

Comments

Richard MacCutchan 21-Oct-22 7:27am

I don't think that would do anything. Maybe you mean <= rather than ==.

Ehsan Nabavi 21-Oct-22 8:37am

yeah it seems that its now working
now I dont know how to stop this loop when it goes to 0 and there is no more rows

Richard MacCutchan 21-Oct-22 8:49am

I don't understand why your original code (see below) did not work.

for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
    // this should process every row in dataGridView1 
}

If you change it to

for (int i = 0; i <= dataGridView1.Rows.Count; i++)
{
    // this will process every row in dataGridView1 plus another, which is probably null, or does not exist
}

Ehsan Nabavi 21-Oct-22 12:57pm

yes it works and copy everything I want but at the end I get his error:
System.NullReferenceException: 'Object reference not set to an instance of an object.'

System.Windows.Forms.DataGridViewCell.Value.get returned null.

Ehsan Nabavi 21-Oct-22 13:50pm

did fix it by disabling datagridview add,edit and delete tasks and now its working fine