Operand type clash: nvarchar is incompatible with image

Question

I'm using a SQL Server 2008 stored procedure to create a new record with this syntax:

cmd.Parameters.Add("@photo", DBNull.Value)
cmd.ExecuteNonQuery()

but the result is a:

Operand type clash: nvarchar is incompatible with image 

Photo is not the only parameter but is the only image one, I am not passing a nvarchar but a null value, am I missing something?

Answer 1

If you pass in DBNull.Value as the value, ADO.NET can't figure out what type the parameter should be. If you specify a string, or an integer value, the type of the SQL parameter can be derived from the value provided - but what type should DBNull.Value be turned into??

When passing in a NULL value, you need to specify that SqlDbType yourself, explicitly:

Dim photoParam As New SqlParameter("@photo", SqlDbType.Image)
photoParam.Value = DBNull.Value
cmd.Parameters.Add(photoParam)

cmd.ExecuteNonQuery()

That should work, I hope!

Answer 2

perfect answer by marc_s. I can select that as right answer cause i dont hve a login. thanks man

Answer 3

Dim photo_NULL As New SqlTypes.SqlBytes .Pararameters.AddWithValue("@photo", IIf(IsNothing(Photo), photo_NULL, Photo)) .CommandType = CommandType.StoredProcedure F_return = (.ExecuteNonQuery > 0)