10

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ferer
  • 101
  • 1
  • 1
  • 3

2 Answers2

14

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!

Update: same thing in C# would be:

SqlParameter photoParam = new SqlParameter("@photo", SqlDbType.Image);
photoParam.Value = DBNull.Value;
cmd.Parameters.Add(photoParam);

cmd.ExecuteNonQuery();

There's a great, free, very useful VB.NET-to-C# converter out there - use it!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    c# could also be written in 1 line: `cmd.Parameters.Add(new SqlParameter("photo", SqlDbType.Image) { Value = DBNull.Value });` – Johann Jan 22 '13 at 15:17
0
 Dim photo_NULL As New SqlTypes.SqlBytes
.Pararameters.AddWithValue("@photo", IIf(IsNothing(Photo), photo_NULL, Photo))
.CommandType = CommandType.StoredProcedure
F_return = (.ExecuteNonQuery > 0)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
revean
  • 1
  • 1
    If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jan 17 '11 at 19:48