Error in null sum value

Question

I want to get the total of the books in the database using sum but if there is no book in the database, it gives error. I want it to write 0 instead of error. My codes are below.

OleDbCommand cmd = new OleDbCommand("select sum(adet) from kitaplar25", conn);
cmd.Parameters.AddWithValue("@adet", label14.Text);
label14.Text = cmd.ExecuteScalar().ToString();

Rajkiran Swain · Answer

OleDbCommand cmd = new OleDbCommand("SELECT SUM(adet) FROM kitaplar25", conn); object result = cmd.ExecuteScalar(); if (result != null) { label14.Text = result.ToString(); } else { label14.Text = "0"; }

Tuhin Paul · Answer

OleDbCommand cmd = new OleDbCommand("SELECT COALESCE(SUM(adet), 0) AS total FROM kitaplar25", conn); label14.Text = cmd.ExecuteScalar().ToString(); we use the COALESCE function to return the sum of books ( SUM(adet) ) or 0 if the result is NULL . We also use the AS keyword to alias the result as total . Finally, we assign the result to the label14.Text property.

Tuhin Paul · Answer

To handle the case where there are no books in the database and avoid the error, you can use the COALESCE function in your SQL query to replace the NULL value with 0.

Rajkiran Swain · Answer

If it will be helpful, please accept the answer.

Mehmet Fatih · Answer

Thanks you everyone for your efforts. I have solved the problem changing something in Rajkiran Swain's answer like that if (result != DBNull.Value).

Amit Mohanty · Answer

OleDbCommand cmd = new OleDbCommand("SELECT ISNULL(SUM(adet), 0) FROM kitaplar25", conn); label14.Text = cmd.ExecuteScalar().ToString();

Jignesh Kumar · Answer

Hi, Please check below and small change is query for isNull() function will solve your issue OleDbCommand cmd = new OleDbCommand("select isNull(sum(isNull(adet,0)),0)from kitaplar25", conn); cmd.Parameters.AddWithValue("@adet", label14.Text); label14.Text = cmd.ExecuteScalar().ToString();

Mehmet Fatih · Answer

You all are trying to find solution to my problem but I am very sorry to infirm that all of them give error. I am usinf access database. Maybe it depends on it. The closest answer is Rajkiran Swain's answer. The only problem with Rajkiran Swain's solution is that it doesn't write zeros.

Muhammad Imran Ansari · Answer

With an alternative way, you can use the ternary operator to provide a default value for null values. Here's an example: OleDbCommand cmd = new OleDbCommand("SELECT SUM(adet) FROM kitaplar25", conn); object result = cmd.ExecuteScalar(); int sum = result == null ? 0 : Convert.ToInt32(result); // Do something with the sum

Mehmet Fatih · Answer

I am sorry but none of them gives 0 result.

Rajkiran Swain · Answer

OleDbCommand cmd = new OleDbCommand("SELECT ISNULL(SUM(adet), 0) FROM kitaplar25", conn); label14.Text = cmd.ExecuteScalar().ToString(); Try this one too.