to labels put random values from database c#

Question

I need to select from database random nicknames and show these nicknames to multiple labels.

My code show only one nickname in all these labels ,but I need after button click random nicknames ,what is in database ,show in labels.

void Button1Click(object sender, EventArgs e)
        {
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=names.mdb";
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "SELECT ID,nickname FROM names where ID=3 ";
            OleDbDataReader reader = command.ExecuteReader();
            while (reader.Read()){
                label1.Text=reader["nickname"].ToString();
                label2.Text=reader["nickname"].ToString();
                label3.Text=reader["nickname"].ToString();
                label4.Text=reader["nickname"].ToString();
                label5.Text=reader["nickname"].ToString();
                label6.Text=reader["nickname"].ToString();
                label7.Text=reader["nickname"].ToString();
                label8.Text=reader["nickname"].ToString();
            }
            connection.Close(); 
        }

example of form http://greenlight.ucoz.lv/11cqsvX/ac_lb.png

to label1 nickname1, to label2 nickname26, to label3 nickname78, ...

Please help me and sorry for my bad english :)

In the while loop you populate every label with the same text. Anyway, if ID is a unique index you'll get only 1 nickname — Matteo Umili, May 26 '15 at 15:20

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

My idea would be to modify your SQL to select a random row, since your current SQL will always select the same row. Do this for every label you have by iterating through each label.

private void button1_Click(object sender, EventArgs e)
{
  OleDbConnection connection = new OleDbConnection();
  connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=names.mdb";
  connection.Open();

  foreach (Control control in Controls)
  {
    if (control is Label)
    {
      OleDbCommand command = new OleDbCommand();
      command.Connection = connection;
      command.CommandText = "SELECT nickname FROM names ORDER BY rnd(ID)";
      OleDbDataReader reader = command.ExecuteReader();
      reader.Read();
      control.Text = reader["nickname"].ToString();
    }
  }
  connection.Close();
}

edited May 23 '17 at 12:14

Community

1
1

answered May 26 '15 at 15:25

MikeB

2,402
1
15
24

can you please show me how to make this in my code? because im beginner in c# and dont know how to – eatmailyo May 26 '15 at 15:27
i dont know why ,but to me have error - Undefined function 'NEWID' in expression. – eatmailyo May 26 '15 at 20:05
oops. I didn't see you were using MS Access. The SQL has been modified per http://stackoverflow.com/questions/9937222/how-to-get-random-record-from-ms-access-database – MikeB May 26 '15 at 21:02
i read about this, but i cant understand how to write this in my code :( can you please help? – eatmailyo May 26 '15 at 21:07
I write like this `ORDER BY rnd(ID)` and then I get error "There is already an open DataReader associated with this Command which must be closed first." – eatmailyo May 26 '15 at 21:09
I'm not familiar with the `OlDbConnection`, but it seems that each command should be read and then closed. I've updated the code in my answer – MikeB May 26 '15 at 22:18
2

@MikeB your error is you should be putting the readers in `using` statements so they get disposed. (you should do the same for the command and connection too) – Scott Chamberlain May 26 '15 at 22:19

score 0 · Accepted Answer · answered May 26 '15 at 21:18

0

get answer ,now my code look this

OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=names.mdb";
            connection.Open();
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "SELECT ID,nickname FROM names ORDER BY rnd(ID)";
            foreach (Control control in Controls)
  {
    if (control is Label)
    {
      OleDbDataReader reader = command.ExecuteReader();
      reader.Read();
      control.Text = reader["nickname"].ToString();
      reader.Close();
    }
  }
            connection.Close();

Thanks @MikeB

answered May 26 '15 at 21:18

eatmailyo

670
1
12
33

1

Don't close the reader, put it in a [`using` statement](https://msdn.microsoft.com/en-us/library/yh598w02.aspx) instead. You should do the same with the connection and command too. – Scott Chamberlain May 26 '15 at 22:20
@eatmailyo Welcome to SO! Instead of writing up your own answer you should upvote & accept MikeB's answer since it's pretty much identical to what you just posted. – JNYRanger May 26 '15 at 22:23
@ScottChamberlain without closing reader ,my programm doesnt work :( – eatmailyo May 26 '15 at 22:41
1

To accept an answer, click the green check mark to its upper left. You don't have to upvote it. – Dour High Arch May 26 '15 at 22:47
Using a `using` statments calls `.Dispose()` for you, `Dispose()` calls `Close()` for you. You are still closing the reader, you are just closing it the "correct" way by disposing of it. – Scott Chamberlain May 26 '15 at 22:53

to labels put random values from database c#

2 Answers2