CODEWITHSUNDEEP
c#asp.netsql
Steve
Steve

Reputation: 123

Insert statement won't insert Null value

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...

Upvotes: 6

Views: 18434

Answers (6)

codepreneur
codepreneur

Reputation: 21

try it like this.

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

Upvotes: 0

ojlovecd
ojlovecd

Reputation: 4902

if you output "'" + DBNull.Value + "'" , you will find that it's '' , which means you insert an empty string instead of null into the DB. So, you just write null:

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

Upvotes: 1

KV Prajapati
KV Prajapati

Reputation: 94653

Use Parameters.

SqlCommand command = new SqlCommand("INSERT INTO Employee VALUES 
            (@employeeID,@name,@age,@phone,@bdate)",connection);
....
command.Parameters.AddWithValue("@bdate",DBNull.Value);
//or
command.Parameters.Add("@bdate",System.Data.SqlDbType.DateTime).Value=DBNull.Value;

Or try this,

 SqlCommand command = new SqlCommand("INSERT INTO Employee 
         (employeeID,name,age,phone) VALUES 
                (@employeeID,@name,@age,@phone)",connection);

Upvotes: 11

Petar Ivanov
Petar Ivanov

Reputation: 93090

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));

Upvotes: 11

user596075
user596075

Reputation:

Try this:

SqlCommand command = new SqlCommand();
command.ComandText = "insert into employee values(@employeeId, @name, @age, @phone, @someNullVal)";
command.Parameters.AddWithValue("@employeedId", employeedID.Text);
// all your other parameters
command.Parameters.AddWithValue("@someNullVal", DBNull.Value);

This solves two problems. You explicit problem (with inserting a NULL value into the table), and SQL Injection potential.

Upvotes: 1

competent_tech
competent_tech

Reputation: 44971

Change DBNull.Value to the literal null for dynamic SQL:

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

Upvotes: 1

Related Questions