Using MySqlDataReader C#

Question

I'm trying to use mysqldatareader to fill some textboxes in C# Visual Studio. I've made the connection string and created the MySqlDataReader command.

But when I click in the button to make the action it shows me a messagebox saying: "Invalid attempt to access a field before calling Read()"

This is my code:

 private void btnBuscar_Click(object sender, EventArgs e)
        {
            try
            {
                MySqlConnection conexao = new MySqlConnection("server=localip; database=localdb; Uid=user; pwd=pass;");
                conexao.Open();
                MySqlCommand comando = new MySqlCommand();

                comando.CommandText = "select p.id, s.sku_id, p.commercial_description, pri.price, max(pri.start) as alterado_em from plu p " +
                        "inner join sku s on p.plu_key = s.plu_key inner join pricing pri on pri.plu_key = p.plu_key " +
                        "where p.id = " + txtCodbusca.Text + " group by pri.plu_key desc";
                comando.CommandType = CommandType.Text;
                comando.Connection = conexao;

                MySqlDataReader DR;
                DR = comando.ExecuteReader();
                DR.Read();
                txtCodinterno.Text = Convert.ToString(DR.GetDecimal(0));
                txtGtin.Text = Convert.ToString(DR.GetChar(1));
                txtDescricao.Text = (DR.GetString(2));
                txtPreco.Text = Convert.ToString(DR.GetDecimal(3));

                conexao.Close();
                HabBotoes();
            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("{0}", ex.Message));
            }                     
        }

Answer 1

Based on my research, your problem is due to empty query results.

I suggest that you can judge the query results before filling the textbox.

You could try the following code:

     DR = comando.ExecuteReader();
            if (DR.HasRows)
            {
                DR.Read();
                txtCodinterno.Text = Convert.ToString(DR.GetDecimal(0));
                txtGtin.Text = Convert.ToString(DR.GetChar(1));
                txtDescricao.Text = (DR.GetString(2));
                txtPreco.Text = Convert.ToString(DR.GetDecimal(3));
            }
           else 
            {
                MessageBox.Show("The query result of p.id=" + txtCodbusca.Text + " is empty");
            }
     conexao.Close();

Answer 2

I see many problems in your code:

1. It is in spanish - which should be avoided, especially if you need help from not spanish people
2. You never dispose IDisposable objects (ex. MySqlConnection)
3. You have a useless try catch in a try catch and in the internal catch you aren't handling the exception
4. Your query is appending parameters as strings instead of passing parameters as sqlparameters which makes your code vulnerable to sql injection
5. You are doing Convert.ToString of a string
6. In your query a space is missing before group by which will break the syntax