Insert NULL values in SQL database

Question

Hi everybody,

I wrote the following method:

public void insert()
    {
      if (_ID.Trim().Length == 0)
        throw new Exception("Model not specified.");

      SqlConnection oConnection = new SqlConnection("Data Source=localhost;Initial Catalog=XXX;Integrated Security=SSPI");
      oConnection.Open();

      string str = "";
      str += "INSERT INTO Card(ID,G_T,ODept_T,C,G,TT,SNG,ON,Img) ";
      str += "VALUES (@ID,@GT,@ODT,@C,@G,@TT,@SNG,@ON,@Img)";
      SqlCommand oCommand = new SqlCommand(str, oConnection);
      oCommand.CommandType = CommandType.Text;
      oCommand.Parameters.Add(new SqlParameter("@ID", _ID));
      oCommand.Parameters.Add(new SqlParameter("@GT", _GT));
      oCommand.Parameters.Add(new SqlParameter("@ODT", _ODT));
      oCommand.Parameters.Add(new SqlParameter("@C", _C));
      oCommand.Parameters.Add(new SqlParameter("@G", _G));
      oCommand.Parameters.Add(new SqlParameter("@TT", _TT));
      oCommand.Parameters.Add(new SqlParameter("@SNG", _SNG));
      oCommand.Parameters.Add(new SqlParameter("@ON", _ON));
      oCommand.Parameters.Add(new SqlParameter("@Img", _Img));

      oCommand.ExecuteNonQuery();

      oCommand.Dispose();
      oConnection.Close();
      oConnection.Dispose();
    }

All the fields but ID have been declared as "Allow Nulls" in SQL Server 2008 and all the local var starting with '_' are string except _Img which is byte[].

When running my project, if i don't provide an image, the system complains about the fact i didn't provide the image and the data aren't saved in the DB.

Any suggestion without using several "if" statements to check the values?

 

Thanks in advance

Answer 1

The problem is, you kind of have to check if the parameters contain a value. If they are null, you should pass

System.DBNull 

 

as a parameter. This is what is used in .net to pass NULL values to a database. You can fix this by using the shortened notation:    oCommand.Parameters.Add(new SqlParameter("@Img", _Img == null ?? System.DBNull));

where _Img == null ?? System.DBNull is the same as:

if(_Img == null)
{
_Img = System.DBNull

}

You can read more about the ??-operator here: http://msdn.microsoft.com/en-us/library/ms173224.aspx 

---

Dimitri C. - Please mark the replies as answers if they help! Thanks.

Answer 2

You need to check whether the value you are inserting is null or not . if yes then use  the System.Data.SqlTypes.SqlString.Null for inserting null values into the columns.

---

Abhinav Galodha

Answer 3

You can do it like:

int someValue = 1;// or string.Empty if there is no value passed to this parameter
if(_GT != String.Empty) //or != ""
   oCommand.Parameters.Add("@GT", SqlDbType.Int _GT).Value = SomeValue;
else
   oCommand.Parameters.Add("@GT", SqlDbType.Int _GT).Value = DbNull.Value;   

---

Mitja

Answer 4

Thanks all for the quick replies.

@Mitja Bonca: i would like to avoid this type of situation

@Dimitri C: i like this solution, if only it would work ;) I inserted what you suggest but VS gives the following error:

"Error 2 'System.DBNull' is a 'type', which is not valid in the given context"

Any suggestion about that, please?

 

Regards

Answer 5

sorry, this will work better:

oCommand.Parameters.Add(

new SqlParameter("@Img", _Img ?? DBNull.Value));

 

---

Dimitri C. - Please mark the replies as answers if they help! Thanks.

Answer 6

Hello,

Use a SELECT statement instead of VALUES()

str += "INSERT INTO Card(ID,G_T,ODept_T,C,G,TT,SNG,ON,Img) ";

str += "SELECT @ID,@GT,@ODT,@C,@G,@TT,@SNG,@ON,@Img";

Adam

---

Ctrl+Z

Answer 7

@Dimitri C: i did some tries but it gives me the same error on byte[] or string:

Error 2 Operator '??' cannot be applied to operands of type 'string' (or byte[]) and 'System.DBNull'

@Adam_Turner: really this would solve my problem?

Answer 8

Honestly, I didn't test it but in theory it shoulod work. A SELECT will produce a null while VALUES is expecting a value

Adam

---

Ctrl+Z

Answer 9

I just tried but this doesn't work :( It produce the same kind of error since _Img is null

Answer 10

There's a difference between null and Nothing.

What is the error?

---

Ctrl+Z

Answer 11

oEx.Message = "The parameterized query '(@ID nvarchar(10),@GT nvarchar(2),@ODeptT' expects the parameter '@Img', which was not supplied."

_Img is null, checked via debug.

Answer 12

Hi,

Actually I always prefer logical null rather then NULL

---

Thanks
Md. Marufuzzaman

---

Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Answer 13

While using SqlParameter, please specify the System.Data.SqlDbType for every parameter. For "Image", please use "System.Data.SqlDbType.Image"

example :

SqlParameter sqlParamter = new SqlParameter("@Img", _Img);
sqlParamter.DbType= System.Data.SqlDbType.Image;

There are other overloaded construtors for SqlParameter which can be used instead of above setter.This should resolve the issue.

Preferably such DBTypes be declared for all parameters as a good practice.