Writing array values into SQL database

Question

Looking for some help for writing array values into one of my tables within my sql database. The array has already calculated all the values, and I want to insert these values into my database. However when I run the code I'm getting an error message stating "An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: Parameter '@PE' has already been defined."

It says the same for each parameter if I remove the one before it.

I'm not quite sure whats going on as I've used this code in another form for writing stuff into a database and that works fine! Any help would be great.

My code is,

try

        {
            string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection myconn = new MySqlConnection(myconnection);
            MySqlDataAdapter MyDataAdaptor = new MySqlDataAdapter();
            MyDataAdaptor.SelectCommand = new MySqlCommand("select * lossdatabase.forecasttable;", myconn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(MyDataAdaptor);
            myconn.Open();


        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }


        string constring = "datasource=localhost;port=3306;username=root;password=1234; ";
        string query = " insert into lossdatabase.forecasttable (PE, Production_Time, Potential) VALUES(@PE, @Production_Time, @Potential;";



        MySqlConnection conLossDB = new MySqlConnection(constring);
        MySqlCommand cmdLossDB = new MySqlCommand(query, conLossDB);


        for (int i=0; i<366; i++)
        {
            cmdLossDB.Parameters.AddWithValue("@PE", textBox2.Text);
            cmdLossDB.Parameters.AddWithValue("@Production_Time", forecast[i,2]);
            cmdLossDB.Parameters.AddWithValue("@Potential", forecast[i,3]);


        }




        MySqlDataReader myReader;
        try
        {
            conLossDB.Open();
            myReader = cmdLossDB.ExecuteReader();
            MessageBox.Show("Forecast Results Saved");


            while (myReader.Read())
            {

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

`select * FROM lossdatabase.forecasttable;` You forgot your FROM there. — Jens, Oct 11 '16 at 11:15
You need to move your "MySqlCommand cmdLossDB = ..." line into the loop, and also execute your query in the loop. — David_001, Oct 11 '16 at 11:16
Create a mysql prepared statement and add batch like here: http://stackoverflow.com/questions/4355046/java-insert-multiple-rows-into-mysql-with-preparedstatement — Dexion, Oct 11 '16 at 11:19

Ricardo Pontual · Answer 1 · 2016-10-11T13:59:12.197

0

If understood your code, you're adding a new parameter for every element in your array. You should add the values to a variable in the for loop, and add the parameter once, after the for loop ends something like this:

MySqlDataReader myReader;
try
{
    conLossDB.Open();
    for (int i = 0; i < 366; i++)
    {
        MySqlCommand cmdLossDB = new MySqlCommand(query, conLossDB);
        cmdLossDB.Parameters.AddWithValue("@PE", textBox2.Text);
        cmdLossDB.Parameters.AddWithValue("@Production_Time", forecast[i, 2]);
        cmdLossDB.Parameters.AddWithValue("@Potential", forecast[i, 3]);
        cmdLossDB.ExecuteNonQuery();
    }

    MessageBox.Show("Forecast Results Saved");

}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

I assumed forecast is an array of strings, you may need to cast for different types. You must also fix the errors pointed by @Jens and @David_001.

edited Oct 11 '16 at 13:59

answered Oct 11 '16 at 11:32

Ricardo Pontual

3,749
3
28
43

I've modified my code based on your suggestion above, as well as the the two comments from Jens and David_001. I'm not getting a fatal error encountered during command execution. Which must be coming from the last part of my code with the MySqlDataReader but again, not sure how to tackle that? @Ricardo Pontual – Loplac92 Oct 11 '16 at 11:54
I've updated my code, check out. I replaced `ExecuteReader` by `ExecuteNonQuery` since it's an insert command. Now it's up to you – Ricardo Pontual Oct 11 '16 at 14:02

Writing array values into SQL database

1 Answers1