Multiple variables in for each loop.

OK. I am working on getting my SQL Insert command working correctly. Currently I have:

private void testButton_Click(object sender, EventArgs e)
        {
            
            try
            {
                //Create OleDB connection
                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=I:\PSC SHARED\accessdb.accdb;
Persist Security Info=False;";
                
                //connect to DB
                connection.Open();

                //Setup 
                var ComboBoxList = Controls.OfType<ComboBox>();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;

                //Insert data into table through loop
                foreach(ComboBox taskComboBox in ComboBoxList)
                {
                    string query = "INSERT INTO MasterTable(Task, TimeWorked, RefDate, Analyst, HoursWorked) values('" + taskComboBox.Text + "', '" + stopwatch1.Elapsed.ToString("hh\\:mm\\:ss") + "', '" + ReferenceDate + "', '" + AnalystName + "', '" + (stopwatch1.Elapsed.TotalMinutes / 60d).ToString("N2") + "')";
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }
                
                //close connection to db
                connection.Close();
            }
            //handle errors
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex);
            }
        }

Open in new window


To my surprise, I almost got this working completely. The "foreach" works, executing the command for each combobox on the form. All work except for the 2, TimeWorked and HoursWorked. It repeats the values of stopwatch1. This makes sense because I specified which stop watch to reference.

What I need to know:
How to do get the stop watch information to "marry-up" with the appropriate ComboBox in the loop?

Please let me know if you need additional information and thank you for your time!!!

Shannon
LVL 2
ShannonCallahanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sam JacobsDirector of Technology Development, IPMCommented:
Shannon,

I don't quite understand the setup of your form.
How many comboboxes do you have?
Can you set up a separate stopwatch for each combobox?

Sam
ShannonCallahanAuthor Commented:
I have 15 stopwatches declared:

