Null values are not inserted in Allow Nulls colums in C#

Question

My query returns false when some values are null, but my table allows Null values.

What did I do wrong?

cmd.CommandText ="Insert into BusinessTbl(BName,BAddress,BEmail,BMobile,BPhone,Cat_Id)" +
    "values(@bname,@baddress,@bemail,@bmobile,@bphone,@catid)";

cmd.Parameters.AddWithValue("@bname", b.BusinessName);
cmd.Parameters.AddWithValue("@name", b.BusinessAddress);
cmd.Parameters.AddWithValue("@bemail", b.BusinessEmail);
cmd.Parameters.AddWithValue("@bmobile", b.BusinessMobile);
cmd.Parameters.AddWithValue("@bphone", b.BusinessPhone);
cmd.Parameters.AddWithValue("@catid", b.ddlbcategory);

con.ExecuteNonQuery(cmd);

My Table

A .Net null is not the same as a database null, if a value is null in your code you need to pass DBNull.Value. — Alex K.
– Alex K., Commented Apr 17, 2018 at 10:58
Besides the issue at hand, I think you also mean to use "@baddress" instead of "@name". — Peter B
– Peter B, Commented Apr 17, 2018 at 11:09

Marc Gravell · Accepted Answer · 2018-04-17 11:01:56Z

3

This is a vexing feature of ADO.NET parameters; basically:

cmd.Parameters.AddWithValue("@bname", ((object)b.BusinessName) ?? DBNull.Value);
cmd.Parameters.AddWithValue("@name", ((object)b.BusinessAddress) ?? DBNull.Value);
// etc

should fix you. If the .Value is null, the parameter isn't sent - it needs to be DBNull.Value. Alternatively, a tool like "Dapper" helps avoid this pain:

con.Execute(@"Insert into BusinessTbl(BName,BAddress,BEmail,BMobile,BPhone,Cat_Id)
              values(@bname,@baddress,@bemail,@bmobile,@bphone,@catid)",
   new { bname = b.BusinessName, ... , catid = b.ddlbcategory });

(which will parameterize correctly, including the nulls)

answered Apr 17, 2018 at 11:01

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

1 Comment

Muhammad Saqib Over a year ago

Thanks Marc Gravell

Collectives™ on Stack Overflow

Null values are not inserted in Allow Nulls colums in C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related