Handling NULL data from DataGridView values to INSERT in SQL Server

Question

I'm using C# Winforms to INSERT some NULL or Empty values from the DataGridView to my SQL Server. But there is an error at this line.

cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = dataGridWork.Rows[i].Cells[1].Value.ToString();

System.NullReferenceException: 'Object reference not set to an instance of an object.'

Since it is expecting a value that is not NULL (Please do correct me if I'm wrong). This is the code for my INSERT query.

using (SqlConnection conn = new SqlConnection(@"Server=" + ip + "," + port + "; Database=records; User ID=" + sqlid + "; Password=" + sqlpass + ""))
{
    conn.Open();
    for (int i = 0; i < dataGridWork.Rows.Count; i++)
    {
        using (SqlCommand cmd = new SqlCommand(@"INSERT INTO [dbo].[workorder]
                                            ([Work Order]
                                            ,[Plate No.]
                                            ,[Date]
                                            ,[Status]
                                            ,[Work Description]
                                            ,[Labor Price]
                                            ,[Item]
                                            ,[Unit Price]
                                            ,[Qty]
                                            ,[Unit]
                                            ,[Item Price]
                                            ,[Mechanic]
                                            ,[Deposit])
                                        VALUES
                                            (@WOrder
                                            ,@Plate
                                            ,@Date
                                            ,@Status
                                            ,@WDesc
                                            ,@LPrice
                                            ,@Item
                                            ,@UPrice
                                            ,@Qty
                                            ,@Unit
                                            ,@IPrice
                                            ,@Mechanic
                                            ,@Deposit)", conn))
        {
            cmd.Parameters.Add("@WOrder", SqlDbType.VarChar).Value = "WRK" + Properties.Settings.Default.work;
            cmd.Parameters.Add("@Plate", SqlDbType.VarChar).Value = plate;
            cmd.Parameters.Add("@Date", SqlDbType.Date).Value = dataGridWork.Rows[i].Cells[0].Value.ToString();
            cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = dataGridWork.Rows[i].Cells[1].Value.ToString();
            cmd.Parameters.Add("@WDesc", SqlDbType.VarChar).Value = dataGridWork.Rows[i].Cells[2].Value.ToString();
            cmd.Parameters.Add("@LPrice", SqlDbType.Decimal, 18).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[3].Value);
            cmd.Parameters.Add("@Item", SqlDbType.VarChar).Value = dataGridWork.Rows[i].Cells[4].Value.ToString();
            cmd.Parameters.Add("@UPrice", SqlDbType.Decimal, 18).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[5].Value);
            cmd.Parameters.Add("@Qty", SqlDbType.Int).Value = dataGridWork.Rows[i].Cells[6].Value.ToString();
            cmd.Parameters.Add("@Unit", SqlDbType.VarChar).Value = dataGridWork.Rows[i].Cells[7].Value.ToString();
            cmd.Parameters.Add("@IPrice", SqlDbType.Decimal, 18).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[8].Value);
            cmd.Parameters.Add("@Mechanic", SqlDbType.VarChar).Value = txtWMech.Text;
            cmd.Parameters.Add("@Deposit", SqlDbType.Decimal, 18).Value = numWDepo.Value;
            cmd.ExecuteNonQuery();
        }
    }
    conn.Close();
    Properties.Settings.Default.work = Properties.Settings.Default.work + 1;
}

Most of the columns can be null or empty. So I tried adding an If-Else for every SqlParameter that will be expecting NULL or empty. These are Status, WDesc, LPrice, Item, UPrice, Qty, Unit, and IPrice Parameters.

Now the whole parameter block is like this,

