如果database sum命令中没有行，则返回错误

本文关键字：返回 错误 database sum 命令 如果 | 更新日期: 2023-09-27 18:12:10

在将字符串值转换为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(); }

如果有值，则工作正常。但是当数据库中没有值时，会有一个错误消息。

系统。

如果database sum命令中没有行，则返回错误

  1. 使用IsDBNull检查空值
    2. 创建和销毁所有在using block中实现IDisposable的类型实例。这确保了连接总是被释放，资源总是被清理。
  2. 不要跨类使用连接。在需要的时候创建它们，然后处理它们。Sql Server将处理连接池。
    3. 直接获取本机类型，而不是等效的字符串!参见数据读取器上的GetInt32而不是ToString的更改。
  3. 你应该重构它，使用SqlParameter，并使检索语句通用或获得两个SUM值在1 sql调用。
  4. 有一个if (!Page.IsPostBack)语句，如果没有这段代码做任何事情，如果它是回发，然后检查在页面的顶部，不执行sql语句，如果它是回发。否则代码会无缘无故地进行(可能)昂贵的sql调用。
try
{
    int companyA = 0,companyB=0;
    using(var con = new SqlConnection("connectionStringHere"))
    {
        con.Open();
        using(SqlCommand cmdc = new SqlCommand("SELECT SUM(Credited_amount) FROM IMS_Credit_Dir WHERE Credit_comp_id=1 AND Crdt_typ_id=1", con))
        using(SqlDataReader drc = cmdc.ExecuteReader())
        {
            if (drc.Read() && !drc.IsDBNull(0))
                companyA = drc.GetInt32(0);
        }
        using(SqlCommand cmdcp = new SqlCommand("SELECT SUM(Credited_amount) FROM IMS_Credit_Dir WHERE Credit_comp_id=2 AND Crdt_typ_id=1", con))
        using(SqlDataReader drcp = cmdcp.ExecuteReader())
        {
            if (drcp.Read() && !drcp.IsDBNull(0))
                companyB = drcp.GetIn32(0);
        }
    }
    // if you are not going to do anything with these values if its not a post back move the check to the top of the method
    // and then do not execute anything if it is a postback
    // ie:  // if (Page.IsPostBack) return;
    if (!Page.IsPostBack) 
    {
        int total = (companyA+companyB);
        count_total_lbl.Text = "Rs." + " " + total.ToString();
        count_comapnya_lbl.Text = "Rs." + " " + companyA.ToString();
        count_companyb_lbl.Text ="Rs."+" "+ companyB.ToString();
    }
}
catch(Exception ex) { Label2.Text = ex.ToString(); }

尝试替换

SELECT SUM(Credited_amount) 


SELECT ISNULL(SUM(Credited_amount),0)

还发现一个混乱的代码，同时转换记入金额值

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

我不知道你的业务需求，但我认为，而不是使用credit_amount值companya_credit_amount应该用来显示companyA变量的值对吗?

你应该做两件事:

  1. string companya_credit_amount = "， comapnyb_credit_amount = ";

  2. 在给这些字符串变量赋值之前，你应该检查db null，如下所示:

    while (drc.Read())
{
companya_credit_amount = (drc[0] != DbNull.Value) ? drc[0].ToString() :    "" ;
}

Similarely 

    while (drcp.Read())
    {
       companyb_credit_amount = (drcp[0] != DbNull.Value) ? drcp[0].ToString() : "";
    }

。

您需要初始化credit_amount为空，并检查db值是否为null，如下所示:

    try {
            companya_credit_amount = string.Empty;
            companyb_credit_amount = string.Empty;
            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 = cmd
c.ExecuteReader();
    if (drc.HasRows)
    {
        while (drc.Read())
        {
            companya_credit_amount = drcp.IsDBNull(0)?string.Empty:Convert.ToString(drcp[0]);
        }
        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.IsDBNull(0)?string.Empty:Convert.ToString(drcp[0]);
        }
        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(); }
相关文章：