MySqlConnection cn = conectar.fazer_conexao();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = cn;
try
{
    cn.Open();
    if (txt_codigo.Text == "")
    {
        cmd.CommandText = "Insert into operacoes (nome_cliente, codigo_cliente, data_locacao) VALUES ('"   cmb_cliente.Text   "','"   codigo_cliente.Text   "','"   Convert.ToDateTime(mask_dl.Text).ToString("yyyy/MM/dd")   "')";
    }

    cmd.CommandText = "SELECT LAST_INSERT_ID()";
    MySqlDataReader read = cmd.ExecuteReader();
    read.Read();

    int id = Convert.ToInt32(cmd.ExecuteScalar());

    for (int i = 0; i <= grid_jogos.RowCount - 1; i  )
    {
        if (Convert.ToBoolean(grid_jogos.Rows[i].Cells["select"].Value))
        {
            string jogos = grid_jogos.Rows[i].Cells[1].Value.ToString();
            cmd.CommandText = "Insert into itens (codigo_operacao, codigo_jogos) values ('"   id   "','"   jogos   "')";
        }
    }

    cmd.ExecuteNonQuery();
    cn.Close();

I've been getting this message when I try to make an insert and select on the same conection. But I don't see another DataReader open. Is there any way to do insert and select or is it just a silly mistake that I didn't notice?

CodePudding user response：

• Dispose everything what implements Disposable(the reader, the command, the connection). Best by using the using-statement.
• Never concatenate strings to add parameters to your sql command text, instead use sql-parameters, otherwise you are wide open for sql injection attacks.
• related: don't pass a string-format for your DateTimes, instead pass a DateTime parameter
• better don't re-use your sql-connection(like conectar.fazer_conexao() suggests), it doesn't hurt to always create the SqlConnection. It's the job of the sql-connection pooling.
• validate user-input before you consume it

So for example here you could use something like following:

if (!DateTime.TryParse(mask_dl.Text, out DateTime maskDl))
{
    // inform user
    return;
}

try
{
    using MySqlConnection cn = new MySqlConnection(GetConnectionString());
    using MySqlCommand cmd = new MySqlCommand(
        "Insert into operacoes (nome_cliente, codigo_cliente, data_locacao) VALUES (@nome_cliente, @codigo_cliente, @data_locacao)",
        cn);
    cmd.Parameters.AddWithValue("@nome_cliente", cmb_cliente.Text);
    cmd.Parameters.AddWithValue("@codigo_cliente", codigo_cliente.Text);
    cmd.Parameters.AddWithValue("@data_locacao", maskDl);
    cn.Open();
    int insertCount = cmd.ExecuteNonQuery();
    long newId = cmd.LastInsertedId; // no need for your reader
    
    // and so on...
}
catch (Exception e)
{
    // log it and do something
    throw;
}

The usings ensure that everything is disposed. The connection is closed even in case of an error.

CodePudding user response：

As Palle has stated in his comment you are vulnerable to SQL injection. Please read on how to fix this first! In addition, I would simply create two using statements each with a separate command object. It could look something like this:

    using(MySqlCommand cmd1 = new MySqlCommand("DO SOMETHING 1", cn))
    {

    }

    using(MySqlCommand cmd2 = new MySqlCommand("DO SOMETHING 2", cn))
    {
             
    }