如果出现 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 个案例需要实施：根本没有数据：检查null无效数据，例如1 + 2 + NULL + 3 == NULL：检查DBNull.Value有效数据，例如1 + 2 + 3 == 6：借助Convert.ToDecimal()代码：// wrap IDisposable into usingusing (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

您需要为查询结果检查 nullvar 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){}