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));
            }                     
        }

Also read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection as a matter of urgency. — mjwills, Aug 11 '20 at 12:13
Hint - you are missing a space before `group by`. And before at least two other places. Look at `comando.CommandText` in the `Watch Window`. — mjwills, Aug 11 '20 at 12:13
`catch (Exception) { }` is not helping you track down this issue. At least print the exception to the console. — 001, Aug 11 '20 at 12:17
I've now printed the exception in a messagebox and it said: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'p.id = txtCodbusca.text' — lf.andreani, Aug 11 '20 at 12:38
Yep - see my earlier comment. Hint - add more spaces. And look at `comando.CommandText` in the `Watch Window`. — mjwills, Aug 11 '20 at 12:43
Hi. I know you are new. Please internet search "c# layers" and "dotnet layers" to find articles like this one (URL later in this comment). Seeing txtBox code and (My)SqlConnection classes in the same routine is a "code smell" : https://learn.microsoft.com/en-us/dotnet/architecture/modern-web-apps-azure/common-web-application-architectures — granadaCoder, Aug 11 '20 at 13:06
I've changed the SQL syntax, I added spaces where it needs. Now the error is: "Invalid attempt to access a field before calling Read()" — lf.andreani, Aug 11 '20 at 13:44

score 0 · Answer 1 · answered Aug 11 '20 at 12:48

0

I see many problems in your code:

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

answered Aug 11 '20 at 12:48

Marco Salerno

5,131
2
12
32

Thanks for your comment, I've made some changes based on your answer. In my next program I'll use english instead of portuguese. – lf.andreani Aug 11 '20 at 13:50
Np if I solved all your problems accept the answer! – Marco Salerno Aug 11 '20 at 13:53
I've edited my question, I'm getting other issue now.. Can u help me? – lf.andreani Aug 11 '20 at 13:53
When the program tries to fill the textbox with the sql information, is my DR estructure? – lf.andreani Aug 11 '20 at 14:05
Debug your code and see which line is throwing the error – Marco Salerno Aug 11 '20 at 14:07

score 0 · Accepted Answer · answered Aug 12 '20 at 06:26

0

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();

answered Aug 12 '20 at 06:26

dear_vv

2,350
1
4
13

Here is a good idea: when you are not sure whether your query statement is correct, you can create a new query file in the Mysql Database to test it. – dear_vv Aug 12 '20 at 06:40
YOU SAVED MY LIFE <3 – lf.andreani Aug 12 '20 at 19:08
I made the change u suggested and now it is working great! Thanks. – lf.andreani Aug 12 '20 at 19:09

Using MySqlDataReader C#

2 Answers2