none
Inserting data from one database to another RRS feed

  • Question

  •  

    Hi,

     

    I am tasked with writing a .net c# (VS 2005) application that selects data from an MS Access mdb (full of data) and inserts the data into a MySQL db (has no data).  The tables in each database are identical.  I am using ODBC drivers for both connections.

     

    I had done something similar on MS sql server and it worked...but I'm having problems with this.  Here is my code:

     

    I do not get any errors or exceptions.

     

    Code Snippet

     

    OdbcConnection obj_MySQLConnection = new OdbcConnection("dsn=ACFMySQL");

    try

    {

    obj_MySQLConnection.Open();

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.Message);

    return;

    }

    //connect to the acf mdb

    OdbcConnection obj_AccessConnection = new OdbcConnection("dsn=TestACF");

    try

    {

    obj_AccessConnection.Open();

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.Message);

    return;

    }

     

     

    //full data from access

    DataTable obj_FullTable = new DataTable("SO_LOST_SLS_REAS");

    OdbcDataAdapter obj_FullAdapter = new OdbcDataAdapter("SELECT * FROM SO_LOST_SLS_REAS", obj_AccessConnection);

     

    obj_FullAdapter.Fill(obj_FullTable);

     

     

    //empty table for meta data so the command builder can work

    DataTable obj_EmptyTable = new DataTable("SO_LOST_SLS_REAS");

    OdbcDataAdapter obj_EmptyAdapter = new OdbcDataAdapter("SELECT * FROM SO_LOST_SLS_REAS", obj_MySQLConnection);

    obj_EmptyAdapter.Fill(obj_EmptyTable);

     

     

    OdbcCommandBuilder obj_CmdBuilder = new OdbcCommandBuilder(obj_EmptyAdapter);

    obj_EmptyAdapter.InsertCommand = new OdbcCommand();

    obj_EmptyAdapter.InsertCommand.Connection = obj_MySQLConnection;

    obj_EmptyAdapter.InsertCommand = obj_CmdBuilder.GetInsertCommand();

     

    int i = obj_EmptyAdapter.Update(obj_FullTable);

    //i is always 0 and the data is not moved

     

     

    Questions...

     

    Is there something wrong with my code that is causing it to fail?

    Is this a good way to accomplish my goal or is there a better way?  I do not know the structure of the tables, so a hard coded solution is not viable.

     

    Thank you in advance for any help/advice!

    Paula

    Tuesday, June 10, 2008 12:42 PM

Answers

  • You might want to try the following method:

    http://www.knowdotnet.com/articles/datasetmerge.html



    Tuesday, June 10, 2008 6:52 PM

All replies