none
经常遇到的SQL取值为空的问题 RRS feed

  • 问题

  •     下面这段代码是要取MonthRent的值,但根据Sql条件得到的结果可能为空,准确的说,应该是根据选择条件,没有任一项符合,最后取得的是连一个空值都算不上的东西!我用if (dr["MonthRent"].ToString() != "")来判断,就会出现如图所示的错误,该如何修改呢?

    ------------------------代码----------------------

    string strconn = "Data Source=.;Initial Catalog=长铁物业公司资产数据库;Integrated Security=True";
                    SqlConnection cn = new SqlConnection(strconn);
                    cn.Open();
                    string MonthRentTop = "select top(1) MonthRent from ContractTable where  ((DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%366=0 or (DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%365=0) and  City='";
                    string MonthRentSearch = DropDownList_City.SelectedItem.Value.ToString() + "' and Road ='" + DropDownList_Road.SelectedItem.Value.ToString() + "' and [Address] ='" + DropDownList_Address.SelectedItem.Value.ToString() + "' and ContractType ='租赁' order by LeaseDateTo desc";
                    MonthRentTop = MonthRentTop + MonthRentSearch;
                    SqlCommand cm = new SqlCommand(MonthRentTop, cn);
                    SqlDataReader dr = cm.ExecuteReader();
                    Response.Write(MonthRentTop);
                    dr.Read();
    
                    if (dr["MonthRent"].ToString() != "")
                    {
                        Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月";
                    }
    
                    else
                    { Label_MonthRent.Text = "上期合同可能为不定期"; }
                    
                    cn.Close();
    ---------------------------图片-------------------------


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!


    2013年6月8日 9:12

答案

  • 这样写:

    string strconn = "Data Source=.;Initial Catalog=长铁物业公司资产数据库;Integrated Security=True";
            SqlConnection cn = new SqlConnection(strconn);
            cn.Open();
            string MonthRentTop = "select top(1) MonthRent from ContractTable where  ((DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%366=0 or (DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%365=0) and  City='";
            string MonthRentSearch = DropDownList_City.SelectedItem.Value.ToString() + "' and Road ='" + DropDownList_Road.SelectedItem.Value.ToString() + "' and [Address] ='" + DropDownList_Address.SelectedItem.Value.ToString() + "' and ContractType ='租赁' order by LeaseDateTo desc";
            MonthRentTop = MonthRentTop + MonthRentSearch;
            SqlCommand cm = new SqlCommand(MonthRentTop, cn);
            SqlDataReader dr = cm.ExecuteReader();
            Response.Write(MonthRentTop);
    
            Label_MonthRent.Text = "上期合同可能为不定期";
            if (dr.Read() && dr["MonthRent"] != System.DBNull.Value)
            {
              Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月";
            }        
    cn.Close();

    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    2013年6月14日 2:02

