program runs fine with no errors but not giving me the result i want

Question

I am trying to take the data from my datatable and sqlbulkcopy it to a temp table then from temp table insert to the main table but when I run the program it runs through but it is not populating the end database table:

static void Main(string[] args)
{
    string straccessconnection = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= T:\Aravind\Company Master Data.accdb";

    string strItemMasterSelect = "SELECT * FROM [Item Master]";
    DataSet DataSet1 = new DataSet();
    OleDbConnection myAccessConn = null;
    try
    {
        myAccessConn = new OleDbConnection(straccessconnection);
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
        return;
    }

    try
    {
        OleDbCommand myAccessCommand = new OleDbCommand(strItemMasterSelect, myAccessConn);
        OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);

        myAccessConn.Open();
        myDataAdapter.Fill(DataSet1, "itemmaster");
        DataTable itemmaster;
        itemmaster = DataSet1.Tables["itemmaster"];
        foreach (DataRow drcurrent in itemmaster.Rows)
        {
            Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7} {8} {9} {10} {11} {12} {13} {14} {15} {16} {17} {18} {18} {19} {20} {21} {22} {23} {24} {25} {26} {27} {28} {29} {30} {31} {32} {33} {34} {35} {36} {37} {38} {39} {40} {41} {42} {43} {44} {45} {46} {47} {48} {49} {50} {51} {52} {53} {54} {55} {56} {57} {58} {59} {60} {61} {62} {63} {64} {65} {66} {67} {68} {69} {70} {71} {72} {73} {74} {75} {76} {77} {78} {79} {80} {81} {82} {83} {84} {85} {86} {87} {88} {89} {90} {91} {92} {93} {94} {95} {96} {97} {98} {99} {100} {101} {102} {103} {104} {105} {106} {107} {108} {109} {110} {111} {112} {113} {114} {115} {116}",
                drcurrent["Item"].ToString(),
                drcurrent["Description"].ToString(),
                drcurrent["Item Type"].ToString(),
                drcurrent["Item Group"].ToString(),
                drcurrent["Product Type"].ToString(),
                drcurrent["Selection Code"].ToString(),
                drcurrent["Standard Cost Price"].ToString(),
                drcurrent["Inventory Unit"].ToString(),
                drcurrent["Purchase Price"].ToString(),
                drcurrent["Purchase Price Unit"].ToString(),
                drcurrent["Latest Purchase Price"].ToString(),
                drcurrent["Sales Price"].ToString(),
                drcurrent["Sales Price Unit"].ToString(),
                drcurrent["Phantom"].ToString(),
                drcurrent["Scrap Factor"].ToString(),
                drcurrent["Weight"].ToString(),
                drcurrent["Signal Code"].ToString(),
                drcurrent["Cost Price Component"].ToString(),
                drcurrent["Last CP Trans Date"].ToString(),
                drcurrent["Unit Set"].ToString(),
                drcurrent["Inventory on Hand"].ToString(),
                drcurrent["Purchase Unit"].ToString(),
                drcurrent["Sales Unit"].ToString(),
                drcurrent["Price Group"].ToString(),
                drcurrent["Statistics Group"].ToString(),
                drcurrent["Commission Group"].ToString(),
                drcurrent["Rebate Group"].ToString(),
                drcurrent["Sugg Ret Price"].ToString(),
                drcurrent["Units per Carton"].ToString(),
                drcurrent["Gross Weight"].ToString(),
                drcurrent["Net Weight"].ToString(),
                drcurrent["Freight Class"].ToString(),
                drcurrent["Unit UPC"].ToString(),
                drcurrent["Case UPC"].ToString(),
                drcurrent["Retail/Misc only"].ToString(),
                drcurrent["Additional Block"].ToString(),
                drcurrent["Display Boxes"].ToString(),
                drcurrent["Best Before"].ToString(),
                drcurrent["Reportable"].ToString(),
                drcurrent["Package (Enum)"].ToString(),
                drcurrent["Package"].ToString(),
                drcurrent["Style (Enum)"].ToString(),
                drcurrent["Style"].ToString(),
                drcurrent["Heart (Enum)"].ToString(),
                drcurrent["Heart"].ToString(),
                drcurrent["Basket (Enum)"].ToString(),
                drcurrent["Basket"].ToString(),
                drcurrent["Sugar Free (Enum)"].ToString(),
                drcurrent["Sugar Free"].ToString(),
                drcurrent["Flatback (Enum)"].ToString(),
                drcurrent["Flatback"].ToString(),
                drcurrent["Multipack (Enum)"].ToString(),
                drcurrent["Multipack"].ToString(),
                drcurrent["Displayer (Enum)"].ToString(),
                drcurrent["Displayer"].ToString(),
                drcurrent["Cross Shippable"].ToString(),
                drcurrent["Freezer Item"].ToString(),
                drcurrent["Novelty Flag"].ToString(),
                drcurrent["Normal/Special/Multipack"].ToString(),
                drcurrent["Component #1"].ToString(),
                drcurrent["Component #1Units"].ToString(),
                drcurrent["Component #2"].ToString(),
                drcurrent["Component #2Units"].ToString(),
                drcurrent["Component #3"].ToString(),
                drcurrent["Component #3Units"].ToString(),
                drcurrent["Component #4"].ToString(),
                drcurrent["Component #4Units"].ToString(),
                drcurrent["Component #5"].ToString(),
                drcurrent["Component #5Units"].ToString(),
                drcurrent["Component #6"].ToString(),
                drcurrent["Component #6Units"].ToString(),
                drcurrent["Component #7"].ToString(),
                drcurrent["Component #7Units"].ToString(),
                drcurrent["Component #8"].ToString(),
                drcurrent["Component #8Units"].ToString(),
                drcurrent["Component #9"].ToString(),
                drcurrent["Component #9Units"].ToString(),
                drcurrent["Component #10"].ToString(),
                drcurrent["Component #10Units"].ToString(),
                drcurrent["Component #11"].ToString(),
                drcurrent["Component #11Units"].ToString(),
                drcurrent["Component #12"].ToString(),
                drcurrent["Component #12Units"].ToString(),
                drcurrent["Component #13"].ToString(),
                drcurrent["Component #13Units"].ToString(),
                drcurrent["Component #14"].ToString(),
                drcurrent["Component #14Units"].ToString(),
                drcurrent["Component #15"].ToString(),
                drcurrent["Component #15Units"].ToString(),
                drcurrent["Component #16"].ToString(),
                drcurrent["Component #16Units"].ToString(),
                drcurrent["Component #17"].ToString(),
                drcurrent["Component #18"].ToString(),
                drcurrent["Component #18Units"].ToString(),
                drcurrent["Component #19"].ToString(),
                drcurrent["Component #19Units"].ToString(),
                drcurrent["Component #20"].ToString(),
                drcurrent["Component #20Units"].ToString(),
                drcurrent["Unit Weight lbs"].ToString(),
                drcurrent["Unit Weight ozs"].ToString(),
                drcurrent["Unit Weight Grams"].ToString(),
                drcurrent["Prepriced"].ToString(),
                drcurrent["Country of Sale"].ToString(),
                drcurrent["Egg"].ToString(),
                drcurrent["Pallet Spec  Wrapped"].ToString(),
                drcurrent["Pallet Spec  Non-Wrapped"].ToString(),
                drcurrent["Stack Height"].ToString(),
                drcurrent["Scan Unit UPC (SO/PO)"].ToString(),
                drcurrent["Budget Category"].ToString(),
                drcurrent["Budget Category description"].ToString(),
                drcurrent["Base Item"].ToString(),
                drcurrent["registered Item"].ToString(),
                drcurrent["Material"].ToString(),
                drcurrent["Size"].ToString(),
                drcurrent["Standard"].ToString(),
                drcurrent["Sales Price (CAD)"].ToString(),
                drcurrent["Suggested Retail Price (CAD)"].ToString());
        }

        if (itemmaster.Rows.Count > 0)
        {
            string strsqlconnection = "Data Source=KCMJF1XTR1\\SQLEXPRESS;Initial Catalog=AccessMaster1;Integrated Security=True";

            using (SqlConnection con = new SqlConnection(strsqlconnection))
            {
                SqlTransaction transaction = null;
                try
                {
                    con.Open();
                    SqlCommand DbCommand = new SqlCommand("CREATE TABLE #ItemMaster( [Item] [nvarchar](255) NOT NULL,[Description] [nvarchar](255) NULL,[Item Type] [nvarchar](255) NULL,[Item Group] [nvarchar](50) NULL,[Product Type] [nvarchar](50) NULL,[Selection Code] [nvarchar](255) NULL,[Standard Cost Price] [float] NULL,[Inventory Unit] [nvarchar](255) NULL,[Purchase Price] [float] NULL, [Purchase Price Unit] [nvarchar](255) NULL,[Latest Purchase Price] [float] NULL,[Sales Price] [float] NULL,[Sales Price Unit] [nvarchar](255) NULL, [Phantom] [nvarchar](255) NULL,[Scrap Factor] [float] NULL,[Weight] [float] NULL,[Signal Code] [nvarchar](3) NULL,[Cost Price Component] [nvarchar](3) NULL,[Last CP Trans Date] [nvarchar](50) NULL, [Unit Set] [nvarchar](50) NULL,[Inventory on Hand] [float] NULL,[Purchase Unit] [nvarchar](50) NULL,[Sales Unit] [nvarchar](50) NULL,[Price Group] [nvarchar](50) NULL,[Statistics Group] [nvarchar](50) NULL,[Commission Group] [nvarchar](50) NULL,[Rebate Group] [nvarchar](50) NULL,[Sugg Ret Price] [float] NULL,[Units per Carton] [float] NULL, [Gross Weight] [float] NULL,[Net Weight] [float] NULL,[Freight Class] [nvarchar](50) NULL,[Unit UPC] [nvarchar](50) NULL,[Case UPC] [nvarchar](50) NULL,[Retail/Misc only] [nvarchar](50) NULL, [Additional Block] [nvarchar](50) NULL,[Display Boxes] [nvarchar](50) NULL,[Best Before] [nvarchar](50) NULL,[Reportable] [nvarchar](50) NULL,[Package (Enum)] [nvarchar](50) NULL,[Package] [nvarchar](50) NULL, [Style (Enum)] [nvarchar](50) NULL,[Style] [nvarchar](50) NULL, [Heart (Enum)] [nvarchar](50) NULL,[Heart] [nvarchar](50) NULL,[Basket (Enum)] [nvarchar](50) NULL,[Basket] [nvarchar](50) NULL,[Sugar Free (Enum)] [nvarchar](50) NULL, [Sugar Free] [nvarchar](50) NULL,[Flatback (Enum)] [nvarchar](50) NULL,[Flatback] [nvarchar](50) NULL,[Multipack (Enum)] [nvarchar](50) NULL,[Multipack] [nvarchar](50) NULL,[Displayer (Enum)] [nvarchar](50) NULL,[Displayer] [nvarchar](50) NULL,[Cross Shippable] [nvarchar](50) NULL,[Freezer Item] [nvarchar](50) NULL,[Novelty Flag] [nvarchar](50) NULL,[Normal/Special/Multipack] [nvarchar](50) NULL, [Component #1] [nvarchar](50) NULL,[Component #1Units] [int] NULL,[Component #2] [nvarchar](50) NULL, [Component #2Units] [int] NULL,[Component #3] [nvarchar](50) NULL,[Component #3Units] [int] NULL,[Component #4] [nvarchar](50) NULL,[Component #4Units] [int] NULL,[Component #5] [nvarchar](50) NULL,[Component #5Units] [int] NULL,[Component #6] [nvarchar](50) NULL,[Component #6Units] [int] NULL,[Component #7] [nvarchar](50) NULL,[Component #7Units] [int] NULL,[Component #8] [nvarchar](50) NULL,[Component #8Units] [int] NULL,[Component #9] [nvarchar](50) NULL,[Component #9Units] [int] NULL,[Component #10] [nvarchar](50) NULL,[Component #10Units] [int] NULL,[Component #11] [nvarchar](50) NULL,[Component #11Units] [int] NULL,[Component #12] [nvarchar](50) NULL, [Component #12Units] [int] NULL,[Component #13] [nvarchar](50) NULL,[Component #13Units] [int] NULL,[Component #14] [nvarchar](50) NULL,[Component #14Units] [int] NULL,[Component #15] [nvarchar](50) NULL,[Component #15Units] [int] NULL,[Component #16] [nvarchar](50) NULL,[Component #16Units] [int] NULL,[Component #17] [nvarchar](50) NULL,[Component #17Units] [int] NULL,[Component #18] [nvarchar](50) NULL,[Component #18Units] [int] NULL,[Component #19] [nvarchar](50) NULL,[Component #19Units] [int] NULL,[Component #20] [nvarchar](50) NULL,[Component #20Units] [int] NULL,[Unit Weight lbs] [int] NULL,[Unit Weight ozs] [int] NULL,[Unit Weight Grams][int] NULL,[Prepriced][nvarchar](50) NULL,[Country of Sale][nvarchar](50) NULL,[Egg] [nvarchar](50) NULL,[Pallet Spec  Wrapped] [nvarchar](50) NULL,[Pallet Spec  Non-Wrapped][nvarchar](50) NULL,[Stack Height][int] NULL,[Scan Unit UPC (SO/PO)][nvarchar](50) NULL,[Budget Category] [int] NULL,[Budget Category description] [nvarchar](50) NULL,[Base Item] [nvarchar](255) NULL, [registered Item] [int] NULL,[Material] [nvarchar](8) NULL,[Size] [nvarchar](12) NULL,[Standard] [nvarchar](8) NULL,[Sales Price (CAD)] [money] NULL,[Suggested Retail Price (CAD)] [money] NULL);", con);
                    DbCommand.ExecuteNonQuery();

                    transaction = con.BeginTransaction(IsolationLevel.ReadCommitted);


                    using (System.Data.SqlClient.SqlBulkCopy s = new System.Data.SqlClient.SqlBulkCopy(con, SqlBulkCopyOptions.Default, transaction))
                    {//load fedex supplied data into temporary table

                        s.DestinationTableName = "#ItemMaster";
                        s.BatchSize = 100;
                        s.NotifyAfter = 100;
                        s.WriteToServer(itemmaster);
                        s.Close();

                        string sqlTrunc = "TRUNCATE TABLE " + "[AccessMaster1].[dbo].[Item Master]";
                        SqlCommand cmd = new SqlCommand(sqlTrunc, con);
                        cmd.Transaction = transaction;
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = "INSERT INTO [AccessMaster1].[dbo].[Item Master]([Item],[Description],[Item Type],[Item Group] ,[Product Type],[Selection Code] ,[Standard Cost Price],[Inventory Unit] ,[Purchase Price],[Purchase Price Unit],[Latest Purchase Price] ,[Sales Price],[Sales Price Unit],[Phantom],[Scrap Factor],[Weight],[Signal Code],[Cost Price Component],[Last CP Trans Date] ,[Unit Set] ,[Inventory on Hand] ,[Purchase Unit],[Sales Unit],[Price Group] ,[Statistics Group],[Commission Group] ,[Rebate Group],[Sugg Ret Price] ,[Units per Carton],[Gross Weight],[Net Weight],[Freight Class],[Unit UPC],[Case UPC] ,[Retail/Misc only],[Additional Block],[Display Boxes],[Best Before] ,[Reportable],[Package (Enum)] ,[Package] ,[Style (Enum)],[Style],[Heart (Enum)],[Heart],[Basket (Enum)],[Basket],[Sugar Free (Enum)],[Sugar Free],[Flatback (Enum)],[Flatback],[Multipack (Enum)],[Multipack],[Displayer (Enum)],[Displayer],[Cross Shippable],[Freezer Item],[Novelty Flag] ,[Normal/Special/Multipack],[Component #1],[Component #1Units],[Component #2],[Component #2Units],[Component #3],[Component #3Units],[Component #4],[Component #4Units],[Component #5] ,[Component #5Units],[Component #6],[Component #6Units],[Component #7],[Component #7Units],[Component #8],[Component #8Units],[Component #9],[Component #9Units],[Component #10],[Component #10Units] ,[Component #11],[Component #11Units] ,[Component #12],[Component #12Units],[Component #13],[Component #13Units],[Component #14],[Component #14Units],[Component #15],[Component #15Units],[Component #16],[Component #16Units],[Component #17],[Component #17Units],[Component #18] ,[Component #18Units],[Component #19],[Component #19Units],[Component #20],[Component #20Units],[Unit Weight lbs],[Unit Weight ozs] ,[Unit Weight Grams],[Prepriced],[Country of Sale],[Egg] ,[Pallet Spec  Wrapped],[Pallet Spec  Non-Wrapped],[Stack Height],[Scan Unit UPC (SO/PO)],[Budget Category],[Budget Category description],[Base Item],[registered Item],[Material],[Size],[Standard],[Sales Price (CAD)],[Suggested Retail Price (CAD)]) SELECT Z.[Item],Z.[Description],Z.[Item Type],Z.[Item Group] ,Z.[Product Type],Z.[Selection Code] ,Z.[Standard Cost Price],Z.[Inventory Unit] ,Z.[Purchase Price],Z.[Purchase Price Unit],Z.[Latest Purchase Price] ,Z.[Sales Price],Z.[Sales Price Unit],Z.[Phantom],Z.[Scrap Factor],Z.[Weight],Z.[Signal Code],Z.[Cost Price Component],Z.[Last CP Trans Date] ,Z.[Unit Set] ,Z.[Inventory on Hand] ,Z.[Purchase Unit],Z.[Sales Unit],Z.[Price Group] ,Z.[Statistics Group],Z.[Commission Group] ,Z.[Rebate Group],Z.[Sugg Ret Price] ,Z.[Units per Carton],Z.[Gross Weight],Z.[Net Weight],Z.[Freight Class],Z.[Unit UPC],Z.[Case UPC] ,Z.[Retail/Misc only],Z.[Additional Block],Z.[Display Boxes],Z.[Best Before] ,Z.[Reportable],Z.[Package (Enum)] ,Z.[Package] ,Z.[Style (Enum)],Z.[Style],Z.[Heart (Enum)],Z.[Heart],Z.[Basket (Enum)],Z.[Basket],Z.[Sugar Free (Enum)],Z.[Sugar Free],Z.[Flatback (Enum)],Z.[Flatback],Z.[Multipack (Enum)],Z.[Multipack],Z.[Displayer (Enum)],Z.[Displayer],Z.[Cross Shippable],Z.[Freezer Item],Z.[Novelty Flag] ,Z.[Normal/Special/Multipack],Z.[Component #1],Z.[Component #1Units],Z.[Component #2],Z.[Component #2Units],Z.[Component #3],Z.[Component #3Units],Z.[Component #4],Z.[Component #4Units],Z.[Component #5] ,Z.[Component #5Units],Z.[Component #6],Z.[Component #6Units],Z.[Component #7],Z.[Component #7Units],Z.[Component #8],Z.[Component #8Units],Z.[Component #9],Z.[Component #9Units],Z.[Component #10],Z.[Component #10Units] ,Z.[Component #11],Z.[Component #11Units] ,Z.[Component #12],Z.[Component #12Units],Z.[Component #13],Z.[Component #13Units],Z.[Component #14],Z.[Component #14Units],Z.[Component #15],Z.[Component #15Units],Z.[Component #16],Z.[Component #16Units],Z.[Component #17],Z.[Component #17Units],Z.[Component #18] ,Z.[Component #18Units],Z.[Component #19],Z.[Component #19Units],Z.[Component #20],Z.[Component #20Units],Z.[Unit Weight lbs],Z.[Unit Weight ozs] ,Z.[Unit Weight Grams],Z.[Prepriced],Z.[Country of Sale],Z.[Egg] ,Z.[Pallet Spec  Wrapped],Z.[Pallet Spec  Non-Wrapped],Z.[Stack Height],Z.[Scan Unit UPC (SO/PO)],Z.[Budget Category],Z.[Budget Category description],Z.[Base Item],Z.[registered Item],Z.[Material],Z.[Size],Z.[Standard],Z.[Sales Price (CAD)],Z.[Suggested Retail Price (CAD)] FROM #ItemMaster Z";
                    }
                    transaction.Commit();
                }

                catch (Exception ex)
                {
                    if (transaction != null) transaction.Rollback();
                    Console.WriteLine(ex.Message);
                }
            }

        }

        int numofrecords = itemmaster.Rows.Count;

        Console.WriteLine("Total Records Loaded into Data Table are" + "-" + numofrecords.ToString());
        Console.ReadLine();
    }
    catch (Exception ex)
    {
        Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
        return;
    }
    finally
    {
        myAccessConn.Close();
    }
}

