Deleting binary data (image column) from sql server with ADO.NET

[Ryan Taylor]

Hello.

I have an ASP.NET application where I allow the user to upload attachments.
I upload the attachments as binary data to an image column in sql server. I
have managed to upload the data correctly. However, I need to be able to set
binary field back to NULL if the user wants to delete an attachmnet. What is
the proper way to do this? Thanks in advance.

I currently receive the following error.

Prepared statement '(@attachment nvarchar(4000), @attchmentMimeT ype
nvarchar(4000), @' expects a parameter @attachment, which was not supplied.

when I do the following.

string update = "UPDATE ResponseDetails SET attachment=@att achment, " +
"attachmentFile Name=@attachmen tFileName,
attachmentMimeT ype=@attachment MimeType, " +
"lastModBy=@las tModBy, lastModUtcDate= @lastModUtcDate " +
"WHERE responseSession Id=@responseSes sionId AND
questionId=@que stionId";

SqlCommand sqlCmd = new SqlCommand(upda te, sqlConn);
sqlCmd.Paramete rs.Add("@attach ment", DBNull.Value);

Or the error

Operand type clash: nvarchar is incompatible with image

when I do the following

string update = "UPDATE ResponseDetails SET attachment=@att achment, " +
"attachmentFile Name=@attachmen tFileName,
attachmentMimeT ype=@attachment MimeType, " +
"lastModBy=@las tModBy, lastModUtcDate= @lastModUtcDate " +
"WHERE responseSession Id=@responseSes sionId AND
questionId=@que stionId";

SqlCommand sqlCmd = new SqlCommand(upda te, sqlConn);
sqlCmd.Paramete rs.Add("@attach ment", null);


Ryan Taylor

[Gary Morris]

You might get a better response posting to a SQL newsgroup. If I knew
the answer, I would tell you though.

"Ryan Taylor" <rt*****@stgeor geconsulting.co m> wrote in message
news:es******** ******@TK2MSFTN GP15.phx.gbl...

Hello.

I have an ASP.NET application where I allow the user to upload attachments. I upload the attachments as binary data to an image column in sql server. I have managed to upload the data correctly. However, I need to be able to set binary field back to NULL if the user wants to delete an attachmnet. What is the proper way to do this? Thanks in advance.

I currently receive the following error.

Prepared statement '(@attachment nvarchar(4000), @attchmentMimeT ype
nvarchar(4000), @' expects a parameter @attachment, which was not supplied.
when I do the following.

string update = "UPDATE ResponseDetails SET attachment=@att achment, " +
"attachmentFile Name=@attachmen tFileName,
attachmentMimeT ype=@attachment MimeType, " +
"lastModBy=@las tModBy, lastModUtcDate= @lastModUtcDate " +
"WHERE responseSession Id=@responseSes sionId AND
questionId=@que stionId";

SqlCommand sqlCmd = new SqlCommand(upda te, sqlConn);
sqlCmd.Paramete rs.Add("@attach ment", DBNull.Value);

Or the error

Operand type clash: nvarchar is incompatible with image

when I do the following

string update = "UPDATE ResponseDetails SET attachment=@att achment, " +
"attachmentFile Name=@attachmen tFileName,
attachmentMimeT ype=@attachment MimeType, " +
"lastModBy=@las tModBy, lastModUtcDate= @lastModUtcDate " +
"WHERE responseSession Id=@responseSes sionId AND
questionId=@que stionId";

SqlCommand sqlCmd = new SqlCommand(upda te, sqlConn);
sqlCmd.Paramete rs.Add("@attach ment", null);


Ryan Taylor

[Ryan Taylor]

A co-worker figured this one out. Simple really. The sql string became

string update = "UPDATE ResponseDetails SET attachment=null , " +
"attachmentFile Name=null,
attachmentMimeT ype=null, " +
"lastModBy=@las tModBy, lastModUtcDate= @lastModUtcDate " +
"WHERE responseSession Id=@responseSes sionId AND
questionId=@que stionId";

Hard code the null values instead of trying to use parameterized lists.

-Ryan.