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), @attchmentMimeType
nvarchar(4000), @' expects a parameter @attachment, which was not supplied.

when I do the following.

string update = "UPDATE ResponseDetails SET attachment=@attachment, " +
"attachmentFileName=@attachmentFileName,
attachmentMimeType=@attachmentMimeType, " +
"lastModBy=@lastModBy, lastModUtcDate=@lastModUtcDate " +
"WHERE responseSessionId=@responseSessionId AND
questionId=@questionId";

SqlCommand sqlCmd = new SqlCommand(update, sqlConn);
sqlCmd.Parameters.Add("@attachment", DBNull.Value);

Or the error

Operand type clash: nvarchar is incompatible with image

when I do the following

string update = "UPDATE ResponseDetails SET attachment=@attachment, " +
"attachmentFileName=@attachmentFileName,
attachmentMimeType=@attachmentMimeType, " +
"lastModBy=@lastModBy, lastModUtcDate=@lastModUtcDate " +
"WHERE responseSessionId=@responseSessionId AND
questionId=@questionId";

SqlCommand sqlCmd = new SqlCommand(update, sqlConn);
sqlCmd.Parameters.Add("@attachment", 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*****@stgeorgeconsulting.com> wrote in message
news:es**************@TK2MSFTNGP15.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), @attchmentMimeType
nvarchar(4000), @' expects a parameter @attachment, which was not supplied.
when I do the following.

string update = "UPDATE ResponseDetails SET attachment=@attachment, " +
"attachmentFileName=@attachmentFileName,
attachmentMimeType=@attachmentMimeType, " +
"lastModBy=@lastModBy, lastModUtcDate=@lastModUtcDate " +
"WHERE responseSessionId=@responseSessionId AND
questionId=@questionId";

SqlCommand sqlCmd = new SqlCommand(update, sqlConn);
sqlCmd.Parameters.Add("@attachment", DBNull.Value);

Or the error

Operand type clash: nvarchar is incompatible with image

when I do the following

string update = "UPDATE ResponseDetails SET attachment=@attachment, " +
"attachmentFileName=@attachmentFileName,
attachmentMimeType=@attachmentMimeType, " +
"lastModBy=@lastModBy, lastModUtcDate=@lastModUtcDate " +
"WHERE responseSessionId=@responseSessionId AND
questionId=@questionId";

SqlCommand sqlCmd = new SqlCommand(update, sqlConn);
sqlCmd.Parameters.Add("@attachment", 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, " +
"attachmentFileName=null,
attachmentMimeType=null, " +
"lastModBy=@lastModBy, lastModUtcDate=@lastModUtcDate " +
"WHERE responseSessionId=@responseSessionId AND
questionId=@questionId";

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

-Ryan.