What I want is to make a simple INSERT
in C#, but I currently get this error:
There is already an open DataReader associated with this Connection which must be closed first.
Code to insert:
string QueryCon = "SELECT Id_Categoria FROM Categoria where Nombre='" + Categoria + "'";
connection.Close();
connection.Open();
MySqlCommand ConnCombo = new MySqlCommand(QueryCon, connection);
MySqlDataReader reader = ConnCombo.ExecuteReader();
while (reader.Read())
{
Id_C = int.Parse(reader.GetString("Id_Categoria"));
}
string query = "INSERT INTO compra (Articulo, Categoria,Importancia,Obligatorio,Costo,Descripcion,Adquirir) VALUES('" + Articulo + "', '" + Id_C + "'," + Importancia + ",'no'," + Costo + ",'" + Descripcion + "','No','" + Mes + "')";
if (aprobar==true) {
//create command and assign the query and connection from the constructor
MySqlCommand cmd = new MySqlCommand(query, connection);
//Execute command
cmd.ExecuteNonQuery(); ------- Error
connection.Close();
}
The answer to your question is in the same error that you send. It says that since you already have an open reader you can't do anything else until you close it. If you wanted to process in parallel you should open another connection.
If not, just do
reader.Close()
once you're done using it to make it work.The error happens because you didn't close the
reader
before running yourinsert
. To avoid this type of problem, it is very important to get into the habit of using blocksusing
for this type of object that implements the interfaceIDisposable
:Simply executing
.Close()
is not enough, because there is still the possibility of leaving a data reader open if an exception is thrown. By using the blockusing
, you are guaranteed that the resource will be closed when you exit the block.Additionally, in the corrected code, I intentionally avoided including the code you have that handles the connection, because I don't agree with it. I don't know why there is a general infatuation with global connections, but that is extremely dangerous and can also cause the error you receive and others if you happen to move to a multithreaded environment. It's best to get a separate connection instance each time you need to communicate with the database, and close it immediately afterwards. Usually, ADO.NET providers take care of working with a pool of connections without you even realizing it so you're not really opening and closing connections every time and performance is good.
So the code should actually look something like this, if you want to avoid problems:
And finally, an additional note, because I didn't realize this before: it's very important to use parameters when constructing your queries. Never concatenate values directly in your SQL. Aside from SQL injection security issues, which I can understand you might not care as much about depending on the type of project you're using, it also avoids problems with unescaped quotes, helps the server to improve performance, and much more. It is very important to develop this good habit.