c# mysql query with select distinct column

Question

I am trying to put the DISTINCT rows (which is a small string) in a string variable targeting a textbox, but it only returns some of the rows with different content in the column. I used similar code but targeting a datagridview and works ok. I tried different approaches, where am I wrong?

stConsulta = "SELECT distinct symbol from ticks";
MySqlCommand MyCmd = new MySqlCommand(stConsulta,cnn);
MyCmd.Connection = cnn;
MyCmd.CommandText = stConsulta;
MySqlDataReader myreader = MyCmd.ExecuteReader();
if (myreader.HasRows)
{
    while (myreader.Read())
    {
        myreader.Read();
        stSymbols = stSymbols +  myreader.GetString("symbol") + " ";
    }
}                        
cnn.Close();
textBox1.Text = stSymbols;

score 3 · Accepted Answer · answered Dec 23 '13 at 20:13

3

Do not call myReader.Read() twice, for starters. You're skipping every other row.

You indicated there aren't many rows, but you may also want to use a StringBuilder for efficiency.

...
var stSymbols = new StringBuilder();

while (myreader.Read())
    stSymbols.Append(myreader.GetString("symbol") + " ");

...
textBox1.Text = stSymbols.ToString();

answered Dec 23 '13 at 20:13

Grant Winney

65,241
13
115
165

3

I think I just heard the sound of a hand hitting a forehead. – Steve Wellens Dec 23 '13 at 20:13

score 1 · Answer 2 · answered Dec 23 '13 at 20:24

Just ignore this line inside the loop

myreader.Read();

when you use this in the condition of while(myreader.Read()) it starts reading it. so don't call it again in side the loop. that's what your mistake is.

if (myreader.HasRows)
{
    while (myreader.Read())
    {
        stSymbols = stSymbols +  myreader["symbol"].ToString() + " ";
    }
}

And you're good to go

score 0 · Answer 3 · answered Dec 23 '13 at 20:26

0

if (myreader.HasRows)
{
    while (myreader.Read())
    {
        stSymbols = stSymbols +  myreader["symbol"].ToString() + " ";
    }
}

answered Dec 23 '13 at 20:26

shujaat siddiqui

1,527
1
20
41

3

Can you include some explanation for how this solves the OP's problem? – Hannele Dec 23 '13 at 20:48
reader.Read() return true if there are rows to read. and then read the row one by one. if there is no more row to read it return false and you get out of the while loop. now how this solves the OP's problem ? basically when you write myreader.Read() in a inside loop it reset the datareader which causes the problem. So you do not have need to write myreader.Read() inside the while loop. hope its clear now @Hannele – shujaat siddiqui Dec 24 '13 at 07:17
really as soon I commented the the second reader.read() it worked fine. Thankyou all! Then I also used the stringbuilder advice and left this code. – user3130592 Dec 24 '13 at 18:17

c# mysql query with select distinct column

3 Answers3