public partial class Form1 : Form
    {
        //Declare stopwatches at Form/Class level.
        Stopwatch stopwatch1 = new Stopwatch();
        Stopwatch stopwatch2 = new Stopwatch();
        Stopwatch stopwatch3 = new Stopwatch();
        Stopwatch stopwatch4 = new Stopwatch();
        Stopwatch stopwatch5 = new Stopwatch();
        Stopwatch stopwatch6 = new Stopwatch();
        Stopwatch stopwatch7 = new Stopwatch();
        Stopwatch stopwatch8 = new Stopwatch();
        Stopwatch stopwatch9 = new Stopwatch();
        Stopwatch stopwatch10 = new Stopwatch();
        Stopwatch stopwatch11 = new Stopwatch();
        Stopwatch stopwatch12 = new Stopwatch();
        Stopwatch stopwatch13 = new Stopwatch();
        Stopwatch stopwatch14 = new Stopwatch();
        Stopwatch stopwatch15 = new Stopwatch();

Open in new window


Each stop watch is linked to the text of a Textbox:

private void timer1_Tick(object sender, EventArgs e)
        {
            //Set Textbox text equal to elapsed time 
            TimerTextBox1.Text = stopwatch1.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox2.Text = stopwatch2.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox3.Text = stopwatch3.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox4.Text = stopwatch4.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox5.Text = stopwatch5.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox6.Text = stopwatch6.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox7.Text = stopwatch7.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox8.Text = stopwatch8.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox9.Text = stopwatch9.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox10.Text = stopwatch10.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox11.Text = stopwatch11.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox12.Text = stopwatch12.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox13.Text = stopwatch13.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox14.Text = stopwatch14.Elapsed.ToString("hh\\:mm\\:ss");
            TimerTextBox15.Text = stopwatch15.Elapsed.ToString("hh\\:mm\\:ss");
        }

Open in new window


This appears as:

Picture of GUI
There is 15 lines of ComboBoxes, buttons, and Stop watches.

Please let me know if you need more information.
Shannon
Sam JacobsDirector of Technology Development, IPMCommented:
I'm assuming that your ComboBoxes are named taskComboBoxn (n=1 through 15) ..

Probably the easiest thing to do is to declare another form-level stopwatch:
Stopwatch stopwatch = new Stopwatch();

Open in new window


And then modify your for loop with a switch statement:
                foreach(ComboBox taskComboBox in ComboBoxList)
                {

                    switch (taskComboBox.Name)
                    {
                        case "taskComboBox1":
        		    stopwatch = stopwatch1;
                            break;
                        case "taskComboBox2":
        		    stopwatch = stopwatch2;
                            break;

         		etc ...

                        default:
                            break;
                    }
                    string query = "INSERT INTO MasterTable(Task, TimeWorked, RefDate, Analyst, HoursWorked) values('" + taskComboBox.Text + "', '" + stopwatch.Elapsed.ToString("hh\\:mm\\:ss") + "', '" + ReferenceDate + "', '" + AnalystName + "', '" + (stopwatch.Elapsed.TotalMinutes / 60d).ToString("N2") + "')";
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

ShannonCallahanAuthor Commented:
OK. I tried:

Stopwatch stopwatch = new Stopwatch();

private void testButton_Click(object sender, EventArgs e)
        {

            try
            {
                //Create OleDB connection
                OleDbConnection connection = new OleDbConnection();
                connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=I:\PSC SHARED\accessdb.accdb;
Persist Security Info=False;";

                //connect to DB
                connection.Open();

                //Setup 
                var ComboBoxList = Controls.OfType<ComboBox>();
                var StopwatchList = Controls.OfType<Stopwatch>();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;

                //Insert data into table through loop
                foreach (ComboBox taskComboBox in ComboBoxList)
                {
                    switch (taskComboBox.Name)
                    {
                        case "taskComboBox1":
                            stopwatch = stopwatch1;
                            break;
                        case "taskComboBox2":
                            stopwatch = stopwatch2;
                            break;
                        case "taskComboBox3":
                            stopwatch = stopwatch3;
                            break;
                        case "taskComboBox4":
                            stopwatch = stopwatch4;
                            break;
                        case "taskComboBox5":
                            stopwatch = stopwatch5;
                            break;
                        case "taskComboBox6":
                            stopwatch = stopwatch6;
                            break;
                        case "taskComboBox7":
                            stopwatch = stopwatch7;
                            break;
                        case "taskComboBox8":
                            stopwatch = stopwatch8;
                            break;
                        case "taskComboBox9":
                            stopwatch = stopwatch9;
                            break;
                        case "taskComboBox10":
                            stopwatch = stopwatch10;
                            break;
                        case "taskComboBox11":
                            stopwatch = stopwatch11;
                            break;
                        case "taskComboBox12":
                            stopwatch = stopwatch12;
                            break;
                        case "taskComboBox13":
                            stopwatch = stopwatch13;
                            break;
                        case "taskComboBox14":
                            stopwatch = stopwatch14;
                            break;
                        case "taskComboBox15":
                            stopwatch = stopwatch15;
                            break;
                        default:
                            break;
                    }

                    string query = "INSERT INTO MasterTable(Task, TimeWorked, RefDate, Analyst, HoursWorked) values('" + taskComboBox.Text + "', '" + stopwatch.Elapsed.ToString("hh\\:mm\\:ss") + "', '" + ReferenceDate + "', '" + AnalystName + "', '" + (stopwatch.Elapsed.TotalMinutes / 60d).ToString("N2") + "')";
                    command.CommandText = query;
                    command.ExecuteNonQuery();
                }

                //close connection to db
                connection.Close();
            }
            //handle errors
            catch (Exception ex)
            {
                MessageBox.Show("Error: " + ex);
            }
}

Open in new window


But no values are being recorded for the Stopwatch.

Thank you,
Shannon
Sam JacobsDirector of Technology Development, IPMCommented:
Are those the correct names of your Combo Boxes (case-sensitive)?
ShannonCallahanAuthor Commented:
haha, thank you! I feel like a dummy.
Sam JacobsDirector of Technology Development, IPMCommented:
You are most welcome.
NorieAnalyst Assistant Commented:
Couldn't you use the Controls collection for this?
string comboname = taskComboBox.Name;

string stopwatchname = comboname.Replace("taskComboBox", "stopwatch");

stopwatch = this.Controls[stopwatchname];

Open in new window

Sam JacobsDirector of Technology Development, IPMCommented:
Yep ... that would be a more elegant solution.
NorieAnalyst Assistant Commented:
Sam

What I posted is reliant on the naming of the combonoxes/stopwatches following a regular pattern.

Looks like that's the case here but you never know.:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
foreach

From novice to tech pro — start learning today.