How can I use DBNull.Value with datagridview cells?

Question

I have this method which inserts data from a Datagridview in a table in my db , It works fine if all cells in my Datagridview are not Null but , If some cells are empty or null it throws the error :

The parameterized query expects the parameter , which was not supplied

The fact is some of cells can be null and the db will accept that , The required cells I need already forced to be filled by the user , So the optional cells could be null .

My method:

private void InsertAll()
{
    using (var cn = new SqlConnection(Cn.ConnectionString)) // Using block around the connection
    using (var cmd = new SqlCommand("", cn))
    {

        cmd.CommandText = @" INSERT INTO Transactions ( DocNum, Code, QtyIn, QtyOut, BalanceAfter, Remarks, Unit )
                     Values (@DocNum, @Code, @QtyIn, @QtyOut, @BalanceAfter, @Remarks,@Unit);";

        cmd.Parameters.Add("@DocNum", SqlDbType.NVarChar, 50);
        cmd.Parameters.Add("@Code", SqlDbType.NVarChar, 50);
        cmd.Parameters.Add("@QtyIn", SqlDbType.Int).Value = 0;
        cmd.Parameters.Add("@QtyOut", SqlDbType.Int).Value = 0;
        cmd.Parameters.Add("@BalanceAfter", SqlDbType.Int);
        cmd.Parameters.Add("@Remarks", SqlDbType.NVarChar, 50);
        cmd.Parameters.Add("@Unit", SqlDbType.NVarChar, 10);

        var qtyKey = (txtTransType.Text == "Release") ? "@QtyOut" : "@QtyIn";

        for (int i = 0; i < DGV1.Rows.Count; i++)
        {
            // These are 3 key fields which can't be null and I force the user to fill .
            // So no need to check if null cause it always will have some value .
            
            cmd.Parameters["@DocNum"].Value = txtDocNum.Text;
            cmd.Parameters["@Code"].Value = DGV1.Rows[i].Cells["Code"].Value;
            cmd.Parameters[qtyKey].Value = DGV1.Rows[i].Cells["Qty"].Value;

            // The rest is the optional cells that the db accepts to be null

            // Then I need to check if they are null and pass the DBNull.Value .            

            // Try DBValue.Null
            if (String.IsNullOrEmpty(DGV1.CurrentRow.Cells["BalanceAfter"].Value.ToString()))
            {
                cmd.Parameters["@BalanceAfter"].Value = DBNull.Value;
            }
            else
            {
                cmd.Parameters["@BalanceAfter"].Value = DGV1.Rows[i].Cells["BalanceAfter"].Value;
            }
            // Another Try

            if (String.IsNullOrEmpty(Remarks.ToString()))
            {
                cmd.Parameters["@Remarks"].Value = DBNull.Value;
            }
            else
            {
                cmd.Parameters["@Remarks"].Value = DGV1.Rows[i].Cells["Remarks"].Value;
            }
            if (String.IsNullOrEmpty(Unit.ToString()))
            {
                cmd.Parameters["@Unit"].Value = DBNull.Value;
            }
            else
            {
                cmd.Parameters["@Unit"].Value = DGV1.Rows[i].Cells["Unit"].Value;
            }

            cn.Open();
            cmd.ExecuteNonQuery();

        }
    }
}

This part :

  if   (String.IsNullOrEmpty(DGV1.CurrentRow.Cells["BalanceAfter"].Value.ToString()))
                    {
                        cmd.Parameters["@BalanceAfter"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@BalanceAfter"].Value = DGV1.Rows[i].Cells["BalanceAfter"].Value;
                    }

Throws error :

Object reference not set to an instance of object

I tried also This :

 if (String.IsNullOrEmpty(Remarks.ToString()))
                {
                    cmd.Parameters["@Remarks"].Value = DBNull.Value;
                }
                else
                {
                    cmd.Parameters["@Remarks"].Value = DGV1.Rows[i].Cells["Remarks"].Value;
                }

But causes the original error as if it doesn't exist at all . Appreciate your help , Thanks

Answer 1

This works guys , Simple null check without using string methods

  if (DGV1.CurrentRow.Cells["BalanceAfter"].Value == null)
                    {
                        cmd.Parameters["@BalanceAfter"].Value = DBNull.Value;
                    }
                    else
                    {
                        cmd.Parameters["@BalanceAfter"].Value = DGV1.Rows[i].Cells["BalanceAfter"].Value;
                    }

Thanks all