Get values of a single column in C# mysql and put it in textbox

Question

I have this table called WeeklySales with 2 columns, DateandTime and Sales. Now, I have 3 textbox on my form. I wanted to get the latest value that was added on the table so I have this string.

    string sql = "SELECT Sales FROM database.weeklysales ORDER BY DateandTime DESC LIMIT 3";

Now, I have this database(lets say that month is the date),

 DateandTime | Sales
 March       | $300
 February    | $500
 January     | $400

and get this result with that string:

 Sales
 $300
 $500
 $400

Now, I wanted to put the first row into first textbox, then second row to second textbox and so on... Now, I do not know what to out in inside the Datareader...

        try
        {
            con.Open();
            using (reader = cmd.ExecuteReader())
            {
               first.Text = ?
               second.Text = ?
               third.Text = ?
            }
        }
        finally
        {
            con.Close();
        }

I have searched but they only get the first row unfortunately.

There are plenty of places that you can learn how to use a data reader properly. I suggest that you do that. — jmcilhinney, Feb 16 '18 at 09:20
I've search thoroughly but can't get the right result. If I've known what to put there, I wouldn't ask here. — Mamayg, Feb 16 '18 at 09:21
https://stackoverflow.com/questions/4018114/read-data-from-sqldatareader it can helps — Anatoli Klamer, Feb 16 '18 at 09:24

score 2 · Answer 1 · answered Feb 16 '18 at 09:30

2

The SqlDataReader class has a Read() method, which returns a bool as long as there are more rows to read. You can use it to read multiple rows using a while loop for example.

using (SqlDataReader reader = cmd.ExecuteReader()
{
   while (reader.Read())
   {
      //Do your stuff here
   }
}

See https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.read%28v=vs.110%29.aspx for further information

answered Feb 16 '18 at 09:30

Oerk

148
10

Thanks. I forgot to include that... But the thing I need the most is what should I put in first.Text = ?.... I always to reader.GetValue(0).ToString(); but it is for rows, no? – Mamayg Feb 16 '18 at 09:34

score 2 · Accepted Answer · answered Feb 16 '18 at 10:00

2

Since you only have 3 text boxes to fill - no loop just advance the reader manually.

MySqlDataReader dr = cmd.ExecuteReader();
dr.Read();
first.Text = dr.GetValue(0).ToString();
dr.Read();
second.Text = dr.GetValue(0).ToString();
dr.Read();
third.Text = dr.GetValue(0).ToString();

answered Feb 16 '18 at 10:00

Mary

14,926
3
18
27

Thank you. I solved the problem. But what if I want to put all the three into one richtextbox (with every data comes into one line)? I know I would use loop but what should I replace in `dr.GetValue(0).ToString();` – Mamayg Feb 16 '18 at 10:41
@Mamayg I think you should ask a new question showing what you have tried and how it didn't work. – Mary Feb 16 '18 at 21:21

Abhilash Ravindran C K · Answer 3 · 2018-02-16T11:10:50.377

1

Following code will be helpful to you,

using (reader = cmd.ExecuteReader())
{
    if (reader.HasRows)
    {   
       int i = 1;                    
       while (reader.Read())
       {
          switch (i)
          {
             case 1:
             first.Text = reader["Sales"].ToString();
             break;
             case 2:
             second.Text = reader["Sales"].ToString();
             break;
             default:
             third.Text = reader["Sales"].ToString();
             break;             
          } 
          i += 1;         
       }
     }
}

edited Feb 16 '18 at 11:10

answered Feb 16 '18 at 10:04

Abhilash Ravindran C K

1,818
2
13
22

Thank you but it still only shows the last (rather than the first) instead of all the values. Anyways, I solved my problem through the answer above. Thank you again, – Mamayg Feb 16 '18 at 10:37

score 0 · Answer 4 · answered Feb 16 '22 at 10:32

0

Avoiding repetitions or in case of multiple objects

public void Example(MySqlDataReader dr)
        {
            TextBox a = new TextBox();
            TextBox b = new TextBox();
            TextBox c = new TextBox();

            foreach(TextBox current in new List<TextBox> { a, b, c })
            {
                dr.Read();
                current.Text = dr.GetValue(0).ToString();
            }
        }

answered Feb 16 '22 at 10:32

PurpleZool

1

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/31083174) – Bruno Bieri Feb 23 '22 at 07:28

Get values of a single column in C# mysql and put it in textbox

4 Answers4