SqlDataReader displaying like fields

Question

When I try to display only distinct values in my DB with SqlDataReader, it is still showing duplicate columns.

string State = ddlState.SelectedItem.Value;
string City = ddlCities.SelectedItem.Text;

SqlConnection con = new SqlConnection("Data Source=--myDB--;Database=--MyconnectionString--");

SqlCommand cmd = new SqlCommand("Select distinct State, City, ExchangeType, Boid, Bex, Npa, Nxx from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);

if (!string.IsNullOrEmpty(ddlCities.SelectedItem.Text))
{
    exchangesheet.Visible = true;

    con.Open();
    SqlDataReader rdr = cmd.ExecuteReader();

    while (rdr.Read())
    {
        this.STATE.Text = rdr["State"].ToString();
        this.CITY.Text = rdr["City"].ToString();
        this.ExchangeType.Text = rdr["ExchangeType"].ToString();
        this.BOID.Text += rdr["Boid"].ToString() + ", ";
        this.BEX.Text += rdr["Bex"].ToString() + ", ";
        this.NPA.Text += rdr["Npa"].ToString() + ", ";
        this.NXX.Text += rdr["Nxx"].ToString() + ", ";
    }

    con.Close();
}
else
{
    exchangesheet.Visible = false;
}

All queries with += are resulting in duplicate data in the labels, even though I have specified only distinct data to be shown in the SqlCommand query. I apologize if I am not explaining correctly but any help or direction will be much appreciated.

Results when searching a specific state:

BOID: 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205,   
BEX: 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205, 9205,    
NPA: 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205, 205,    
NXX: 212, 214, 226, 238, 244, 250, 251, 252, 254, 262, 263, 278, 279, 290, 298, 307, 313, 314, 320, 321, 322, 323, 324, 326, 328, 380, 397, 402, 403, 408, 414, 421, 423, 437, 444, 445, 453, 458, 502, 520, 533, 536, 545, 558, 583, 591, 592, 595, 599, 682, 714, 731, 733, 776, 780, 781, 783, 786, 787, 788, 791, 798, 801, 802, 803, 808, 822, 823, 824, 833, 836, 841, 854, 856, 868, 870, 871, 873, 876, 879, 913, 916, 917, 923, 925, 929, 930, 933, 939, 940, 941, 942, 943, 945, 949, 951, 956, 957, 967, 968, 969, 970, 972, 977, 978, 979, 980, 981, 982, 983, 985, 987, 988, 989, 991, 995,

Have you tried to add a GROUP BY clause in your SQL SELECT statement? — abhi
– abhi, Commented Jun 6, 2017 at 16:34
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Jun 6, 2017 at 16:38
I have not tried the GROUP BY clause yet, I will look into that. This is a small internal tool being used by me at work so I am not worried about injections at the moment, thank you though! — jsnhndrsn1985
– jsnhndrsn1985, Commented Jun 6, 2017 at 16:50
GROUP BY without any aggregates is actually the same as DISTINCT. This is essentially how distinct is suposed to work - in the presented case you are getting a row for every distinct value of NXX — user6144226
– user6144226, Commented Jun 6, 2017 at 16:55

Eric Lizotte · Accepted Answer · 2017-06-06 18:40:39Z

1

When doing a query with distinct, it will give you the distinct combination of all the selected columns. to accomplish what you want, it would have to be more like this

SqlCommand cmd = new SqlCommand("Select distinct State from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
...code to collate all the unique values and add to appropriate label...

SqlCommand cmd = new SqlCommand("Select distinct City from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
...code to collate all the unique values and add to appropriate label...

SqlCommand cmd = new SqlCommand("Select distinct Exchange from TAS where StateId=" + "'" + State + "'" + " AND City=" + "'" + City + "'", con);
...code to collate all the unique values and add to appropriate label...

etc.

There are many other ways to accomplish the same thing as well

For the repeating the multiple queries, you can use something roughly like this

SqlCommand cmd = new SqlCommand..the query for getting unique state
con.Open();
using ( SqlDataReader rdr = cmd.ExecuteReader())
  {
    while (rdr.Read())
      {
          this.STATE.Text = rdr["State"].ToString();
       }
    }

then repeating the others

edited Jun 6, 2017 at 18:40

answered Jun 6, 2017 at 16:37

Eric Lizotte

2241 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jsnhndrsn1985 Over a year ago

I will give it a shot. Thanks!

jsnhndrsn1985 Over a year ago

It seems it does not like that I am opening multiple SqlDataReader reader = cmd.ExecuteReader(); running at the same time. I have separated the SqlCommands and updated the variables but tells me that the original reader needs to be closed, however simply stating rdr.Close(); doesn't work either.

Eric Lizotte Over a year ago

do it like this SqlCommand cmd = new SqlCommand..the query for getting unique state con.Open(); using ( SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { this.STATE.Text = rdr["State"].ToString(); } } then repeat that for the next city, exchange type, etc

Isaiah · Accepted Answer · 2017-06-06 16:37:14Z

0

What happens when you replace the += with = ?

answered Jun 6, 2017 at 16:37

Isaiah

1822 silver badges6 bronze badges

1 Comment

jsnhndrsn1985 Over a year ago

It will only show only value in each field and not show any other unique values, BOID: 205, BEX: 9205, NPA: 205, NXX: 995,

user6144226 · Accepted Answer · 2017-06-06 18:48:41Z

Assuming that for some reason you can't afford to make multiple queries to the DB, and that your result data set is small. This might be faster if your server round trip is long or your DBA will hate you for spamming him with 6 table scans etc. You could perform the actual distinct in your code.

        con.Open();
        var hsNxx = new HashSet<int>();
        var hsBex = new HashSet<int>();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {


            while (rdr.Read())
            {
                hsBex.Add((int)rdr["Bex"]);
                hsNxx.Add((int)rdr["Nxx"]);

            }

        }

        con.Close();
        string distinctBex =hsBex.Count == 1 ? hsBex.First().ToString() : hsBex.Select(f => f.ToString()).Aggregate((x, y) => x + "," + y);
        this.BEX.Text = distinctBex;

Assumed the columns are ints. You could also implement the Text to be changed only once and not mutated every row with +=

As a side note I recommend warpping IDisposables of connection/reader/command in a using clause

Collectives™ on Stack Overflow

SqlDataReader displaying like fields

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related