Click here to Skip to main content
15,667,885 members
Please Sign up or sign in to vote.
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
Updated 21-Oct-22 1:23am
v2
Comments
PIEBALDconsult 19-Oct-22 15:22pm    
You'll need to SELECT from one connection and INSERT on the other.

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");
 
Share this answer
 
I managed to fix this by making this
for (int i = 0; i == dataGridView1.Rows.Count; i++)
 
Share this answer
 
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

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