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.
ToString()
is already being called automatically. – OJ. Apr 2 '11 at 3:00