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

Jes Sie

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.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Himanshu Gupta9y ago
Accepted answer
Make a check as given below:
int sum=0;
if(dt == null || dt.rows.count <1)
{
sum = 0;
}
else
{
//Put the logic here
}
Jes Sie9y ago
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 < 1)
                        {
                            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 Sharma9y ago
Hi ,

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