object reference not set to an instance of object sending empty value to sql table

Question

I get this error

object reference not set to an instance of object

when trying to send empty value to a SQL Server database. The column accepts NULL values. This is the code of my table

[id] [int] IDENTITY(1,1) NOT NULL,
[numeroreporte] [varchar](100) NULL,
[dias] [nvarchar](10) NULL,
[lugar] [nvarchar](max) NULL,
[workorder] [int] NULL,
[InOutHour] [nvarchar](30) NULL,
[overtime] [int] NULL,
[emergencia] [nvarchar](50) NULL,
[viaje] [nvarchar](50) NULL,
[almuerzo] [decimal](12, 2) NULL,
[peaje] [decimal](12, 2) NULL,
[otros] [decimal](12, 2) NULL,

This is my code in c#:

public void datosReporte ( DataGridView dgv,  Label linkLB )
{
    sqlCOMM = new SqlCommand("InsertarDatosReporte", sqlCON);
    sqlCOMM.CommandType = System.Data.CommandType.StoredProcedure;

    sqlCOMM.Parameters.Add("@numeroreporte", System.Data.SqlDbType.NVarChar); 
    sqlCOMM.Parameters.Add("@sp_dias", System.Data.SqlDbType.NVarChar) ;
    sqlCOMM.Parameters.Add("@sp_lugar", System.Data.SqlDbType.NVarChar) ;
    sqlCOMM.Parameters.Add("@sp_workorder", System.Data.SqlDbType.VarChar) ;
    sqlCOMM.Parameters.Add("@sp_InOutHour", System.Data.SqlDbType.NVarChar) ;
    sqlCOMM.Parameters.Add("@sp_overtime", System.Data.SqlDbType.Int).ToString() ;
    sqlCOMM.Parameters.Add("@sp_emergencia", System.Data.SqlDbType.NVarChar) ;
    sqlCOMM.Parameters.Add("@sp_viaje", System.Data.SqlDbType.NVarChar) ;
    sqlCOMM.Parameters.Add("@sp_almuerzo", System.Data.SqlDbType.Decimal) ;
    sqlCOMM.Parameters.Add("@sp_peaje", System.Data.SqlDbType.Decimal) ;
    sqlCOMM.Parameters.Add("@sp_otros", System.Data.SqlDbType.Decimal);

    try
    {
        sqlCON.Open();

        foreach (DataGridViewRow row in dgv.Rows)
        {
            if (!row.IsNewRow)
            {  
                sqlCOMM.Parameters["@numeroreporte"].Value =linkLB.Text; 
                sqlCOMM.Parameters["@sp_dias"].Value = row.Cells[0].Value.ToString();
                sqlCOMM.Parameters["@sp_lugar"].Value = row.Cells[1].Value.ToString();
                sqlCOMM.Parameters["@sp_workorder"].Value = row.Cells[2].Value;
                sqlCOMM.Parameters["@sp_InOutHour"].Value = row.Cells[3].Value.ToString();
                sqlCOMM.Parameters["@sp_overtime"].Value = row.Cells[4].Value;
                sqlCOMM.Parameters["@sp_emergencia"].Value = row.Cells[5].Value.ToString();
                sqlCOMM.Parameters["@sp_viaje"].Value = row.Cells[6].Value.ToString();
                sqlCOMM.Parameters["@sp_almuerzo"].Value = row.Cells[7].Value;
                sqlCOMM.Parameters["@sp_peaje"].Value = row.Cells[8].Value;
                sqlCOMM.Parameters["@sp_otros"].Value = row.Cells[9].Value; 

                sqlCOMM.ExecuteNonQuery(); 
            }
        }
    }
    catch (SqlException ex)
    {
        MessageBox.Show(ex.Message);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        sqlCON.Close();
    }
}

Which parameter having empty value/which line is causing the problem? and by empty you mean null? — Comfortably Numb
– Comfortably Numb, Commented Apr 25, 2014 at 20:41
How you try to send null value? Which of the parameters is null? — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Apr 25, 2014 at 20:42
SQL does not support where val = NULL it supports where val IS NULL, so you can't just throw null as a value. — Uri Agassi
– Uri Agassi, Commented Apr 25, 2014 at 20:43
Hamlet Hakobyan, all parameters are null, but when i see that error, i put default values. But if the user erase that value and send that empty value this catch appears. — wylo
– wylo, Commented Apr 29, 2014 at 20:37

jmcilhinney · Accepted Answer · 2014-04-25 20:46:22Z

1

The error message you get suggests to me that one of the cells where you use Value.ToString() actually contains null. You can't call ToString on an object that doesn't exist.

One solution is for you to check the cell Value first and, if it's null, assign DBNull.Value to the Value of the parameter. A better option would be to simply bind the grid to a DataTable and then empty cells would contain DBNull.Value by default. You would simply use a data adapter to save all the changes from the DataTable back to the database in a batch, so there would be no loop.

answered Apr 25, 2014 at 20:46

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wylo Over a year ago

jmcilhinney, im not have much experience but, but if you says that you write, in c sharp code i will understand better.

Collectives™ on Stack Overflow

object reference not set to an instance of object sending empty value to sql table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related