Using MySQL Data Reader

Question

I'm not familiar with using Data Reader, i need help with the following code, i want to retrieve a single data from the database.

MySqlDataAdapter data = new MySqlDataAdapter(cmd);
                    conn.Open();
                    DataTable dt = new DataTable();
                    data.Fill(dt);
                    gridView1.DataSource = dt;

                    int retrievedValue = 0;
                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if ((int)reader["order_status"] == 0)
                            {
                                retrievedValue = (int)reader.GetValue(0);

                                    GridView View2 = sender as GridView;
                                    e.Appearance.BackColor = Color.Green;
                                    e.Appearance.BackColor2 = Color.ForestGreen;
                            }
                        }
                    }

What is the type of `order_status` column? Also you need to use `==` for equality checking, `=` is assignment operator. — Soner Gönül, Apr 16 '15 at 13:11

Soner Gönül · Accepted Answer · 2015-04-16T13:27:11.647

5

reader["order_status"] returns object, since you told it is an already integer, you need to cast it to int first.

You need to use == operator as well since it is a equality operator. = operator is an assignment operator.

if ((int)reader["order_status"] == 0)

Or you can use GetInt32 method with it's zero-based column number. Let's say it's the first column that your query returns, you can use it like;

if(reader.GetInt32(0) == 0)

By the way, if you wanna get only single value, I strongly suspect you may wanna use ExecuteScalar method since it get's the first column of the first row. Then you can structure your query as SELECT order_status FROM ... etc..

edited Apr 16 '15 at 13:27

answered Apr 16 '15 at 13:15

Soner Gönül

97,193
102
206
364

@KevinRodriguez If value that you want to get is the first column of the first row, you can use `ExecuteScalar` method like, `int value = (int)cmd.ExecuteScalar();`. Remember, your query _can_ get multiple rows with multiple columns. This does not break this methods. `ExecuteScalar` method gets first column of the first row and other values in your command are ignored. – Soner Gönül Apr 16 '15 at 13:31
im not getting this right, the code wont let me retrieve rows that has a value of 0? – Kevin Rodriguez Apr 16 '15 at 13:39
What is your `cmd` query exactly? You will not need `ExecuteReader` when you use `ExecuteScalar` by the way. – Soner Gönül Apr 16 '15 at 14:31

Gus · Answer 2 · 2015-04-16T14:31:19.047

1

Be sure to assign a variable before while (reader.Read()) otherwise it will and error. Then close the data reader once you are finished using it. Like so:

 using (MySqlDataReader reader = cmd.ExecuteReader())    
    {

    int retrievedValue = 0;

          while (reader.Read())
          {
                retrievedValue = (int)reader.GetValue(0);
                if (retrievedValue == 0)
                {

                    GridView View2 = sender as GridView;
                                    e.Appearance.BackColor = Color.Green;
                                    e.Appearance.BackColor2 = Color.ForestGreen;
                }
                else if (retrievedValue == 1)
                {

                    GridView View2 = sender as GridView;
                                    e.Appearance.BackColor = Color.Red;
                                    e.Appearance.BackColor2 = Color.ForestGreen;
                }
          }//and so on...
          reader.Close();
    }

I hope this is was you're looking for.

edited Apr 16 '15 at 14:31

answered Apr 16 '15 at 13:19

Gus

31
5

thank you, will this code retrieve rows that has a value of 0? – Kevin Rodriguez Apr 16 '15 at 13:40
Which column number do you want to retrieve data from? If you want to retrieve from the first column then use `retrievedValue = dr.GetValue(0)` or from second column, `retrievedValue = dr.GetValue(1)`, etc. Note that it will only use 1 row but you must specify which column you want in dr.GetValue. – Gus Apr 16 '15 at 13:43
You may also need to convert data to a data type such as: `Convert.ToInt32(dr.GetValue(0));` but for strings, you do not: `dr.GetString(0)` – Gus Apr 16 '15 at 13:45
order_status column, it has a value of ranging from 1 - 6, and if the value of a row is 1 i need to paint the gridview row to red and if it is 2 need to paint it with green and so on. im getting an error Could not find specified column in results: order_status – Kevin Rodriguez Apr 16 '15 at 13:48
The error is likely caused by misspellings in your query. Make sure that your column names, tables, etc are correctly spelled. – Gus Apr 16 '15 at 14:08
I'm not sure what the purpose of `(reader["order_status"] ==0` is. If you are trying to find 0 in "order_status" then I would add 'WHERE order_status = 0' to my SQL query and replace the if statement with `if (reader.Read() == 0)` and delete `retrievedValue = dr.GetValue(X)` I will update my answer. – Gus Apr 16 '15 at 14:17

Using MySQL Data Reader

2 Answers2

Linked

Related