C # - Insert null into SQL Compact Edition table

Question

I am trying to insert some data into an SQL Compact Edition table using a parameter. My table allows me to enter null, but when I run my code below, I cannot get it to work, because one of these parameters will sometimes be empty, and I would like to insert zero in this column when the parameter is null.

How can i fix this?

string strConn = Properties.Settings.Default.SqlConnectionString; using (SqlCeConnection conn = new SqlCeConnection(strConn)) { conn.Open(); using (SqlCeCommand cmd = new SqlCeCommand("insert into table(ID, Name, Adress) values (@parm1, @parm2, @param3)", conn)) { cmd.Parameters.AddWithValue("@parm1", ID); cmd.Parameters.AddWithValue("@parm2", Name); cmd.Parameters.AddWithValue("@parm3", Adress); cmd.ExecuteNonQuery(); } } 

Answer 1

I think you can pass DBNull.Value as your value when it is null:

 cmd.Parameters.AddWithValue("@parm2", (Name as object) ?? DBNull.Value); 

Answer 2

Check if your == null parameter is specified, and if so, add DBNull instead or omit the parameter.

The totals of .NET and null SQL values ​​are different.

Answer 3

Perhaps the source of this problem lies in the SQL query string itself. You declare three parameters:

@parm1 , @parm2 and - focus on writing - @param3

And you installed: @parm1 , @parm1 and - focus on spelling - @parm3

My assumption is that the reason for this may be the naming of integers @parm3 vs. @param3 .

Answer 4

I use the following extension method to set parameters to zero.

 public static class SqlExtensions { public static void AddNullableParameterWithValue(this SqlCeCommand cmd, string name, object value) { if (null != value) cmd.Parameters.AddWithValue(name, value); else cmd.Parameters.AddWithValue(name, DBNull.Value); } }