C# - add multiple values to a parameter in MySQL

Question

So I want to store multiple values to one parameter. Something like adding a row to a column. I've been trying this with the following: storeData.Parameter.AddWithValue()

This sadly doesn't work, because it says that subjects, which I'm using in the following code, is already difined. I'm not that experienced with MySQL yet so I don't really know another way to do this. Here is my code:

        string conString = @"Data Source=MYIP; Port=3306; Initial Catalog=MYCATALOG; User ID=USERNAME; Password=PASS;Integrated Security=true;";
        MySqlConnection SalesKicker;
        SalesKicker = new MySqlConnection(conString);

        try
        {
            SalesKicker.Open();

            MySqlCommand tabCreator = new MySqlCommand("CREATE TABLE " + dt.bedrijfsNaam + " (subjects TEXT , jrLijks INT , kwrt INT , mnd INT , wek INT , dag INT)", SalesKicker);
            tabCreator.ExecuteNonQuery();
            MySqlCommand storeData = new MySqlCommand("INSERT INTO " + dt.bedrijfsNaam+ " (subjects, jrLijks, kwrt, mnd, wek, dag)" + "VALUES (@subjects, @jrLijks, @kwrt, @mnd, @wek, @dag)", SalesKicker);

            string[] subjects = new string[] { "Prospects", "Hot Prospect", "Afspraken Maken", "Afspraken", "Offertes Maken", "Gescoorde Offertes", "Nieuwe Klanten" };
            for (int i = 0; i < subjects.Length; i++) {
                storeData.Parameters.AddWithValue("@subjects", subjects[i]);
                if(i == 0)
                {
                    storeData.Parameters.AddWithValue("@jrLijks", dt.skProspects * 12);
                    storeData.Parameters.AddWithValue("@kwrt", dt.skProspects * 3);
                    storeData.Parameters.AddWithValue("@mnd", dt.skProspects);
                    storeData.Parameters.AddWithValue("@wek", (dt.skProspects * 12) / 52);
                    storeData.Parameters.AddWithValue("@dag", (dt.skProspects * 12) / 365);
                }
                //these if statements are used for every subject
                storeData.ExecuteNonQuery();
                SalesKicker.Close();
        }

The code stops after it adds "Prospects". What am I doing wrong here? Can someone help me with this?

this is the error I`m getting:

{"Parameter 'subjects' has already been defined."}

ow, and dt.bedrijfsNaam gets its value through the dt class

Thanks in advance

Why are you creating table in C# code? Ideally Table are created by SQL script as it is one time affair....are you going to drop table at end of C#code execution? — Viru
– Viru, Commented Feb 2, 2016 at 14:22
SalesKicker.Close(); on the first trip round closes the connection & should throw next time round ... misplaced braces? — Alex K.
– Alex K., Commented Feb 2, 2016 at 14:22
I have to create a new table for each company named after the company name (bedrijfsNaam) — user5730942
– user5730942, Commented Feb 2, 2016 at 14:23

Mathijs · Accepted Answer · 2016-02-02 14:29:01Z

3

If you want to reuse parameters, you should call

command.Parameters.Clear();

before executing the command.

In your case it would be good to add this line right after the opening brackets of your for loop.

answered Feb 2, 2016 at 14:29

Mathijs

3832 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex K. Over a year ago

or just update the non-static value within the loop; storeData.Parameters["@subjects"].Value = subjects[i];

Viru · Accepted Answer · 2016-02-02 14:52:39Z

Check below code

    string conString = @"Data Source=MYIP; Port=3306; Initial Catalog=MYCATALOG; User ID=USERNAME; Password=PASS;Integrated Security=true;";
            MySqlConnection SalesKicker;
            SalesKicker = new MySqlConnection(conString);

            try
            {
                SalesKicker.Open();

                MySqlCommand tabCreator = new MySqlCommand("CREATE TABLE " + dt.bedrijfsNaam + " (subjects TEXT , jrLijks INT , kwrt INT , mnd INT , wek INT , dag INT)", SalesKicker);
                tabCreator.ExecuteNonQuery();
                MySqlCommand storeData = new MySqlCommand("INSERT INTO " + dt.bedrijfsNaam+ " (subjects, jrLijks, kwrt, mnd, wek, dag)" + "VALUES (@subjects, @jrLijks, @kwrt, @mnd, @wek, @dag)", SalesKicker);

                string[] subjects = new string[] { "Prospects", "Hot Prospect", "Afspraken Maken", "Afspraken", "Offertes Maken", "Gescoorde Offertes", "Nieuwe Klanten" };
storeData.Parameters.Add("@subjects");
storeData.Parameters.AddWithValue("@jrLijks", dt.skProspects * 12);
                    storeData.Parameters.AddWithValue("@kwrt", dt.skProspects * 3);
                    storeData.Parameters.AddWithValue("@mnd", dt.skProspects);
                    storeData.Parameters.AddWithValue("@wek", (dt.skProspects * 12) / 52);
                    storeData.Parameters.AddWithValue("@dag", (dt.skProspects * 12) / 365);

                for (int i = 0; i < subjects.Length; i++) {
                    storeData.Parameters["@subjects"].Value=subjects[i];

                    //these if statements are used for every subject
                    storeData.ExecuteNonQuery();

            }
    SalesKicker.Close();

Collectives™ on Stack Overflow

C# - add multiple values to a parameter in MySQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related