I get error when my column is empty.

double tot_main, tot_oper, gift, sum_tot, sum_tot_gift, amount_gift_new, amount_cut;

// sum MainOper
SqlCommand check_main = new SqlCommand("select Sum(amount_Gift) from MainOper where  Emp_no='"   TextBox1.Text   "' ", con);
SqlDataAdapter sd1 = new SqlDataAdapter(check_main);
DataTable dt1 = new DataTable();
sd1.Fill(dt1);
// sum Oper
SqlCommand check_Oper = new SqlCommand("select Sum(amount_Gift) from Oper where  Emp_no='"   TextBox1.Text   "' ", con);
SqlDataAdapter sd2 = new SqlDataAdapter(check_Oper);
DataTable dt2 = new DataTable();
sd2.Fill(dt2);

// variable
gift = double.Parse(TextBox8.Text);
tot_main = double.Parse(dt1.Rows[0][0].ToString()); // note:when empty or 0 cat get sum_tot
tot_oper = double.Parse(dt2.Rows[0][0].ToString()); // note:when empty or 0 cant get sum_tot
// variable
sum_tot = tot_oper   tot_main; //when have value in tot_main & tot_oper is done - need every table have number
sum_tot_gift = sum_tot   gift;
amount_gift_new = sum_tot_gift - 1000;
amount_cut = gift - amount_gift_new;

else if (amount_cut <= 1000)
{
    SqlCommand co = new SqlCommand("exec gifter '"   Emp_no2   "','"   Emp_name2   "','"   Emp_dept2   "','"   Emp_poss2   "', '"   Ref_Gift2   "','"   Run_Gift2   "','"   Date_Gift2   "','"   amount_cut   "', '"   Type_Gift2   "','"   Month_num2   "'", con);
    co.ExecuteNonQuery();
    con.Close();
    Label13.Text = "successfuly";
    GetProductionList();
}

CodePudding user response：

The issue is two issues:

Rows can be returned, but have nulls, and sum = null value.

No rows returned, then NO OBJECT is returned!!!

If you use a datatable, then sum() would in theory always return a value even when criteria fails (but, the sum() result can still be "db null". So BOTH dbnull, and null/no object/noting can be returned when using Execute scaler like I used below).

However, using ExecuteScaler still saves us a LOT of code, and not even having to define some datatable(s). so, I just use a helper routine (MyNz).

So with MyNz, you can have the 2nd default value of "" for strings or whatever you need/want for a dbnull or nothing/null object.

So, this code should help:

       double sum_tot, sum_tot_gift, amount_gift_new, amount_cut, gift;

        // sum MainOper
        string strSQL =
            "select Sum(amount_Gift) from MainOper where  Emp_no = @Emp_no";

        using (SqlCommand cmdSQL = new SqlCommand(strSQL, con))
        {
            con.Open();

            cmdSQL.Parameters.Add("@Emp_no", SqlDbType.NVarChar).Value = textbox1.Text;
            double tot_main = (double)MyNz(cmdSQL.ExecuteScalar(),0d);

            cmdSQL.CommandText =
                "select Sum(amount_Gift) from Oper where  Emp_no = @Emp_No";
            double tot_oper = (double)MyNz(cmdSQL.ExecuteScalar(),0d);

            gift = double.Parse(TextBox8.Text);

            sum_tot = tot_oper   tot_main; //when have value in tot_main & tot_oper is done - need every table have number
            sum_tot_gift = sum_tot   gift;
            amount_gift_new = sum_tot_gift - 1000;
            amount_cut = gift - amount_gift_new;
        }

And since that test for null sum value, or NOTHING returned, then I have this helper routine:

    object MyNz(object value, object Default)
    {
        if (value == DBNull.Value || value == null)
            return Default;
        else
            return value;
    }

Make sure the "default" is of the correct type.

So, for literal values, use this:

0d;  // double
0f;  // float
0m;  // decimal
0;   // int

Note how we used a parameter, but we do NOT have to change it for the 2 queries, and thus they are the same. And above assumes that the parameter is of type string - but if it is not, then change the sqldbType to the correct data type.

And note how we did not even have to bother with data tables.

So, EVEN with parameters, the code is safe from sql injection, but MORE important is still less code, easy to read code, and more maintainable.

You don't show where/when/how the connection was created here, but it should be the topmost using block. We open the connection, and then let the system/using block automatic close that connection for us.

CodePudding user response：

error message > System.FormatException: 'Input string was not in a correct format.'

double tot_main, tot_oper, gift, sum_tot, sum_tot_gift, amount_gift_new, amount_cut;

        // sum Oper
        SqlCommand check_Oper = new SqlCommand("select Sum(amount_Gift) from Oper where  Emp_no='"   TextBox1.Text   "' ", con);
        SqlDataAdapter sd2 = new SqlDataAdapter(check_Oper);
        DataTable dt2 = new DataTable();
        sd2.Fill(dt2);
        // variable
        gift = double.Parse(TextBox8.Text);
        tot_oper = double.Parse(dt2.Rows[0][0].ToString());
        sum_tot_gift = tot_oper   gift;