全部回复

  •  if (dr["MonthRent"]!=null)

    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    2013年6月8日 9:14
  •     谢谢您的及时回复,明天我继续试试!但是我估计可能还是不行,因为确的说,应该是根据选择条件,没有任一项符合,最后取得的是连一个空值都算不上的东西。

        请看上面的图中的错误提示是“在没有任何数据时进行无效的读取尝试”


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!


    2013年6月8日 11:38
  •   

    if(dr.Read()) { if (dr["MonthRent"]!=DbValue.Null && dr["MonthRend"]!=null) { Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月"; } }

    你可以参考:DBNull.Value,null,String.Empty之间的区别和联系


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    2013年6月9日 1:15
  • 按照您的提示 我再次修改了代码,这样写总可以了把?可是还是错了!最后我只好在SQL里做文章,保证输出值不可能为空,才解决的。虽然解决了,但我很郁闷。。。真的只能像我这样曲线救国么?

    string strconn = "Data Source=.;Initial Catalog=长铁物业公司资产数据库;Integrated Security=True";
            SqlConnection cn = new SqlConnection(strconn);
            cn.Open();
            string MonthRentTop = "select top(1) MonthRent from ContractTable where  ((DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%366=0 or (DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%365=0) and  City='";
            string MonthRentSearch = DropDownList_City.SelectedItem.Value.ToString() + "' and Road ='" + DropDownList_Road.SelectedItem.Value.ToString() + "' and [Address] ='" + DropDownList_Address.SelectedItem.Value.ToString() + "' and ContractType ='租赁' order by LeaseDateTo desc";
            MonthRentTop = MonthRentTop + MonthRentSearch;
            SqlCommand cm = new SqlCommand(MonthRentTop, cn);
            SqlDataReader dr = cm.ExecuteReader();
            Response.Write(MonthRentTop);
            dr.Read();
            
            if (dr["MonthRent"] != null || dr["MonthRent"]!=System.DBNull.Value )
                {
                    Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月";
                }
            else
            { Label_MonthRent.Text = "上期合同可能为不定期"; }
    
            cn.Close();


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2013年6月9日 7:34
  • 仔细看我上面的代码,你加了if条件吗?

    if(dr.Read())


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    2013年6月9日 7:36
  • 你好,請試試這樣。

    While (dr.Read())
    { 
        if (dr["MonthRent"] != null || dr["MonthRent"]!=System.DBNull.Value )
        {
            Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月";
        }
        else
        { 
      Label_MonthRent.Text = "上期合同可能为不定期"; 
     }
    }
     


    大家一齊探討、學習和研究,謝謝!
    MCSD, MCAD, MCSE+I, MCDBA, MCDST, MCSA, MCTS, MCITP, MCPD,
    MCT, Microsoft Community Star(TW & HK),
    Microsoft MVP for VB.NET since 2003
    My MSMVP Blog

    2013年6月10日 6:23
  •     加上if(dr.Read()) 判断是吗?我加上了,没有出现错误了,可是Label_MonthRent无论何时都不会显示数值了。。。。。。

    string strconn = "Data Source=.;Initial Catalog=长铁物业公司资产数据库;Integrated Security=True";
            SqlConnection cn = new SqlConnection(strconn);
            cn.Open();
            string MonthRentTop = "select top(1) MonthRent from ContractTable where  ((DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%366=0 or (DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%365=0) and  City='";
            string MonthRentSearch = DropDownList_City.SelectedItem.Value.ToString() + "' and Road ='" + DropDownList_Road.SelectedItem.Value.ToString() + "' and [Address] ='" + DropDownList_Address.SelectedItem.Value.ToString() + "' and ContractType ='租赁' order by LeaseDateTo desc";
            MonthRentTop = MonthRentTop + MonthRentSearch;
            SqlCommand cm = new SqlCommand(MonthRentTop, cn);
            SqlDataReader dr = cm.ExecuteReader();
            Response.Write(MonthRentTop);
            dr.Read();
            if (dr.Read())
            {
                if (dr["MonthRent"] != null || dr["MonthRent"] != System.DBNull.Value)
                {
                    Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月";
                }
                else
                { Label_MonthRent.Text = "上期合同可能为不定期"; }
            }
            cn.Close();


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2013年6月13日 0:46
  • 删除“dr.Read()",只保留if(dr.Read()).

    另外检查确保你至少可以通过SQL语句获取一条记录。


    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    2013年6月13日 1:09
  •     删除了dr.Read()了,的确不会再弹出错误,可是实际上还存在着一个错误,那就是sql语句没有获取到一条合适的记录时,它不会转到  

    else { Label_MonthRent.Text = "上期合同可能为不定期"; }


        ,但是不会出错而已。这就是一直折磨着我的问题的核心所在了,该怎么继续解决呢?谢谢!

          

    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2013年6月14日 0:28
  • 这样写:

    string strconn = "Data Source=.;Initial Catalog=长铁物业公司资产数据库;Integrated Security=True";
            SqlConnection cn = new SqlConnection(strconn);
            cn.Open();
            string MonthRentTop = "select top(1) MonthRent from ContractTable where  ((DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%366=0 or (DATEDIFF (DAY,LeaseDateFrom,LeaseDateTo)+1)%365=0) and  City='";
            string MonthRentSearch = DropDownList_City.SelectedItem.Value.ToString() + "' and Road ='" + DropDownList_Road.SelectedItem.Value.ToString() + "' and [Address] ='" + DropDownList_Address.SelectedItem.Value.ToString() + "' and ContractType ='租赁' order by LeaseDateTo desc";
            MonthRentTop = MonthRentTop + MonthRentSearch;
            SqlCommand cm = new SqlCommand(MonthRentTop, cn);
            SqlDataReader dr = cm.ExecuteReader();
            Response.Write(MonthRentTop);
    
            Label_MonthRent.Text = "上期合同可能为不定期";
            if (dr.Read() && dr["MonthRent"] != System.DBNull.Value)
            {
              Label_MonthRent.Text = dr["MonthRent"].ToString() + "元/月";
            }        
    cn.Close();

    If you think one reply solves your problem, please mark it as An Answer, if you think someone's reply helps you, please mark it as a Proposed Answer

    Help by clicking:
    Click here to donate your rice to the poor
    Click to Donate
    Click to feed Dogs & Cats


    Found any spamming-senders? Please report at: Spam Report

    2013年6月14日 2:02
  •     终于完美解决了。非常感谢,把dr.Read()放到判断里,我还第一次知道!

        我另外想了一个办法,直接在SQL语句上做文章,使之输出值永不为空,也可以解决这个问题,不过还是你这个方法最好!


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2013年6月14日 8:52