Insert statement won't insert Null value

Question

I'm trying to insert a null value into my database from C# like this:

SqlCommand command = new SqlCommand("INSERT INTO Employee
VALUES ('" + employeeID.Text + "','" + name.Text + "','" + age.Text
        + "','" + phone.Text + "','" + DBNull.Value + "')", connection);

DBNull.Value is where a date can be but I would like it to be equal to null but it seems to put in a default date, 1900 something...

Answer 1

Change to:

SqlCommand command = new SqlCommand("INSERT INTO Employee VALUES ('" + employeeID.Text + "','" + name.Text + "','" + age.Text + "','" + phone.Text + "',null)", connection);

DBNull.Value.ToString() returns empty string, but you want null instead.

However this way of building your query can lead to issues. For example if one of your strings contain a quote ' the resulting query will throw error. A better way is to use parameters and set on the SqlCommand object:

SqlCommand command = new SqlCommand("INSERT INTO Employee VALUES (@empId,@name,@age,@phone,null)", connection);
command.Parameters.Add(new SqlParameter("@empId", employeeId.Text));
command.Parameters.Add(new SqlParameter("@name", name.Text));
command.Parameters.Add(new SqlParameter("@age", age.Text));
command.Parameters.Add(new SqlParameter("@phone", phone.Text));