OleDB Dataset to Sql Dataset

Question

I am pulling data from a .mdb table with an OleDbConnection. I need to write the data to and SQL server table.

The read works fine and I can connect with SqlConnection fine. I see the row count at 288 and column count at 38, which indicates to me the data was successfully retrieved.

Ultimately however, no data reaches the SQL table.

public DataTable Forecast2(string fileName)
    {
        string connectionString = @provider + "; Data Source=" + fileLoc + fileName;
        string strSQL = "Select STATUS_DT, MATL_NO, FRCST01_1, FRCST01_2, FRCST02_1, FRCST02_2, FRCST03_1, FRCST03_2, " +
                        "FRCST04_1, FRCST04_2, FRCST05_1, FRCST05_2, FRCST06_1, FRCST06_2, FRCST07_1, FRCST07_2, " +
                        "FRCST08_1, FRCST08_2, FRCST09_1, FRCST09_2, FRCST10_1, FRCST10_2, FRCST11_1, FRCST11_2, " +
                        "FRCST12_1, FRCST12_2, FRCST13_1, FRCST13_2, FRCST14_1, FRCST14_2, FRCST15_1, FRCST15_2, " +
                        "FRCST16_1, FRCST16_2, FRCST17_1, FRCST17_2, FRCST18_1, FRCST18_2 " +
                        "FROM Forecast " +
                        "WHERE DESC = 'Reqmt' " +
                        "ORDER BY MATL_NO";

        OleDbConnection connection = new OleDbConnection(connectionString);

        connection.Open();
        OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSQL, connection);
        DataSet ds = new DataSet();
        dataAdapter.Fill(ds);

        using (SqlConnection con = new SqlConnection(SqlConStr))
        {
            con.Open();
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
            {
                foreach (DataColumn c in ds.Tables[0].Columns)
                    bulkCopy.ColumnMappings.Add(c.ColumnName, c.ColumnName);

                bulkCopy.DestinationTableName = "dbo.CustomerForecast";
                try
                {
                    DataTable dt = ds.Tables[0]; 
                    //Console.WriteLine(dt.Rows.Count);
                    //Console.WriteLine(dt.Columns.Count);
                    bulkCopy.WriteToServer(dt);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
        }
        return ds.Tables[0];
    }

The SQL table name exists and the correct schema is used. The SQL table has more columns than the DataTable, but I map the columns so I am not thinking that should matter. All columns in the SQL table have default values.

Regards,
Lance

Answer 1

karenpayneoregon set me on the correct path with her initial question. Yes rijwanansari, you too suspected a similar issue.

This was a great exercise as I improved mapping, column naming, adding, removing, sorting, etc. using DataSets, DataTables, and DataViews.

Thanks everyone.

Regards,
Lance

Answer 2

Hi @Lance James

Your code seems correct to me. I suspect on column mapping. Would please verify the column name mapping in destination table.
Also, in last, please close the connection.