Why can't I insert DBNull.Value into a nullable image-field in sql server 2005?

Question

Why can't I insert DBNull.Value into a nullable image-field in sql server 2005?

I tried this code:

SqlConnection conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=PrescriptionTrackingSystem;Integrated Security=True");

            conn.Open();

            SqlTransaction transaction = conn.BeginTransaction();

            SqlCommand command = new SqlCommand(@"INSERT INTO Customer(
                               ID
                              ,Name
                              ,TelephoneNumber
                              ,DateOfBirth,
                               InsuranceProvider,
                               PolicyNumber,Photo)
                          VALUES(
                               @ID
                              ,@Name
                              ,@TelephoneNumber
                              ,@DateOfBirth,
                               @InsuranceProvider,
                               @PolicyNumber,
                               @Photo)", conn);

            command.Transaction = transaction;

            command.Parameters.AddWithValue("@ID", 1000);
            command.Parameters.AddWithValue("@Name", item.Name);
            command.Parameters.AddWithValue("@TelephoneNumber", item.TelephoneNumber);
            if (item.DateOfBirth != null)
            {
                command.Parameters.AddWithValue("@DateOfBirth", item.DateOfBirth);
            }
            else
            {
                command.Parameters.AddWithValue("@DateOfBirth", DBNull.Value);
            }
            command.Parameters.AddWithValue("@InsuranceProvider", item.InsuranceProvider);
            command.Parameters.AddWithValue("@PolicyNumber", item.PolicyNumber);

            if (item.Photo != null)
            {
                command.Parameters.AddWithValue("@Photo", item.Photo);
            }
            else
            {
                command.Parameters.AddWithValue("@Photo", DBNull.Value);
            }

            int count = command.ExecuteNonQuery();

            transaction.Commit();

            conn.Close();

item is of type Customer.

public class Customer
{        
    public string Name { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public string TelephoneNumber { get; set; }
    public string InsuranceProvider { get; set; }
    public int? PolicyNumber { get; set; }
    public byte [] Photo { get; set; }
}

When inserting I get this exception:

Operand type clash: nvarchar is incompatible with image

Why DBNull.Value has got problems only with image? Why not with datetime?

What about the stored proc, what is the data type? – Kane Jan 24 '12 at 10:42 — Kane, Jan 24 '12 at 10:42

score 4 · Accepted Answer · answered Jun 20 '12 at 14:39

4

You could try this:

command.Parameters.Add("@Photo", SqlDbType.Image).Value = DBNull.Value;

answered Jun 20 '12 at 14:39

Sumi

56
3

score 1 · Answer 2 · answered Jan 24 '12 at 10:44

1

I think this could be to do with the fact that you are not explicitly defining the SqlParameter.SqlDbType and think it will be assuming NVARCHAR. Instead of using AddWithValue, trying adding a new SqlParameter and setting SqlDbType explicitly to SqlDbType.Image.

Here's the MSDN ref which states the default is NVARCHAR.

answered Jan 24 '12 at 10:44

AdaTheDev

142,592
28
206
200

Yes. But I have posted my code and you can see DateTime is working correctly. – user366312 Jan 24 '12 at 10:47
A NULL NVARCHAR can be cast to a DATETIME without problem. However, a NULL NVARCHAR cannot be cast to an IMAGE as you get a conversion error. Give it a try, setting the SqlDbType to Image for that parameter. – AdaTheDev Jan 24 '12 at 10:52

score 1 · Answer 3 · answered Jan 24 '12 at 10:48

1

You need to explicitly specify the parameter's type as SqlDbType.Image

answered Jan 24 '12 at 10:48

Oleg Dok

21,109
4
45
54

Yes. But I have posted my code and you can see DateTime is working correctly. – user366312 Jan 24 '12 at 10:49
Yes, thats because there EXISTS implicit conversions between nvarchar to datetime and it treats NULL values as it should. And there is no any implicit conversions from nvarchar to Image, even if value is NULL – Oleg Dok Jan 24 '12 at 10:51
Is the conversion associated with SqlServer2005 or SqlCommand object? – user366312 Jan 24 '12 at 10:55

score 0 · Answer 4 · edited May 09 '15 at 13:52

0

I solved this like this:

If (Photo.Equals(DBNull.Value)) Then
    cmd.Parameters.Add("Photo", SqlDbType.Image).Value = DBNull.Value
Else
    cmd.Parameters.AddWithValue("Photo", Photo)
End If

edited May 09 '15 at 13:52

Soma

861
2
17
32

answered May 09 '15 at 13:11

user4882095

1

Why can't I insert DBNull.Value into a nullable image-field in sql server 2005?

4 Answers4