cmd.Parameters.Add("@WOrder", SqlDbType.VarChar).Value = "WRK" + Properties.Settings.Default.work;
cmd.Parameters.Add("@Plate", SqlDbType.VarChar).Value = plate;
cmd.Parameters.Add("@Date", SqlDbType.Date).Value = dataGridWork.Rows[i].Cells[0].Value;
if (dataGridWork.Rows[i].Cells[1].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@Status", dataGridWork.Rows[i].Cells[1].Value));
}
else
{
    cmd.Parameters.Add(new SqlParameter("@Status", DBNull.Value.ToString()));
}
if (dataGridWork.Rows[i].Cells[2].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@WDesc", SqlDbType.VarChar)).Value = dataGridWork.Rows[i].Cells[2].Value.ToString();
}
else
{
    cmd.Parameters.Add(new SqlParameter("@WDesc", DBNull.Value.ToString()));
}
if (dataGridWork.Rows[i].Cells[3].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@LPrice", SqlDbType.Decimal, 18)).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[3].Value);
}
else
{
    cmd.Parameters.Add(new SqlParameter("@LPrice", DBNull.Value));
}
if (dataGridWork.Rows[i].Cells[4].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@Item", SqlDbType.VarChar)).Value = dataGridWork.Rows[i].Cells[4].Value.ToString();
}
else
{
    cmd.Parameters.Add(new SqlParameter("@Item", DBNull.Value.ToString()));
}
if (dataGridWork.Rows[i].Cells[5].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@UPrice", SqlDbType.Decimal, 18)).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[5].Value);
}
else
{
    cmd.Parameters.Add(new SqlParameter("@UPrice", DBNull.Value));
}
if (dataGridWork.Rows[i].Cells[6].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@Qty", SqlDbType.Int)).Value = dataGridWork.Rows[i].Cells[6].Value;
}
else
{
    cmd.Parameters.Add(new SqlParameter("@Qty", DBNull.Value));
}
if (dataGridWork.Rows[i].Cells[7].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@Unit", SqlDbType.VarChar)).Value = dataGridWork.Rows[i].Cells[7].Value.ToString();
}
else
{
    cmd.Parameters.Add(new SqlParameter("@Unit", DBNull.Value.ToString()));
}
if (dataGridWork.Rows[i].Cells[8].Value != null)
{
    cmd.Parameters.Add(new SqlParameter("@IPrice", SqlDbType.Decimal, 18)).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[8].Value);
}
else
{
    cmd.Parameters.Add(new SqlParameter("@IPrice", DBNull.Value));
}
cmd.Parameters.Add("@Mechanic", SqlDbType.VarChar).Value = txtWMech.Text;
cmd.Parameters.Add("@Deposit", SqlDbType.Decimal, 18).Value = numWDepo.Value;
cmd.ExecuteNonQuery();

Now it can insert the values for every row inside my DataGridView. But is there a way how can I shorten or eliminate the If-Else statement? Or to make the code look cleaner?

You could use the null-conditional operator - cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = dataGridWork.Rows[i].Cells[1].Value?.ToString() ?? DBNull.Value; - the ? after the value means "if not null, use this value" and the ?? means "if it is null, use this value" — stuartd
– stuartd, Commented Jun 19, 2018 at 9:12
Use Sql Server Management Studio (SSMS) and check using the explorer that the database Table Columns are set to allow for Nulls. Find the table and the open up columns. The columns should show the type and then null like : ID (varchar(20), null) — jdweng
– jdweng, Commented Jun 19, 2018 at 9:16
@stuartd but how about if it is int or decimal or double? Convert.ToDouble(dataGridWork.Rows[i].Cells[3].Value ?? DBNull.Value); I have tried this but it returns an error — Jepher
– Jepher, Commented Jun 19, 2018 at 10:26
@Jepher sorry missed that. If the value is null, do you want to send 0 or null to the DB? For the first case it would be Convert.ToDouble(dataGridWork.Rows[i].Cells[3].Value); — stuartd
– stuartd, Commented Jun 19, 2018 at 10:45
I didnt use ?? DBNull.Value for numeric values. Just a normal Parameters.Add with a converted value. cmd.Parameters.Add("@LPrice", SqlDbType.Decimal, 18).Value = Convert.ToDouble(dataGridWork.Rows[i].Cells[3].Value); and it's working. It returns 0 for Int and 0.00 for Decimal. Thank you @stuartd — Jepher
– Jepher, Commented Jun 19, 2018 at 10:46

Vikas Chaturvedi · Accepted Answer · 2018-06-19 10:41:18Z

2

You can use extension methods for this. e.g.

public static T GetValueOrDefault<T>(this DataGridViewRow sourceRow, int columnIndex, T defaultValue = default(T))
{
    var o = sourceRow.Cells[columnIndex];
    if (o is T) return (T)o; 
    return defaultValue;
}

Use this as below:

dataGridWork.Rows[i].GetValueOrDefault<int>(1,0);

edited Jun 19, 2018 at 10:41

answered Jun 19, 2018 at 9:11

Vikas Chaturvedi

745 bronze badges

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

2 Comments

TaW Over a year ago

DataGridRow != DataGridViewRow

Jepher Over a year ago

As for you sample reference, it is pointing an error to sourceRow stating that Cannot apply indexing with [] to an expression of type 'DataGridViewRow'. I tried changing it to this DataRow sourceRow. And its pointing the error to GetValueOrDefault<int>

Collectives™ on Stack Overflow

Handling NULL data from DataGridView values to INSERT in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related