How to set sum to 0 if no data is found from the Database

Question

Hello DBA's once again I needed help.

I have this query which will give me the sum of all sold insurance.

select e.AgentID, sum(isnull(e.TotalPremium,0)) from tblMotorInsurance_eCI e where e.AgentID=@AgentID group by e.AgentID;

I give me the answer I needed if the agent has a sale. but if the agent doesn't have any record yet, it gives me this error:

Server Error in '/' Application.
There is no row at position 0.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.IndexOutOfRangeException: There is no row at position 0.
Source Error:
Line 102: catch (Exception ex)
Line 103: {
Line 104: throw ex;
Line 105: }
Line 106: }

I wanted to show me a sale = 0 if the record doesn't exist. Thanks for any help.

Himanshu Jaiswal · Answer

Make a check as given below: int sum=0; if(dt == null || dt.rows.count { sum = 0; } else { //Put the logic here }

Hold On · Answer

Thanks to both of you. Both of your recommendations worked. I applied Himanshu recommendation and added a method to the if clause. try { if (ds == null || ds.Tables[0].Rows.Count { IfTotalPremiumIsZero(); } else { lblCreditLimit.Text = ds.Tables[0].Rows[0][ CreditLimit ].ToString(); lblInsuranceSold.Text = ds.Tables[0].Rows[0][ InsuranceSold ].ToString(); lblPaidInsurance.Text = ds.Tables[0].Rows[0][ PaidInsurance ].ToString(); lblUnpaidInsurance.Text = ds.Tables[0].Rows[0][ UnpaidInsurance ].ToString(); lblCreditLimitBalance.Text = ds.Tables[0].Rows[0][ RemainingCreditLimit ].ToString(); } } catch (Exception ex) { if (ex is IndexOutOfRangeException) { IfTotalPremiumIsZero(); } else { throw ex; } } More power to both of you and thanks again.

Ankit Sharma · Answer

Hi  Please try below code IF EXISTS( SELECT NULL FROM tblMotorInsurance_eCI where AgentID=@AgentID) BEGIN SELECT e.AgentID sum ( isnull (e.TotalPremium0)) FROM tblMotorInsurance_eCI e WHERE e.AgentID=@AgentID GROUP by e.AgentID; END ELSE BEGIN SELECT 0 END Hope this helps