468,505 Members | 1,694 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,505 developers. It's quick & easy.

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

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
Nov 18 '05 #1
2 2618
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

Nov 18 '05 #2
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.
Nov 18 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by PearCZ | last post: by
3 posts views Thread by Josema | last post: by
3 posts views Thread by Simon Harris | last post: by
6 posts views Thread by | last post: by
15 posts views Thread by mleaver | last post: by
62 posts views Thread by ivan.leben | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.