CODEWITHSUNDEEP
c#sql-serversql-server-2012
wylo
wylo

Reputation: 1

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

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();
    }
}

Upvotes: 0

Views: 269

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54417

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.

Upvotes: 1

Related Questions