have a Problem in C# windows program that is I can't work with 2 database those are simple database in tables and Data. in this Project I want to open Access DataBase and Create New Access DataBase with another Name But with Same Tables And Columns And Rows and fill with Source Data that is in Source Database.

I can't read from source DB and insert into new destination DataBase. the sorce code is below please Help me to Complete this Project, thanks a lot.

private void button3_Click(object sender, EventArgs e)
{
    OleDbConnection cn = new OleDbConnection();
    cn.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + openFileDialog1.FileName;
    OleDbCommand cmd = new OleDbCommand();
    cn.Open();
    DataTable table = cn.GetSchema("Tables");

    int i = 0;
    foreach (System.Data.DataRow row in table.Rows)
    {
        if ((string)row["TABLE_TYPE"] == "TABLE")
        {
            comboBox1.Items.Add(row["TABLE_NAME"]);
            Tables[i] = row["TABLE_NAME"].ToString();
            listBox1.Items.Add(Tables[i]);
            i++;
            n++;
        }
    }
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
    OleDbConnection conne = new OleDbConnection();
    conne.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + openFileDialog1.FileName;
    conne.Open();
    OleDbCommand cmd = new OleDbCommand();
    cmd.Connection = conne;
    DataTable Dt = new DataTable();
    cmd.CommandText = "select * from " + comboBox1.Text;
    OleDbDataAdapter adapter = new OleDbDataAdapter();
    adapter.SelectCommand = cmd;
    adapter.Fill(Dt);
    dataGridView1.DataSource = Dt;
    dataGridView1.Visible = true;
    conne.Close();
}
private void button2_Click(object sender, EventArgs e)
{
    saveFileDialog1.Filter = "accdb|*.accdb";
    saveFileDialog1.Title = "Save Access DataBase File";
    saveFileDialog1.FileName = strFileName;
    saveFileDialog1.ShowDialog();
    System.IO.File.Copy(openFileDialog1.FileName, saveFileDialog1.FileName);

    ADOX.Catalog cat = new ADOX.Catalog();
    cat.Create("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + saveFileDialog1.FileName);
    Console.WriteLine("Database Created Successfully");
    OleDbConnection connsave = new OleDbConnection();
    connsave.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + saveFileDialog1.FileName;
    connsave.Open();
    OleDbCommand cmdsave = new OleDbCommand();
    cmdsave.Connection = connsave;

    OleDbConnection connopen = new OleDbConnection();
    connopen.ConnectionString = @"provider=Microsoft.ACE.OLEDB.12.0;" + @"data source=" + openFileDialog1.FileName;
    connopen.Open();
    OleDbCommand cmdopen = new OleDbCommand();
    cmdopen.Connection = connopen;

    int i = 0;

    foreach (string strtablename in Tables)
    {
        if (i < n)
        {
            cmdsave.CommandText = "CREATE TABLE [" + Tables[i] + "]";
            cmdsave.ExecuteNonQuery();
            cmdsave.CommandText = "DELETE FROM [" + Tables[i] + "]";
            cmdsave.ExecuteNonQuery();
            cmdopen.CommandText = "SELECT * FROM [" + Tables[i] + "]";
            cmdopen.ExecuteNonQuery();
            cmdsave.CommandText = "INSErT INTO [" + Tables[i] + "]";
            cmdsave.ExecuteNonQuery();                       
            i++;
        }
    }
    connopen.Close();
    connsave.Close();
    textBox2.Text = saveFileDialog1.FileName.ToString();
    MessageBox.Show("DataBase Save Sucessfull in \"" + textBox2.Text + "\"");
}

Could you be more specific about what is not working? Also, dumb question, but couldn't you just copy and paste the file?

i cant use the code for copy . I shulde be use only sql statements for example SELECT,INSERT , ...
these code DELETE FROM & "SELECT * FROM " & INSERT INTO dos not Work
PLZ give me a code to read or fetch from one database and insert into new database.

What doesn't work? Are you getting any errors/exceptions? I'm not surprised it's not working, but I'm not going to do your work for you. You should go through some SQL tutorials, for starters. This ones not bad: http://w3schools.com/sql/default.asp.

Edited 4 Years Ago by nmaillet

Hi,
Correct me if I am wrong. You have created a table, but did not add any columns to that table.

Regards

one more....
You are selecting data like:

select * from tablename

and it your command:

INSERT INTO tablename

my question is - what are you inserting to you new table?

regards

K... it's pretty clear what you want to do, and repeating it again doesn't really help. I also think it's pretty clear that you don't have a fundemental understanding of SQL, or C# for that matter. For instance, your foreach statement is a mess, you could do it one of two ways:

foreach (string strtablename in Tables)
{
    cmdsave.CommandText = "CREATE TABLE [" + strtablename + "]";
    cmdsave.ExecuteNonQuery();
    ...
}

or:

for (int i = 0; i < Tables.Length; i++)
                    //or Tables.Count depending on the type.
{
    cmdsave.CommandText = "CREATE TABLE [" + Tables[i] + "]";
    cmdsave.ExecuteNonQuery();
    ...
}

Although that weird hybrid could work, you really shouldn't; it's inefficient, unnecessary and just bad practice.

Second, take a look at the INSERT statement. It requires VALUES to insert into the table (as Rogachev mentioned). How is it supposed to know what you want to put in there?

PLZ give me a code to read or fetch from one database and insert into new database.

I certainly will not. That's your job, not mine. I gave you a link to a good quick SQL tutorial, which will show you how to properly format your SQL queries; so how's about you take a look at it, and try fixing your code?

This article has been dead for over six months. Start a new discussion instead.