How to SQL insert if some values are null

Question

I want to insert new records into a SQL server 2008 database. Some of the column values are NULL and so I get an error. This is the SQL query i am using:

INSERT INTO Products VALUES(NEWID(), '" + cur.name + "', '" + cur.type + "');

The error is caused if my values are NULL because it reads: NEWID(), , ;

I could do a bunch of if statements, but there MUST be a more efficient way to do it. Many thanks in advance.

I think you have to do the conditional way to make it work. There is no auto handling of this case — Devesh
– Devesh, Commented Nov 11, 2013 at 10:16
Yes they are nullable. Error is "Incorrect syntax near ','." — abiNerd
– abiNerd, Commented Nov 11, 2013 at 10:16

Kami · Accepted Answer · 2013-11-11 11:56:30Z

4

C# does not appear to convert all null values to their equivalent DBNull values correctly.

Try something like the following

string sql = "INSERT INTO Products 
VALUES(NEWID(), @cName, @cType)";

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@cName", cur.name == null ? (object)DBNull.Value : cur.name);
cmd.Parameters.AddWithValue("@cName", cur.type == null ? (object)DBNull.Value : cur.type);

cmd.Execute();

Your current query is prone to possible sql injection, so using parametrised query is a better solution. It also allows for null value checks.

Alternatively, if you need the value to be empty, then use something like this

cmd.Parameters.AddWithValue("@cName", cur.type == null ? "" : cur.type);

edited Nov 11, 2013 at 11:56

answered Nov 11, 2013 at 10:15

Kami

19.5k4 gold badges53 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abiNerd Over a year ago

This seems to be working well. But the word NULL now shows up in my gridview instead of just being a blank space like before. Is there anything I can do about that/why is it happening?

Kami Over a year ago

@abiNerd This is because you are not putting a null value in the database. Earlier, the value was being converted to ''. If you would like the functionality, use the updated code.

Michael Mairegger · Accepted Answer · 2013-11-11 10:15:14Z

0

If you really want to insert a null into the database use DBNull.Value

answered Nov 11, 2013 at 10:15

Michael Mairegger

7,35133 silver badges44 bronze badges

Collectives™ on Stack Overflow

How to SQL insert if some values are null

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related