Click here to Skip to main content
15,667,847 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
hey guys pls I need your help, the code below works at about 95%, the other 5% is the problem. when I run the program,the form allows me to select the file I want to read and then when I click the populate button(button2_Click_1), it goes down hill from there. The cancel button does not respond, the label is not visible and doesn't update and the same goes for the progress bar. I have used Application.DoEvent, but I still can't seem to get it to work.

namespace DellOrderUP
    public partial class Form1 : Form
        public Form1()

        // some public variables
        String Filename = "";

        // this is the browse button
        private void button1_Click(object sender, EventArgs e)
            //Stream myStream = null;
            OpenFileDialog openFileDialog1 = new OpenFileDialog();

            openFileDialog1.InitialDirectory = "c:\\ ";  // *** Edit: Space added 

            openFileDialog1.Filter = "txt files (*.xls)|*.xls|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 2;
            openFileDialog1.RestoreDirectory = true;

            if (openFileDialog1.ShowDialog() == DialogResult.OK)
                // show the user the file they selected.
                // they can only change it through the browse button.
                textBox1.Text = openFileDialog1.FileName;

                // pass the filename to public variable filename
                Filename = openFileDialog1.FileName;

                //check extension
                String Extension = openFileDialog1.FileName;

                String Check = Path.GetExtension(Extension);
                if (Check == ".xls" || Check == ".xlsx" || Check == ".xlsm")
                    // enable procced button
                    button2.Enabled = true;
                    String Error = "Sorry Only Excel Files!!!!";
                    String caption = "Excel Only";
                    MessageBox.Show(Error, caption);
                    button2.Enabled = false;

        //private void textBox1_TextChanged(object sender, EventArgs e)
          private void textBox1_TextChanged(object sender, EventArgs e)

        // procceed
        private void button2_Click_1(object sender, EventArgs e)
            // pass the file path to OpenExcelFile

            // make lable1 visbible and show state
            label1.Visible = true;

            // progress bar becomes visible
            progressBar1.Visible = true;

        // close the program
        private void button3_Click_1(object sender, EventArgs e)
            string message = "Do you want to close this program ?";
            string caption = "InventoryUploader";
            MessageBoxButtons button = MessageBoxButtons.YesNoCancel;

            DialogResult result;

            result = MessageBox.Show(this, message, caption, button);

            if (result == DialogResult.Yes)
                // Closes the parent form. 


        private void label1_Click(object sender, EventArgs e)
        private void progressBar1_Click(object sender, EventArgs e)

        // opens the file and reads the coloumns of data in
        private void OpenExcelFile(String filepath)

            // Enable button 3(cancel button) and disable button2(procced button)
            button3.Enabled = true;
            button2.Enabled = false;

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filepath);

            Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];

            Excel.Range xlRange = xlWorksheet.UsedRange.Columns[37];

            // update status for the user to see
            UpdateLabel("Please Wait, reading data");

            System.Array myvalues = (System.Array)xlRange.Cells.Value;
            string[] strArray = myvalues.OfType<object>().Select(o => o.ToString()).ToArray();

            // get the size of strArray
            int Size_Of_strArray = strArray.Length;

            // create a new array and initilize it with the size of the previous array
            String[] FirstClean = new String[Size_Of_strArray];
            String[] SecondClean = new String[Size_Of_strArray];

            // update status for the user to see
            UpdateLabel("Sanitizing data");

            // in this loop, the word (exec InsertDellInventory) will be removed from each entry
            for (int i = 1; i < Size_Of_strArray - 1; i++)
                FirstClean[i] = strArray[i].Remove(0, 24);

                // now remove '' and pass the data to second clean array
                SecondClean[i] = FirstClean[i].Replace("'", "");

            // update progress for the user to see

            // pass the array to insert function
            BeforeInsertingIntoDB(ref SecondClean);


        // update the progress bar
        private void UpdateProgress( int i) 
            progressBar1.Value = i;
        }// update the progress

        // update the label
        private void UpdateLabel( String status) 
            label1.Text = status
        }// update the progress

        private void BeforeInsertingIntoDB(ref string[] data)
            //update status for the user to see
            UpdateLabel("Preparing data for insertion");

            // create and initialize all the variables
            String PoNum = ""; String OrderNum = "";

            // get the size of the passed array
            int Size_Of_Array = data.Length;

            for (int i = 1; i < Size_Of_Array - 1; i++)
                String Distribution = data[i];
                counter = i; // assign the value of i to counter

                String[] DistributionArray = Distribution.Split(',');

                PoNum = DistributionArray[0]; OrderNum = DistributionArray[1]; 
                 // call the InserDataintoDB functions
                 InsertDataIntoDB(PoNum, OrderNum)


            // call the update progress function

        }// End BeforeInsertingintoDB

        // insertIntoDb
        private void InsertDataIntoDB(String PoNum, String OrderNum)


            ////update status for the user to see
            UpdateLabel("Opening Db connection");
             // call the update progress function

            String InventoryUploader = ConfigurationManager.ConnectionStrings ["DellOrderUP.Properties.Settings.InventoryUploader"].ConnectionString;
            SqlConnection con = new SqlConnection(InventoryUploader);


                //update status for the user to see
                label1.Text = "Populating Database";

                // call the update progress

                SqlCommand cmd = new SqlCommand("UploadDellOrder", con);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@ProdNum", SqlDbType.Int));
                cmd.Parameters["@ProdNum"].Value = PoNum;

                cmd.Parameters.Add(new SqlParameter("@OrderNum", SqlDbType.Int));
                cmd.Parameters["@OrderNum"].Value = OrderNum;


            catch (Exception e)
                String caption_error_num = Convert.ToString(counter);
                String error = e.ToString();


Thanks for your help.

[edit] Space added in the line openFileDialog1.InitialDirectory = "c:\\"; to avoid scaped quotes and the mess in the formatting colous[/edit]
Updated 2-Dec-13 7:35am
rudolph098 2-Dec-13 13:42pm    
thanks you, it was driving me nuts.
BillWoodruff 2-Dec-13 21:21pm    
The first step in dealing with a problem like this is to use the Debugger. Set break-points, try and intercept the problem during a trial where you experience the long delay. Then post details of what you find.

1 solution

Within button2_Click_1 you call OpenExcelFile method.

There are couple things that can go wrong there.

First, OpenExcelFile might take some time. Even several seconds (Excel Automation is very, very slow). If you want your application to be responsive all the time, you should delegate such time consuming tasks to the separate (non-GUI) thread.

Second, OpenExcelFile can produce number of errors (file aready open, file doesn't exist etc.). You should provide some error handling there to properly handle such cases.
Share this answer
rudolph098 2-Dec-13 13:50pm    
The purpose of the GUI is to aid the user select the given file they want and the file read-in is surprisingly very fast.
The file I am using has been edited thoroughly and only has a small data set. Also, I don't want to add code for error handling until I fix this problem.
Adam Zgagacz 2-Dec-13 14:10pm    
Well, I'm not so sure about speed. If Excel is not already running, your method will need to launch it in the background what might add significant delay. Reason your GUI is not responding to your interactions is due to the time button2_Click_1 takes to complete. You can try to time how long it take to perform button2_Click_1 (you can use Stopwatch for this).

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900