I tried to debug the program but now able to see any errors. Can someone please tell me where I am making a mistake in my code?

Maybe you could make your code more readable, e.g. by removing the huge writeline statement and some error handling? — Paolo Tedesco, May 02 '15 at 19:10
I agree, please remove the try catch sections and run it again — Paolo Costa, May 02 '15 at 19:10
I formatted the code for you, but as has been mentioned, if you're going to come looking for help at least put in some effort in formatting the code so it's readable. — Craig W., May 02 '15 at 19:14
Have you considered [this](https://www.youtube.com/watch?v=0-TS8SDUmMY)? — yazanpro, May 02 '15 at 19:27
I am unable to recreate your issue. The details are [here](http://pastebin.com/zasd6qpT) if you want to try it for yourself and see if there are significant differences between my attempt at an [MCVE](http://stackoverflow.com/help/mcve) and your actual code. — Gord Thompson, May 02 '15 at 23:17
@PaoloCosta i tried removing the try catch didnt help the issue — HackGod555, May 02 '15 at 23:26
@yazanpro i tried the breakpoint, it runs through all code with out and errors — HackGod555, May 02 '15 at 23:27
@GordThompson Thanks bud that exactly what i was looking for, i used your example and rebuilt my code, it runs good and my end tables are getting populated. — HackGod555, May 04 '15 at 03:58

score 1 · Answer 1 · answered May 04 '15 at 04:05

This is the changed code that works!!

static void accesstosqlitemmaster()
{
 var dt = new System.Data.DataTable();
string accdbConnStr =  @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= T:\Company Master Data.accdb";
using (var accdbConn = new OleDbConnection(accdbConnStr))
{
using (var da = new OleDbDataAdapter("SELECT * FROM [Item Master]", accdbConn))
{
da.Fill(dt);  
}
}
 Console.WriteLine("DataTable filled from Access db ItemMaster - Row count: {0}", dt.Rows.Count,DateTime.Now.ToString());
string sqlConnStr = @"Data Source=KCMJF1XTR1\SQLEXPRESS;Initial Catalog=AccessMaster1;Integrated Security=True";
using (var sqlConn = new SqlConnection(sqlConnStr))
{
sqlConn.Open();
using (var cmd = new SqlCommand())
{
 cmd.Connection = sqlConn;
   cmd.CommandText = "CREATE TABLE #ItemMaster ( [Item] [nvarchar](255) NOT NULL,[Description] [nvarchar](255) NULL,[Item Type] [nvarchar](255) NULL,[Item Group] [nvarchar](50) NULL,[Product Type] [nvarchar](50) NULL,[Selection Code] [nvarchar](255) NULL,[Standard Cost Price] [float] NULL,[Inventory Unit] [nvarchar](255) NULL,[Purchase Price] [float] NULL, [Purchase Price Unit] [nvarchar](255) NULL,[Latest Purchase Price] [float] NULL,[Sales Price] [float] NULL,[Sales Price Unit] [nvarchar](255) NULL, [Phantom] [nvarchar](255) NULL,[Scrap Factor] [float] NULL,[Weight] [float] NULL,[Signal Code] [nvarchar](3) NULL,[Cost Price Component] [nvarchar](3) NULL,[Last CP Trans Date] [nvarchar](50) NULL, [Unit Set] [nvarchar](50) NULL,[Inventory on Hand] [float] NULL,[Purchase Unit] [nvarchar](50) NULL,[Sales Unit] [nvarchar](50) NULL,[Price Group] [nvarchar](50) NULL,[Statistics Group] [nvarchar](50) NULL,[Commission Group] [nvarchar](50) NULL,[Rebate Group] [nvarchar](50) NULL,[Sugg Ret Price] [float] NULL,[Units per Carton] [float] NULL, [Gross Weight] [float] NULL,[Net Weight] [float] NULL,[Freight Class] [nvarchar](50) NULL,[Unit UPC] [nvarchar](50) NULL,[Case UPC] [nvarchar](50) NULL,[Retail/Misc only] [nvarchar](50) NULL, [Additional Block] [nvarchar](50) NULL,[Display Boxes] [nvarchar](50) NULL,[Best Before] [nvarchar](50) NULL,[Reportable] [nvarchar](50) NULL,[Package (Enum)] [nvarchar](50) NULL,[Package] [nvarchar](50) NULL, [Style (Enum)] [nvarchar](50) NULL,[Style] [nvarchar](50) NULL, [Heart (Enum)] [nvarchar](50) NULL,[Heart] [nvarchar](50) NULL,[Basket (Enum)] [nvarchar](50) NULL,[Basket] [nvarchar](50) NULL,[Sugar Free (Enum)] [nvarchar](50) NULL, [Sugar Free] [nvarchar](50) NULL,[Flatback (Enum)] [nvarchar](50) NULL,[Flatback] [nvarchar](50) NULL,[Multipack (Enum)] [nvarchar](50) NULL,[Multipack] [nvarchar](50) NULL,[Displayer (Enum)] [nvarchar](50) NULL,[Displayer] [nvarchar](50) NULL,[Cross Shippable] [nvarchar](50) NULL,[Freezer Item] [nvarchar](50) NULL,[Novelty Flag] [nvarchar](50) NULL,[Normal/Special/Multipack] [nvarchar](50) NULL, [Component #1] [nvarchar](50) NULL,[Component #1Units] [int] NULL,[Component #2] [nvarchar](50) NULL, [Component #2Units] [int] NULL,[Component #3] [nvarchar](50) NULL,[Component #3Units] [int] NULL,[Component #4] [nvarchar](50) NULL,[Component #4Units] [int] NULL,[Component #5] [nvarchar](50) NULL,[Component #5Units] [int] NULL,[Component #6] [nvarchar](50) NULL,[Component #6Units] [int] NULL,[Component #7] [nvarchar](50) NULL,[Component #7Units] [int] NULL,[Component #8] [nvarchar](50) NULL,[Component #8Units] [int] NULL,[Component #9] [nvarchar](50) NULL,[Component #9Units] [int] NULL,[Component #10] [nvarchar](50) NULL,[Component #10Units] [int] NULL,[Component #11] [nvarchar](50) NULL,[Component #11Units] [int] NULL,[Component #12] [nvarchar](50) NULL, [Component #12Units] [int] NULL,[Component #13] [nvarchar](50) NULL,[Component #13Units] [int] NULL,[Component #14] [nvarchar](50) NULL,[Component #14Units] [int] NULL,[Component #15] [nvarchar](50) NULL,[Component #15Units] [int] NULL,[Component #16] [nvarchar](50) NULL,[Component #16Units] [int] NULL,[Component #17] [nvarchar](50) NULL,[Component #17Units] [int] NULL,[Component #18] [nvarchar](50) NULL,[Component #18Units] [int] NULL,[Component #19] [nvarchar](50) NULL,[Component #19Units] [int] NULL,[Component #20] [nvarchar](50) NULL,[Component #20Units] [int] NULL,[Unit Weight lbs] [int] NULL,[Unit Weight ozs] [int] NULL,[Unit Weight Grams][int] NULL,[Prepriced][nvarchar](50) NULL,[Country of Sale][nvarchar](50) NULL,[Egg] [nvarchar](50) NULL,[Pallet Spec  Wrapped] [nvarchar](50) NULL,[Pallet Spec  Non-Wrapped][nvarchar](50) NULL,[Stack Height][int] NULL,[Scan Unit UPC (SO/PO)][nvarchar](50) NULL,[Budget Category] [int] NULL,[Budget Category description] [nvarchar](50) NULL,[Base Item] [nvarchar](255) NULL, [registered Item] [int] NULL,[Material] [nvarchar](8) NULL,[Size] [nvarchar](12) NULL,[Standard] [nvarchar](8) NULL,[Sales Price (CAD)] [money] NULL,[Suggested Retail Price (CAD)] [money] NULL)";
 cmd.ExecuteNonQuery();
}
using (SqlTransaction tran = sqlConn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
 using (var sbc = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, tran))
{
  sbc.BatchSize = 10000;
  sbc.BulkCopyTimeout = 10000;
  sbc.DestinationTableName = "#ItemMaster";
 sbc.WriteToServer(dt);
  }
 Console.WriteLine(DateTime.Now.ToString());
  using (var cmd = new SqlCommand())
 {
 cmd.Connection = sqlConn;
  cmd.Transaction = tran;
  cmd.CommandText = "SELECT COUNT(*) AS n FROM #ItemMaster";
  Console.WriteLine("SqlBulkCopy complete. Temp table row count: {0}", cmd.ExecuteScalar());
   cmd.CommandText = "TRUNCATE TABLE [AccessMaster1].[dbo].[Item Master]";
   cmd.ExecuteNonQuery();
  Console.WriteLine("Truncated ItemMasterTable");
   cmd.CommandText = "INSERT INTO [AccessMaster1].[dbo].[Item Master]([Item],[Description],[Item Type],[Item Group] ,[Product Type],[Selection Code] ,[Standard Cost Price],[Inventory Unit] ,[Purchase Price],[Purchase Price Unit],[Latest Purchase Price] ,[Sales Price],[Sales Price Unit],[Phantom],[Scrap Factor],[Weight],[Signal Code],[Cost Price Component],[Last CP Trans Date] ,[Unit Set] ,[Inventory on Hand] ,[Purchase Unit],[Sales Unit],[Price Group] ,[Statistics Group],[Commission Group] ,[Rebate Group],[Sugg Ret Price] ,[Units per Carton],[Gross Weight],[Net Weight],[Freight Class],[Unit UPC],[Case UPC] ,[Retail/Misc only],[Additional Block],[Display Boxes],[Best Before] ,[Reportable],[Package (Enum)] ,[Package] ,[Style (Enum)],[Style],[Heart (Enum)],[Heart],[Basket (Enum)],[Basket],[Sugar Free (Enum)],[Sugar Free],[Flatback (Enum)],[Flatback],[Multipack (Enum)],[Multipack],[Displayer (Enum)],[Displayer],[Cross Shippable],[Freezer Item],[Novelty Flag] ,[Normal/Special/Multipack],[Component #1],[Component #1Units],[Component #2],[Component #2Units],[Component #3],[Component #3Units],[Component #4],[Component #4Units],[Component #5] ,[Component #5Units],[Component #6],[Component #6Units],[Component #7],[Component #7Units],[Component #8],[Component #8Units],[Component #9],[Component #9Units],[Component #10],[Component #10Units] ,[Component #11],[Component #11Units] ,[Component #12],[Component #12Units],[Component #13],[Component #13Units],[Component #14],[Component #14Units],[Component #15],[Component #15Units],[Component #16],[Component #16Units],[Component #17],[Component #17Units],[Component #18] ,[Component #18Units],[Component #19],[Component #19Units],[Component #20],[Component #20Units],[Unit Weight lbs],[Unit Weight ozs] ,[Unit Weight Grams],[Prepriced],[Country of Sale],[Egg] ,[Pallet Spec  Wrapped],[Pallet Spec  Non-Wrapped],[Stack Height],[Scan Unit UPC (SO/PO)],[Budget Category],[Budget Category description],[Base Item],[registered Item],[Material],[Size],[Standard],[Sales Price (CAD)],[Suggested Retail Price (CAD)]) SELECT Z.[Item],Z.[Description],Z.[Item Type],Z.[Item Group] ,Z.[Product Type],Z.[Selection Code] ,Z.[Standard Cost Price],Z.[Inventory Unit] ,Z.[Purchase Price],Z.[Purchase Price Unit],Z.[Latest Purchase Price] ,Z.[Sales Price],Z.[Sales Price Unit],Z.[Phantom],Z.[Scrap Factor],Z.[Weight],Z.[Signal Code],Z.[Cost Price Component],Z.[Last CP Trans Date] ,Z.[Unit Set] ,Z.[Inventory on Hand] ,Z.[Purchase Unit],Z.[Sales Unit],Z.[Price Group] ,Z.[Statistics Group],Z.[Commission Group] ,Z.[Rebate Group],Z.[Sugg Ret Price] ,Z.[Units per Carton],Z.[Gross Weight],Z.[Net Weight],Z.[Freight Class],Z.[Unit UPC],Z.[Case UPC] ,Z.[Retail/Misc only],Z.[Additional Block],Z.[Display Boxes],Z.[Best Before] ,Z.[Reportable],Z.[Package (Enum)] ,Z.[Package] ,Z.[Style (Enum)],Z.[Style],Z.[Heart (Enum)],Z.[Heart],Z.[Basket (Enum)],Z.[Basket],Z.[Sugar Free (Enum)],Z.[Sugar Free],Z.[Flatback (Enum)],Z.[Flatback],Z.[Multipack (Enum)],Z.[Multipack],Z.[Displayer (Enum)],Z.[Displayer],Z.[Cross Shippable],Z.[Freezer Item],Z.[Novelty Flag] ,Z.[Normal/Special/Multipack],Z.[Component #1],Z.[Component #1Units],Z.[Component #2],Z.[Component #2Units],Z.[Component #3],Z.[Component #3Units],Z.[Component #4],Z.[Component #4Units],Z.[Component #5] ,Z.[Component #5Units],Z.[Component #6],Z.[Component #6Units],Z.[Component #7],Z.[Component #7Units],Z.[Component #8],Z.[Component #8Units],Z.[Component #9],Z.[Component #9Units],Z.[Component #10],Z.[Component #10Units] ,Z.[Component #11],Z.[Component #11Units] ,Z.[Component #12],Z.[Component #12Units],Z.[Component #13],Z.[Component #13Units],Z.[Component #14],Z.[Component #14Units],Z.[Component #15],Z.[Component #15Units],Z.[Component #16],Z.[Component #16Units],Z.[Component #17],Z.[Component #17Units],Z.[Component #18] ,Z.[Component #18Units],Z.[Component #19],Z.[Component #19Units],Z.[Component #20],Z.[Component #20Units],Z.[Unit Weight lbs],Z.[Unit Weight ozs] ,Z.[Unit Weight Grams],Z.[Prepriced],Z.[Country of Sale],Z.[Egg] ,Z.[Pallet Spec  Wrapped],Z.[Pallet Spec  Non-Wrapped],Z.[Stack Height],Z.[Scan Unit UPC (SO/PO)],Z.[Budget Category],Z.[Budget Category description],Z.[Base Item],Z.[registered Item],Z.[Material],Z.[Size],Z.[Standard],Z.[Sales Price (CAD)],Z.[Suggested Retail Price (CAD)] FROM #ItemMaster Z";
Console.WriteLine(DateTime.Now.ToString());
 cmd.ExecuteNonQuery();
 cmd.CommandText = "SELECT COUNT(*) AS m FROM [AccessMaster1].[dbo].[Item Master]";
Console.WriteLine("Inserted Records into ItemMaster:{0}", cmd.ExecuteScalar());
  }
tran.Commit();
  }
 }
  }

program runs fine with no errors but not giving me the result i want

1 Answers1