Insert a null value into an int column in SQL Server

Question

public bool insertRowIntoApplicant(int AnsId, string Name)
{
    string CS = Utilities.GetConnString();

    using (SqlConnection con = new SqlConnection(CS))
    {
        SqlCommand cmd = new SqlCommand("Insert Into ApplicantTable (AnswerableId, Name) values (@AnsId, @Name);

        SqlParameter paraAnsId = new SqlParameter()
                    {
                        ParameterName ="@AnsId",
                        SqlDbType = System.Data.SqlDbType.Int,
                        Direction = System.Data.ParameterDirection.Input,
                        Value = AnsId
                    };
        cmd.Parameters.Add(paraAnsId);
        cmd.Parameters.Add(paraName);
    }
}

I get the value from a hidden field

int getAnswerabelValue = Convert.ToInt32(hfAnswerableID.Value);

if (dal_Obj1.insertRowIntoApplicant(getAnswerabelValue, Name)
{
}

My question is that getAnswerabelValue sometimes can be null, so how to change my code?

Does the database column accept `null`? Have you tried using `int? AnsId`? What is the type of `hfAnswerableID.Value`? — mjwills, Jul 04 '18 at 13:48
you want a nullable int then... declare as int? getAnswerableValue — Fuzzybear, Jul 04 '18 at 13:49
Do you mean that `hfAnswerableID` can sometimes be null? Because in this code `getAnswerabelValue` most certainly can not be null. What is `hfAnswerableID`? Where does it come from? — David, Jul 04 '18 at 13:50
@jawed: If, in your method, `AnsId` needs to allow for null values then make its type `int?` (shorthand for `Nullable`) in the method signature. Then you can pass it a nullable integer. — David, Jul 04 '18 at 13:54
Maybe not quite a duplicate [here](https://stackoverflow.com/questions/9801649/inserting-null-to-sql-db-from-c-sharp-dbcommand) but see how to use `DBNull.Value` also. Though I agree, in this case it’s simpler to just use `int?`. — blins, Jul 04 '18 at 14:00

score 6 · Accepted Answer · answered Jul 04 '18 at 13:55

The column can be null? Then the variable should be Nullable<int> too.

int? ansIdOrNull = null;
int ansId;
if(int.TryParse(hfAnswerableID.Value, out ansId))
    ansIdOrNull = ansId;

// pass ansIdOrNull to the method...

You have to use DBNull.Value in case it is null:

public bool insertRowIntoApplicant(int? ansId, string Name)
{
    string CS = Utilities.GetConnString();
    using (SqlConnection con = new SqlConnection(CS))
    {
       SqlCommand cmd = new SqlCommand("Insert Into ApplicantTable(AnswerableId,Name) values(@AnsId,@Name);
       SqlParameter paraAnsId = new SqlParameter()
        {
            ParameterName ="@AnsId",
            SqlDbType = System.Data.SqlDbType.Int,
            Direction = System.Data.ParameterDirection.Input,
            Value = (object)ansId ?? DBNull.Value
        };
        // initialize paraName
        cmd.Parameters.Add(paraAnsId);
        cmd.Parameters.Add(paraName);
    }
}

Why should go extra parsing while you can actually use string to post it into the SQL table? Like `cmd.Parameters.AddWithValue('@getAnswerabelValue', string.IsNullOrEmpty(getAnswerabelValue) ? DBNull.Value : getAnswerabelValue)` — Jamshaid K., Jul 04 '18 at 17:12
Because you want type safety, you want to control what the database stores and because you want to store null values. The method should not need to take the value somewhere but it should get it as parameter. The only valid type is an int? — Tim Schmelter, Jul 04 '18 at 17:39

Fuzzybear · Answer 2 · 2018-07-04T13:56:03.143

0

If your db column accepts nulls then you will want to declare your int as a nullable int

int? getAnswerabelValue = hfAnswerableID;

edited Jul 04 '18 at 13:56

answered Jul 04 '18 at 13:50

Fuzzybear

1,388
2
25
42

If `hfAnswerableID` is already an `int?` then you'd also want to drop the `.Value`, and at that point there's no need for `getAnswerabelValue` in the first place. – David Jul 04 '18 at 13:52

Insert a null value into an int column in SQL Server

2 Answers2