如果出现 DBNULL，我如何使消息框出现

Question

我有一个问题，如果我通过 a 搜索数据TextBox并且数据库中不存在该数据，我会收到错误消息“对象不能从 DBNull 转换为其他类型。”我试图让自己MessageBox看起来像是在说数据不存在并且无法弄清楚如何做到这一点。我尝试使用一个if语句，如果TextBox等于DBNull则MessageBox出现 a。这不是t work and im not too sure why. The error occurs with me trying to **equal** toDBNull . How do i produce aTextBox` 说数据不存在吗？{        SqlConnection con = new SqlConnection("***COnString**");        con.Open();        SqlCommand comm = new SqlCommand("SELECT SUM (Total_Hours_Day) FROM Sign_In_Out_Table, User_Table WHERE User_Table.FirstName = '" + Search_Username_Alerts_Admin_txtbox.Text + "' AND Sign_In_Out_Table.eb_number = User_Table.eb_number AND Date between GETDATE()-14 and GETDATE()", con);        decimal TotalHoursFortnight = Convert.ToDecimal(comm.ExecuteScalar());        con.Close();        decimal sum = 0;        sum += Convert.ToDecimal(TotalHoursFortnight);        if (Search_Username_Alerts_Admin_txtbox.Text == DBNull)        {            MessageBox.Show("No Data Exists");        }        else        {            MessageBox.Show(Search_Username_Alerts_Admin_txtbox.Text + ":" + Environment.NewLine + " Hours Worked = " + TotalHoursFortnight, ("Working Info Admin"), MessageBoxButtons.OK, MessageBoxIcon.Information);        }}我的预期结果是出现一个消息框，提示搜索时数据不存在。但如果确实如此，数据将显示在消息框中。

Answer 1

您有3 个案例需要实施：

1. 根本没有数据：检查null

2. 无效数据，例如1 + 2 + NULL + 3 == NULL：检查DBNull.Value

3. 有效数据，例如1 + 2 + 3 == 6：借助Convert.ToDecimal()

代码：

// wrap IDisposable into using

using (SqlConnection con = new SqlConnection("***COnString**")) {

  con.Open();

  //DONE: Make sql readable

  //DONE: Make sql parametrized

  //TODO: you may want to change eb_number = eb_number into INNER JOIN

  string sql = 

    @"SELECT SUM (Total_Hours_Day) 

        FROM Sign_In_Out_Table, 

             User_Table 

       WHERE User_Table.FirstName = @prm_FirstName  

         AND Sign_In_Out_Table.eb_number = User_Table.eb_number 

         AND Date BETWEEN GETDATE() - 14 AND GETDATE()"; 

  using (SqlCommand comm = new SqlCommand(sql, con)) {

    //TODO: Better specify RDBMS type explictly with "comm.Parameters.Add(...)"

    comm.Parameters.AddWithValue(

      "@prm_FirstName", Search_Username_Alerts_Admin_txtbox.Text); 

    var result = comm.ExecuteScalar();

    if (null == result) {              // No Data

      MessageBox.Show("No Data Exist");

    }

    else if (DBNull.Value == result) { // We have the Data and it's RDBMS Null

      MessageBox.Show("Data Exist, but not valid.");

    } 

    else {                             // We have a valid Decimal

      Decimal sum = Convert.ToDecimal(result); 

      //TODO: put the relevant code here

    }

  }

}

Answer 2

我会使用 decimal.tryparse 来查看是否返回了数据

            SqlConnection con = new SqlConnection("***COnString**");

            con.Open();

            SqlCommand comm = new SqlCommand("SELECT SUM (Total_Hours_Day) FROM Sign_In_Out_Table, User_Table WHERE User_Table.FirstName = '" + Search_Username_Alerts_Admin_txtbox.Text + "' AND Sign_In_Out_Table.eb_number = User_Table.eb_number AND Date between GETDATE()-14 and GETDATE()", con);

            string TotalHoursFortnight = (comm.ExecuteScalar()).ToString();

            con.Close();

            decimal sum = 0;

            decimal temp;

            if(!decimal.TryParse(TotalHoursFortnight, out temp)) 

            { 

                MessageBox.Show("No Data Exists");

            }

            else

            {

                sum += temp;

                MessageBox.Show(Search_Username_Alerts_Admin_txtbox.Text + ":" + Environment.NewLine + " Hours Worked = " + TotalHoursFortnight, ("Working Info Admin"), MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

Answer 3

您需要为查询结果检查 null

var result = comm.ExecuteScalar();

    if(result != null){

         decimal TotalHoursFortnight = Convert.ToDecimal(comm.ExecuteScalar());

         // move remain code to if block

    }

并且if (Search_Username_Alerts_Admin_txtbox.Text == DBNull)应该改为

if (Convert.IsDBNull(Search_Username_Alerts_Admin_txtbox.Text){

}