Casting Nullable and Insert From DGV

[maheshwag]

I am trying to insert data from dgv to sql as per below.

TransID      Amount                 Amount1
-------------------------------------------------
1         12000.00    
1                                    12000.00              
 
 

The above transaction shows that the column “Amount1 “ rows “1” has Null value where as same column’s rows “2” has value. I wants to insert the multiple records into table. The above transaction is sample.

I am trying to insert it from below which is fail to insert.

private void textBox1_KeyPress(object sender, KeyPressEventArgs e)
{
 if ((Keys)e.KeyChar == Keys.Enter) 
 {
  string mess = "Confirm";
  string cap = "Wanna Save";
  MessageBoxButtons bt = MessageBoxButtons.YesNo;
  DialogResult result;
  result = MessageBox.Show(mess, cap, bt);
  if (result == DialogResult.Yes) 
  {
   string connstr = "server=.;initial catalog=maa;uid=mah;pwd=mah";
   SqlConnection con = new SqlConnection(connstr);
   con.Open();
   for (int i = 0; i < dataGridView1.Rows.Count-1; i++) 
   {
     decimal? amt = null;
     decimal? amt1 = null;
     amt = Convert.ToDecimal(dataGridView1.Rows[i].Cells[0].Value.ToString());
     amt1 = Convert.ToDecimal(dataGridView1.Rows[i].Cells[1].Value.ToString());// try to casting null but fail
 
   if (amt.HasValue || amt1.HasValue)  
   {
    string sql = " insert into dummy(amount,amount1) values (@amount,@amount1)";
    SqlCommand cmd = new SqlCommand(sql, con);
    cmd.Parameters.Add("@amount", SqlDbType.Decimal).Value = amt;
    cmd.Parameters.Add("@amount1", SqlDbType.Decimal).Value = amt1;                                
 
   if (amt == null || amt1 == null)
   {
    cmd.Parameters.Add("@amount", SqlDbType.Decimal).Value = DBNull.Value;
    cmd.Parameters.Add("@amount1", SqlDbType.Decimal).Value = DBNull.Value;
 
   }
    cmd.ExecuteNonQuery();
    amt = null;
    amt1 = null;
 
                        }
 
                    }
 
                }
            }
 
        }
 
 
 

how to solve it?.

[hype261]

Why not do this...

 
if(dataGridView1.Rows[i].Cells[0].Value != null)
{
   cmd.Parameters.Add("@amount",SqlDbType.Decimal).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells[0].Value.ToString());
}
else
{
   cmd.Parameters.Add("@amount",SqlDbType.Decimal).Value = DBNull.Value;
 
}
 

You would have to do this for both parameters.

[maheshwag]

hi
hype261

It's throw Null reference exception. I explain How.

See I have try your code like below:

 string sql = " insert into dummy(amount,amount1)values(@amount,@amount1)";
                            SqlCommand cmd = new SqlCommand(sql, con);
                            if (dataGridView1.Rows[i].Cells[0].Value!=null ) 
                            {
                                cmd.Parameters.Add("@amount", SqlDbType.Decimal).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells[0].Value.ToString());
                                cmd.Parameters.Add("@amount1", SqlDbType.Decimal).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells[1].Value.ToString()); //this line throw exception error like "Null referenceException was un handle" 
                            }
                            else  
                            {
                                cmd.Parameters.Add("@amount", SqlDbType.Decimal).Value = DBNull.Value;
                                cmd.Parameters.Add("@amount1", SqlDbType.Decimal).Value = DBNull.Value;
                            }
                            cmd.ExecuteNonQuery();
 
 

When I enter the data as above said amount1 coloumn's first rows remain nullable hence it's throw NullException Error.

[hype261]

Yes that is expected. I mentioned this in my first post. You are going to have to test both variables to see if they are null.

Sometime like this.

 if (dataGridView1.Rows[i].Cells[0].Value!=null )  
 { 
     cmd.Parameters.Add("@amount", SqlDbType.Decimal).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells[0].Value.ToString()); 
} 
else   
{ 
cmd.Parameters.Add("@amount", SqlDbType.Decimal).Value = DBNull.Value; 
} 
 
 if (dataGridView1.Rows[i].Cells[1].Value!=null )  
 { 
 cmd.Parameters.Add("@amount1", SqlDbType.Decimal).Value = Convert.ToDecimal(dataGridView1.Rows[i].Cells[1].Value.ToString());                             
} 
else   
{ 
cmd.Parameters.Add("@amount1", SqlDbType.Decimal).Value = DBNull.Value; 
} 
 
 

[maheshwag]

hi
hype261

thanks for help dear