C# SqlDataReader only returns values > 0

Question

This has been bugging me for the past several hours and I can't seem to find the answer..

I have the following query

SELECT        A, SUM(B) AS total
FROM          table
GROUP BY      A

now the column B in table can only hold 0 or 1. and A is total1, total2 or total3

now when I use this directly in the SQL database I get a nice table holding

A        total
total1   1
total2   0
toatl3   5

This is exactly what I want it to do. However if using in my c# program. if one of the totals is 0 it is not displayed at all.. Below is the code I'm using but it only works fine when total1, total2 and total3 are bigger then 0 so the table above would only display total1 and total3...

string total = "A        total";       
SqlConnection conn = new SqlConnection("connection string goes here I know");
try
{
    conn.Open();
    SqlCommand total = new SqlCommand(
        "SELECT A, SUM(B) AS total FROM table GROUP BY A", conn);

    SqlDataReader total_reader = total.ExecuteReader();
    while (total_reader.Read())
    {
        total += total_reader["A"].ToString() + "  " + total_reader["total"] + "\n";
    }
}
catch (Exception err)
{
    serverstats += err.ToString();
}
finally
{
    conn.Close();
}

How can I make it so that it would display the table properly, even if total1, total2 and total3 are 0

thus displaying:

A        total
total1   0
total2   0
toatl3   0

I know that a 0 in SQL generally equals to null and such.

I suspect that this is the reason that C# assumes that if the value is 0 that it is not of interest.

I hope I explained it properly enough, thanks for any help in advance!

=======EDIT======

COALESCE or ISNULL does not make a difference :(

I assume it's to do with the C# reader bit not in the SQL querying bit.

As you can see in my example the SQL bit DOES create a table with the correct Rows and does not write them as NULL. But the C# bit seems to read it as NULL.

I doubt this will fix the problem but have you tried calling ToString() on total_reader["total"]? — SquidScareMe
– SquidScareMe, Commented Apr 2, 2011 at 2:50
@SquidScareMe ToString() is already being called automatically. — OJ.
– OJ., Commented Apr 2, 2011 at 3:00

Bala R · Accepted Answer · 2011-04-02 03:25:02Z

2

If column B can have nulls, try

SELECT        A, COALESCE(SUM(B),0) AS total
FROM          table
GROUP BY      A

answered Apr 2, 2011 at 3:25

Bala R

109k23 gold badges203 silver badges212 bronze badges

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

2 Comments

JoshRoss Over a year ago

Shouldn't you sum(coalesce(b,0))?

Raskaroth Over a year ago

THanks, but I did try that already. as well as the ISNULL.. My guess is the C# SqlDataReader bit is to blame for reading the 0 as a NULL road

ysrb · Accepted Answer · 2011-04-02 10:37:21Z

1

which sql database are you using? sql server or mysql?

or try to edit the line:

total += total_reader["A"].ToString() + " " + total_reader["total"] + "\n";

to

total += total_reader["A"].ToString() + " " + int.Parse(total_reader["total"].ToString()) + "\n";

answered Apr 2, 2011 at 10:37

ysrb

6,7202 gold badges32 silver badges30 bronze badges

Comments

marc_s · Accepted Answer · 2012-11-28 21:59:19Z

0

Okay turns out that I had WHERE (B = 1)

I forgot to take this out of my SQL query..

Thanks for the help :)

edited Nov 28, 2012 at 21:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 2, 2011 at 11:00

Raskaroth

6591 gold badge10 silver badges26 bronze badges

Collectives™ on Stack Overflow

C# SqlDataReader only returns values > 0

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related