Avatar of ShannonCallahan
ShannonCallahan

asked on 

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
C#SQL

Avatar of undefined
Last Comment
Norie
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

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
Avatar of ShannonCallahan
ShannonCallahan

ASKER

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:

User generated image
There is 15 lines of ComboBoxes, buttons, and Stop watches.

Please let me know if you need more information.
Shannon
ASKER CERTIFIED SOLUTION
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of ShannonCallahan
ShannonCallahan

ASKER

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
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

Are those the correct names of your Combo Boxes (case-sensitive)?
Avatar of ShannonCallahan
ShannonCallahan

ASKER

haha, thank you! I feel like a dummy.
Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

You are most welcome.
Avatar of Norie
Norie

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

Avatar of Sam Jacobs
Sam Jacobs
Flag of United States of America image

Yep ... that would be a more elegant solution.
Avatar of Norie
Norie

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.:)
C#
C#

C# is an object-oriented programming language created in conjunction with Microsoft’s .NET framework. Compilation is usually done into the Microsoft Intermediate Language (MSIL), which is then JIT-compiled to native code (and cached) during execution in the Common Language Runtime (CLR).

98K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo