CODEWITHSUNDEEP
c#mysql
Soundflow
Soundflow

Reputation: 59

c# Mysql There is already an open DataReader associated with this Connection

Hello guys I am trying to do some stuff while reading. What I am trying to do is edit row which was just read. But I get error. Maybe u have some suggestions how should I fix it, to make it work without quitting the data reader. P.S: Ignore that, that queries are open for SQL injections .

string select = "Select * FROM ivykiai WHERE `Ivikio diena` MOD Periodiskumas_d = 0 AND `Ivikio diena` > 0 AND `Ivikio diena` < `Dif dien`";
MySqlCommand command = new MySqlCommand(select, cnn);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    db1 = (now - Convert.ToDateTime(reader["Nuo"])).TotalDays;
    MessageBox.Show(db1.ToString());
    db1 = db1 - Convert.ToInt32(reader["Ivikio diena"]);
    MessageBox.Show(db1.ToString());
    b = Convert.ToInt32(db1) / Convert.ToInt32(reader["Periodiskumas_d"]);
    MessageBox.Show(b.ToString());
    a =+ Convert.ToInt32(reader["Suma"]);
    MessageBox.Show(a.ToString());
    a = a * b;
    MessageBox.Show(a.ToString());
    string prideti = "Update Lesos Set Grynieji=Grynieji + '"+ a +"'";
    MySqlCommand prideti_cmd = new MySqlCommand(prideti, cnn);
    string p = prideti_cmd.ExecuteNonQuery().ToString();
    string update = "UPDATE Ivikiai Set `Ivykio diena`+= '" + db1 + "'";
    MySqlCommand update_cmd = new MySqlCommand(update, cnn);
    string u = update_cmd.ExecuteNonQuery().ToString();
}
reader.Close();
cnn.Close();

Upvotes: 2

Views: 2207

Answers (3)

Ntellect13
Ntellect13

Reputation: 331

Try this:

        using (MySqlConnection cnn = new MySqlConnection(dbConnectionString))
        {
            cnn.Open();
            MySqlCommand command = new MySqlCommand(select, cnn);
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                db1 = (now - Convert.ToDateTime(reader["Nuo"])).TotalDays;
                MessageBox.Show(db1.ToString());
                db1 = db1 - Convert.ToInt32(reader["Ivikio diena"]);
                MessageBox.Show(db1.ToString());
                b = Convert.ToInt32(db1) / Convert.ToInt32(reader["Periodiskumas_d"]);
                MessageBox.Show(b.ToString());
                a = +Convert.ToInt32(reader["Suma"]);
                MessageBox.Show(a.ToString());
                a = a * b;
                MessageBox.Show(a.ToString());
            }

            string prideti = "Update Lesos Set Grynieji=Grynieji + '" + a + "'";
            MySqlCommand prideti_cmd = new MySqlCommand(prideti, cnn);
            string p = prideti_cmd.ExecuteNonQuery().ToString();
            string update = "UPDATE Ivikiai Set `Ivykio diena`+= '" + db1 + "'";
            MySqlCommand update_cmd = new MySqlCommand(update, cnn);
            string u = update_cmd.ExecuteNonQuery().ToString();
        }

All of the variables needed for the ExecuteNonQuery() are set when the data is read so you can use them outside the MySqlDataReader.ExecuteReader() function.

Upvotes: 0

ekad
ekad

Reputation: 14614

You can't execute prideti_cmd and update_cmd using the same connection inside the while (reader.Read()) block and reader is still open, however you can do that outside the while (reader.Read()) block and after closing reader. I would suggest creating the following class

public class MyClass
{
    public DateTime Nuo { get; set; }
    public int IvikioDiena { get; set; }
    public int Periodiskumas_d { get; set; }
    public int Suma { get; set; }
}

and change your code as below

string select = "Select * FROM ivykiai WHERE `Ivikio diena` MOD Periodiskumas_d = 0 AND `Ivikio diena` > 0 AND `Ivikio diena` < `Dif dien`";
using (MySqlCommand command = new MySqlCommand(select, cnn))
{
    // execute the select query and store the results to list variable
    List<MyClass> list = new List<MyClass>();
    using (MySqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            MyClass record = new MyClass();
            record.Nuo = Convert.ToDateTime(reader["Nuo"]);
            record.IvikioDiena = Convert.ToInt32(reader["Ivikio diena"]);
            record.Periodiskumas_d = Convert.ToInt32(reader["Periodiskumas_d"]);
            record.Suma = Convert.ToInt32(reader["Suma"]);
            list.Add(record);
        }
    }

    // enumerate list and execute both the update queries
    foreach (var record in list)
    {
        db1 = (now - record.Nuo).TotalDays;
        MessageBox.Show(db1.ToString());
        db1 = db1 - record.IvikioDiena;
        MessageBox.Show(db1.ToString());
        b = Convert.ToInt32(db1) / record.Periodiskumas_d;
        MessageBox.Show(b.ToString());
        a =+ record.Suma;
        MessageBox.Show(a.ToString());
        a = a * b;
        MessageBox.Show(a.ToString());
        string prideti = "Update Lesos Set Grynieji=Grynieji + '"+ a +"'";
        MySqlCommand prideti_cmd = new MySqlCommand(prideti, cnn);
        string p = prideti_cmd.ExecuteNonQuery().ToString();
        string update = "UPDATE Ivikiai Set `Ivykio diena`+= '" + db1 + "'";
        MySqlCommand update_cmd = new MySqlCommand(update, cnn);
        string u = update_cmd.ExecuteNonQuery().ToString();
    }
}

Upvotes: 2

TomTom
TomTom

Reputation: 62101

Generally you can ever only have one active command - SQL Server MARS being a little exceptiohn.

So, you can not use a connection WHILE IT HAS AN OPEN READER. Your first need to finish reading, then can update- or use anothe connection, which will get you into transaction isolation troubles.

Upvotes: 1

Related Questions