run multiple SqlCommand and ExecuteNonQuery

Question

I am trying to run multiples SqlCommand in the same connection, but for some reason the program will stop at the second

command.ExecuteNonQuery();

Here is my code :

  string queryString = "SELECT DISTINCT Titre from infosHoraire where Salle='DOO';" +
                              "SELECT DISTINCT Titre from infosHoraire where Salle='FOO' and Jour <='" + finDate + "';" +
                              "SELECT DISTINCT Titre from infosHoraire where Salle='GOO' and Jour <='" + finDate + "';";


        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConsoleXMLtoDB"].ConnectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

            try
            {

                // on remplit le dico
                while (reader.Read())
                {
                    MoviesList.Add(reader[0].ToString(), "0");
                    searchCode(reader[0].ToString(), MoviesList);
                    //Console.WriteLine(" On rajoute le code {0}", MoviesList[reader[0].ToString()]);
                }
                reader.NextResult();
                while (reader.Read())
                {
                    if (!MoviesList.ContainsKey(reader[0].ToString()))
                    {
                        MoviesList.Add(reader[0].ToString(), "0");
                        searchCode(reader[0].ToString(), MoviesList);
                    }

                }
                reader.NextResult();
                while (reader.Read())
                {
                    if (!MoviesList.ContainsKey(reader[0].ToString()))
                    {
                        MoviesList.Add(reader[0].ToString(), "0");
                    }
                }

                foreach (string key in MoviesList.Keys)
                {

                    Console.WriteLine("MoviesList {0}, code {1} .", key, MoviesList[key]);

                    // RAJOUTER DONNEES HORAIRES
                    command.CommandText = "INSERT INTO infosHoraire (Code) VALUES ('" + MoviesList[key] + "') where Titre = '" + key + "'";
                    //cmd.Parameters.AddWithValue("@code", MoviesList[key]);

                    IT STOPS HERE.
                    command.ExecuteNonQuery();


                }



            }
            catch (Exception)
            {
                //Console.WriteLine("{0} Exception caught.", e);
            }
            finally
            {
                // Always call Close when done reading.
                reader.Close();
                connection.Close();
            }
        }

MoviesList is a

  Dictionary<string, string>

I can't really find where the issue comes from.

It will work fine if I remove this :

  foreach (string key in MoviesList.Keys)
                {
                    //literaltest.Text += "<br/> dictionnaire " + key + "," + MoviesList[key];
                    Console.WriteLine("MoviesList {0}, code {1} .", key, MoviesList[key]);

                    // RAJOUTER DONNEES HORAIRES
                    command.CommandText = "INSERT INTO infosHoraire (Code) VALUES ('" + MoviesList[key] + "') where Titre = '" + key + "'";
                    //cmd.Parameters.AddWithValue("@code", MoviesList[key]);
                    command.ExecuteNonQuery();


                }

Nothing, it does not state that there is an error. But in the foreach only the first element of the Dictionary is shown in the console. — Exia0890, Jul 31 '13 at 03:42

TheProvost · Accepted Answer · 2013-07-31T05:06:05.797

2

Edit: Try to put a breakpoint on your catch and see if there is an error.

  catch (Exception e)
        {
            //Console.WriteLine("{0} Exception caught.", e);
        }

Hover your mouse on e and you should be able to see the error message. Then paste it here. Its probably your query causing the problem rather than the command object

Edit. The reader object is still running which is limiting you to use the command object again. Close the reader first before trying to execute the next command

reader.Close();

edited Jul 31 '13 at 05:06

answered Jul 31 '13 at 03:45

TheProvost

1,832
2
16
41

1

Thanks, it asked me to close the datareader executing another query, wich I did. The program now finish but seems like the INSERT did not work. – Exia0890 Jul 31 '13 at 03:54
Glad i could help. I would edit my answer so you can flag it as answer so we can close the question. – TheProvost Jul 31 '13 at 05:04
@TheProvost, on a side note, is it really necessary to use semi-colons to separate multiple sql statements in one executenonquery statement? – Rod Sep 22 '15 at 19:13
@Rod Nope. Semi-colons are not necessary – TheProvost Sep 29 '15 at 06:31

score 1 · Answer 2 · answered Jul 31 '13 at 03:54

