Manohar Kumar Manohar Kumar - 1 month ago 5
ASP.NET (C#) Question

If there is no row in database sum command return an error

An error is thrown when there is no data in data base while converting a string value into int.

try {

SqlCommand cmdc = new SqlCommand("SELECT SUM(Credited_amount) FROM IMS_Credit_Dir WHERE Credit_comp_id=1 AND Crdt_typ_id=1", con);
string companya_credit_amount = null, comapnyb_credit_amount = null;
con.Open();
SqlDataReader drc = cmdc.ExecuteReader();

if (drc.HasRows)
{

while (drc.Read())
{
companya_credit_amount = drc[0].ToString();
}
drc.Close();
con.Close();
}
SqlCommand cmdcp = new SqlCommand("SELECT SUM(Credited_amount) FROM IMS_Credit_Dir WHERE Credit_comp_id=2 AND Crdt_typ_id=1", con);
con.Open();
SqlDataReader drcp = cmdcp.ExecuteReader();
if (drcp.HasRows)
{

while (drcp.Read())
{
companyb_credit_amount = drcp[0].ToString();
}
drcp.Close();
con.Close();
}
if (!Page.IsPostBack)
{
int companyA = 0,companyB=0;
if (companya_credit_amount != "") { companyA = Convert.ToInt32(credit_amount.ToString()); }
if (companyb_credit_amount != ""){ companyB = Convert.ToInt32(companyb_credit_amount); }
int total = (companyA+companyB);
count_total_lbl.Text = "Rs." + " " + total.ToString();
count_comapnya_lbl.Text = "Rs." + " " + companya_credit_amount.ToString();
count_companyb_lbl.Text ="Rs."+" "+ companyb_credit_amount.ToString();
}
}
catch(Exception ex) { Label2.Text = ex.ToString(); }


If there is value its working fine.but when there is no value in data base there is an error msg.

System.FormatException: Input string was not in a correct format.

Answer

Try to replace this

SELECT SUM(Credited_amount) 

WITH

SELECT ISNULL(SUM(Credited_amount),0)

Also find one confusing code while converting Credited amount values

if (companya_credit_amount != "") { companyA = Convert.ToInt32(credit_amount.ToString()); }
                                                         ---------^^^^^
if (companyb_credit_amount != ""){ companyB = Convert.ToInt32(companyb_credit_amount); }

I don't know about your business requirement but What i think Instead of using credit_amount value companya_credit_amount should be use to show value for companyA variable right?

Comments