How to Insert null value for a nullable integer column to SQL Table by C#

Question

I am reading a CSV file via OleDBConnection, retrieving integer values for some fields there and saving to SQL DB. I have a column named Q3 which is nullable.

When I try the following below, it works well:

create table #temp (num int);
insert into #temp (num) values (null);

However, I have the following code in asp.net 4.5, which is not working:

SQLDatabase sql = new SQLDatabase();
SQLParamList sqlParams = new SQLParamList();

int? Q3 = null;
Q3 = CheckNumericContent(r, dr, "Q3"); //Q3 empty cell for the row in csv.
sqlParams.Add("@Q3", Q3); //Q3 is still null here.
sql.ExecStoredProcedureDataTable("[spInsert_Data]", sqlParams);

The code of CheckNumericContent function is here:

private int? CheckNumericContent(int r, DataRow dr, string columnname)
{
    int ret = -1;
    if (dr[columnname] != null && !String.IsNullOrEmpty(dr[columnname].ToString()))
    {
        try
         {
             if (!Int32.TryParse(dr[columnname].ToString(), out ret))
             {
                 ErrorMessage = ErrorMessage + string.Format("Row {0}: {1) is not numeric.\n", r.ToString(), columnname);
             }
         }
         catch (Exception ex)
         {
            ErrorMessage = ErrorMessage + "some error: "+ex.ToString(); 
         }
          return ret;
    }
    else
    {
        return null;
    }          
}

spInsert_Data stored Procedure is (Sql Server 2014):

CREATE PROCEDURE spInsert_Data
   @Q3 int
AS BEGIN
   insert into tblMyData (Q3) values (@Q3);
END

This code works well unless Q3 is null. When Q3 is null, which means there is no data for Q3 for a row in the CSV, it is giving the error:

Procedure or function 'spInsert_Data' expects parameter '@Q3', which was not supplied.

Picture of the error is on the below. What is wrong and how can I fix it? Any help would be appreciated!

Solution 1

You could use DBNull.Value.

int? Q3 = null;
Q3 = CheckNumericContent(r, dr, "Q3"); //Q3 empty cell for the row in csv.
if (Q3.HasValue)
{
    sqlParams.Add("@Q3", Q3);
}
else
{
    sqlParams.Add("@Q3", DBNull.Value)
}
sql.ExecStoredProcedureDataTable("[spInsert_Data]", sqlParams);

DBNull.Value can be used to set a null value in the database. From the docs:

If a database field has missing data, you can use the DBNull.Value property to explicitly assign a DBNull object value to the field.

Solution 2

Just set it to null as the default. But you'll want to not insert when that value is null (at least this is what i'd expect)... so add an IF.

CREATE PROCEDURE spInsert_Data (@Q3 int = null)
AS BEGIN
IF (@Q3 is not null)
BEGIN
   insert into tblMyData (Q3) values (@Q3);
END
END

Comments

[Eray Balkanli]

There are other columns in the original, so even though Q3 is null I should add the row. I'll try your solution

[Eray Balkanli]

Your solution works, just I don't want to update the SP since it is also used by other pages. Thanks a lot for sharing this, appreciated.

[S3S]

No sweat! @ErayBalkanli

[Jig12]

if ( drpselected.SelectedValue == "") { values.add(DBNull.Value); } else { values.add(drpselected.SelectedValue); }