1

remove where condition from your following insert query

command.CommandText = "INSERT INTO infosHoraire (Code) VALUES ('" + MoviesList[key] + "') where Titre = '" + key + "'";

answered Jul 31 '13 at 03:54

user2119324

587
8
24

Thanks for your answer, as below if do this it would insert the code to every row, is there another way to select them ? – Exia0890 Jul 31 '13 at 03:57
An insert statement with VALUES() inserts a single row. If you don't want to insert another row, don't call it. The WHERE clause does nothing. – dbugger Jul 31 '13 at 03:59
I see I will try another way. – Exia0890 Jul 31 '13 at 04:04
1

'command.CommandText = "UPDATE infosHoraire SET Code='" + MoviesList[key] + "' where Titre = '" + key + "'";' – user2119324 Jul 31 '13 at 04:04
Thanks for the proposition but the code value is still set at 'NULL' for every row – Exia0890 Jul 31 '13 at 04:06
Ok, sorry I said nothing, it worked. It is just that uncommented by accident something that I should not. Anyway Thanks. – Exia0890 Jul 31 '13 at 05:36

score 1 · Answer 3 · answered Jul 31 '13 at 04:06

The SQLDataReader you used to read records is still open and using that connection. You need to close that reader before executing another command on the same connection. e.g.

 reader.Close();
foreach (string key in MoviesList.Keys)
                {

                    Console.WriteLine("MoviesList {0}, code {1} .", key, MoviesList[key]);

                    // RAJOUTER DONNEES HORAIRES
                    command.CommandText = "INSERT INTO infosHoraire (Code) VALUES ('" + MoviesList[key] + "') where Titre = '" + key + "'";
                    //cmd.Parameters.AddWithValue("@code", MoviesList[key]);

                    IT STOPS HERE.
                    command.ExecuteNonQuery();


                }

Thanks for your answer.Yes, thanks to TheProvost, the message error pointed out that I had to close it. — Exia0890, Jul 31 '13 at 04:11

score 0 · Answer 4 · answered Jul 31 '13 at 03:48

0

Your SQL syntax for the INSERT statement is bad. You just need this

querystring= "INSERT INTO infosHoraire (Code) VALUES ('" + MoviesList[key] + "')";

answered Jul 31 '13 at 03:48

dbugger

15,868
9
31
33

Thanks for your answer, but if do this it would insert the code to every row, is there another way to select them ? I will try with SET – Exia0890 Jul 31 '13 at 03:57

score 0 · Answer 5 · answered Jul 31 '13 at 04:04

The SqlCommand (command) is still busy - you can't reuse it for the insert statement. You'd need to open a new SqlConnection with a new SqlCommand. Although it'd probably be cleaner if you just moved your foreach outside of the original queries using block, since you only referencing the in memory dictionary at that point.

As mentioned, your insert statement also has a where clause which doesn't make sense...did you perhaps mean that to be an update statement?

Drop the empty catch and you'll see some exception messages, which will make your life much easier.

Thanks for your answers, it is just like you said ^^ . – Exia0890 Jul 31 '13 at 04:12 — Exia0890, Jul 31 '13 at 04:12

score 0 · Answer 6 · answered Jul 31 '13 at 04:37

0

//edit your select statement try this

  SELECT DISTINCT Titre from infosHoraire where Salle in ('DOO','FOO','GOO') and Jour <='" + finDate + "'";

answered Jul 31 '13 at 04:37

odlan yer

721
1
7
15

Can you add some more explanation? – Cole Tobin Jul 31 '13 at 04:59
Thanks for your answer, it does works, but it doesn't really change the outcome. The INSERT/ or even UPDATE statement won't work. – Exia0890 Jul 31 '13 at 05:06
he's executing multiple sql command in the same connection, if you look the 3 select statement it can combine into one select statement – odlan yer Jul 31 '13 at 05:07
@Exia0890, did you try to remove your where condition on your INSERT statement? – odlan yer Jul 31 '13 at 05:10
I did like @user2119324 said, but it did not work, although it does work when I do it manually, I will probably open another topic for this issue. – Exia0890 Jul 31 '13 at 05:19

run multiple SqlCommand and ExecuteNonQuery

6 Answers6