C# Transfer Data from ODBC Database to Local SQL Database

Question

I have connected to a DB through an ODBC connection. The data is on a server and I have the appropriate permissions and username/password.

I am trying to import some of the data into a local SQL database (.mdf). I suspect my SQL statement is wrong.

The idea is that when a an item is selected from a listBox that the data will be downloaded to the SQL database. This has completely stopped any progress on my project. Please help!!!

    public partial class frmNorth : Form
    {
            // variables for the connections 
            private OdbcConnection epnConnection = new OdbcConnection();
            private SqlConnection tempDbConnection = new SqlConnection();
    public frmNorth()
    {
        InitializeComponent();
        // This is for the ePN DB
        epnConnection.ConnectionString = @"Dsn=ePN; uid=username; pwd=myPa$$Word";
        // This is for the local DB
        tempDbConnection.ConnectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\TempDB.mdf;Integrated Security=True";
    }
    private void lbxFSR_SelectedIndexChanged(object sender, EventArgs e)
    {
        try //For ePN
        {
            //This is where I need the help <--------------------
            epnConnection.Open();
            tempDbConnection.Open();
            OdbcCommand epnCommamd = new OdbcCommand();
            epnCommamd.Connection = epnConnection;
            string epnQuery =   "INSERT INTO " + tempDbConnection + ".tblTemp (FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC) " +
                                "SELECT PROJ_FNCL_SPLIT.FNCL_SPLIT_REC_ID,PROJ_FNCL_SPLIT.PROJ_ID,PROJ_FNCL_SPLIT.SALES_SRC_PRC " + 
                                "FROM " + epnConnection + ".PROJ_FNCL_SPLIT " + 
                                "WHERE PROJ_ID=" + lbxFSR.Text + "";
            epnCommamd.CommandText = epnQuery;
            epnCommamd.CommandTimeout = 0;
            epnCommamd.ExecuteNonQuery();

            epnConnection.Close();
            tempDbConnection.Close();
        }
        catch (Exception ex)
        {
            epnConnection.Close();
            tempDbConnection.Close();
            MessageBox.Show("Error " + ex);
        }
    }
    }

This is the error that I get. The error occurs at epnCommamd.ExecuteNonQuery();

Picture of Error Message

Answer 1

The problem is that you can't in general INSERT into one table using a SELECT from a table on another database in the way that you're attempting. If source and destination tables are on same database server (eg both on Sql Server) you have a shot at INSERT INTO db1.SourceTable ... SELECT ... FROM db2.DestinationTable.

However, since you have source table on ODBC connection and destination on Sql connection, this won't work.

You need to do it in two steps. Download your ODBC table into a C# DataTable, then upload the C# DataTable into your Sql Server table. I can't test against your databases, but I have tested a version of this code on transfers between Microsoft Access database and a Sql Server database

private void lbxFSR_SelectedIndexChanged(object sender, EventArgs e)
{
    try //For ePN
    {
        //This is where I need the help <--------------------

        // Break the operation into two parts
        // The ODBC & SQL databases can't talk directly to each other.

        // 1. Download ODBC table into your C# DataTable

        DataTable dt;
        epnConnection.Open();
        string epnQuery =   "SELECT FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC " + 
                            "FROM PROJ_FNCL_SPLIT " + 
                            "WHERE PROJ_ID='" + lbxFSR.Text + "'";
        OdbcCommand epnCommamd = new OdbcCommand(epnQuery, epnConnection);
        epnCommamd.CommandTimeout = 0;
        OdbcDataReader dr = epnCommamd.ExecuteReader();
        dt.Load(dr);
        epnConnection.Close();

        // 2. Upload your C# DataTable to the SQL table

        // This select query tells the SqlDataAdapter what table you want to work with, on SQL database
        // The WHERE 0 = 1 clause is to stop it returning any rows, 
        // however you still get the column names & datatypes which you need to perform the update later
        string selectQuery = "SELECT FNCL_SPLIT_REC_ID, PROJ_ID, SALES_SRC_PRC " +
                            " FROM PROJ_FNCL_SPLIT WHERE 0 = 1";
        tempDbConnection.Open();
        var da = new SqlDataAdapter(selectQuery, tempDbConnection);
        var commandBuilder = new SqlCommandBuilder(da);
        // The DataAdapter's `Update` method applies the contents of the DataTable `dt` to the table specified in the `selectQuery`.
        // It does this via the SqlCommandBuilder, which knows how to apply updates to a Sql Database.
        da.Update(dt);                                      // Channel the C# DataTable through the DataAdapter
        tempDbConnection.Close();
    }
    catch (Exception ex)
    {
        epnConnection.Close();
        tempDbConnection.Close();
        MessageBox.Show("Error " + ex);
    }
}

Answer 2

I cant comment cause i don't have enough points so i have to put this in answers but do both of your connections actually open? I would also avoid showing passwords in your connection strings on here.