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!

Possible duplicate of [Stored procedure or function expects parameter which is not supplied](https://stackoverflow.com/questions/19703653/stored-procedure-or-function-expects-parameter-which-is-not-supplied) — VDWWD, Jan 17 '19 at 22:11

haldo · Accepted Answer · 2019-01-17T23:43:50.093

8

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.

edited Jan 17 '19 at 23:43

answered Jan 17 '19 at 22:11

haldo

14,512
5
46
52

if ( drpselected.SelectedValue == "") { values.add(DBNull.Value); } else { values.add(drpselected.SelectedValue); } – Jig12 Oct 03 '20 at 04:37
Syntactically much simpler: sqlParams.Add("@Q3", (object)Q3 ?? DBNull.Value); – Eliot Gillum Oct 15 '22 at 06:34

S3S · Answer 2 · 2019-01-17T22:07:46.650

1

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

edited Jan 17 '19 at 22:07

answered Jan 17 '19 at 22:05

S3S

24,809
5
26
45

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 Jan 17 '19 at 22:06
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. – Eray Balkanli Jan 17 '19 at 22:25
No sweat! @ErayBalkanli – S3S Jan 17 '19 at 22:26

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

2 Answers2