Deleting varbinary(MAX) from SQL Server column

Question

Please read my entire description before you just blow me off and point me at a similar, but different, problem description/solution!!

I have a table (Images) which contains 2 columns -- ID (Identity, primary key) and ImageData (a varbinary(MAX) column).

The table definition is:

USE [ImageDB]
GO

/****** Object:  Table [dbo].[Images]    Script Date: 12/21/2014 11:03:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Images](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ImageData] [varbinary](max) NULL,
 CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

I can successfully retrieve these records by the primary key. I can successfully create these records from uploaded image files.

What I cannot do is remove the contents of the ImageData column from an existing record.

I am trying to use a stored procedure called ClearImage to empty the contents of the ImageData column:

USE [ImageDB]
GO
/****** Object:  StoredProcedure [dbo].[ClearImage]    Script Date: 12/21/2014 11:06:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ClearImage]
(
@id     int,
@image varbinary(max)
)
AS
Begin
UPDATE Images
SET  ImageData = @image 
WHERE ID = @id
END

The code I am executing (the last test) is:

SqlConnection connection = new SqlConnection();
connection.ConnectionString = "Initial Catalog=ImageDB;Data Source=XXXXXXXXXX;Persist Security Info=False;user id=YYYYYY;password=ZZZZZZZZZZ;";
connection.Open();
SqlCommand command = new SqlCommand("ClearImage", connection);
command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = Convert.ToInt32(ClearKey.Text);
command.Parameters.Add("@image", System.Data.SqlDbType.VarBinary).Value = System.DBNull.Value;
command.ExecuteScalar();
connection.Close();
connection.Dispose();

The error I consistently receive (no matter what I try to do): Procedure or function 'ClearImage' expects parameter '@id', which was not supplied.

Please bear in mind that I am experienced enough to ensure that I HAVE supplied the @id parameter (and made certain it is a valid integer key value to a record that exists in the database).

I have read numerous forum entries here (including 18170985 and others) and elsewhere and none can provide a solution to my problem. I have worked on this for hours to no avail.

Does anyone know why it is giving me this obscure (and incorrect) error message? I am running in Visual Studio 2013 and using a SQL Server 2008 database.

I cannot post a screen capture of the command parameters due to restrictions, but please trust me, both parameters are present, correctly named, etc.

Thanks in advance!

Lynn

Answer 1

The exception you are experiencing has two probable causes:

1. Either no parameter was supplied OR
2. Command type was not specified.

Since in your case [1] is ruled out, you have to explicitly specify the command type. So after this line:

SqlCommand command = new SqlCommand("ClearImage", connection);

simply add:

command.CommandType = CommandType.StoredProcedure;