can not update table . error is : Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Question

0.00/5 (No votes)

See more:

I am trying to update table . the error is :Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

C#

//convert image to byte
            MemoryStream ms = new MemoryStream();
            picperson.Image.Save(ms, picperson.Image.RawFormat);
            byte [] arrImage = ms.GetBuffer();
            ms.Close();

SQL

objconnection.Open();
                objcommand.CommandText =
                    "UPDATE profile SET  picture='"+arrImage+
                    "' WHERE id=" + txtid.Text;

in insert sql command i have not this error but when I use update command taken this error?
I want change image in database table where picture field type is varbinary.I picture field type is not image type. arrImage convert to byte then save to picture field with varbinary type.
I say where I have not problem with Insert sql command but update error.

Posted 29-Mar-13 8:51am

zxo004

Updated 29-Mar-13 9:01am

v2

Add a Solution

2 solutions

Solution 1

http://stackoverflow.com/questions/11411854/implicit-conversion-from-data-type-nvarchar-to-varbinarymax-is-not-allowed[^]

Posted 29-Mar-13 9:02am

Vasudevan Deepak Kumar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-03-29T09:09:00

Solution 2

That won't work. Not as an INSERT, or as an UPDATE.
You might think it does, but...have a look at this: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^] - it explains the basics of what is happening.

Now think: What does ToString return for a byte array?
Answer: "System.Byte[]"
And when you concatenate strings to form an SQL command, you end up with:

SQL

UPDATE profile SET  picture='System.Byte[]' WHERE id=888

So, instead, use parametrized queries:

C#

objcommand.CommandText = "UPDATE profile SET picture=@IM WHERE id=@ID";
objcommand.Parameters.AddWithValue("@ID", txtid.Text);
objcommand.Parameters.AddWithValue("@IM", arrImage);

This also has the advantage that your users can't damage or destroy your database by typing in the textbox and causing an SQL injection attack!

Posted 29-Mar-13 9:09am

OriginalGriff

Comments

zxo004 29-Mar-13 15:26pm

thanks. this works.

OriginalGriff 29-Mar-13 16:10pm

You're welcome!
But do remember that you should use parametrized queries by default - SQL Injection is no joke! :)

Kenneth Haugland 29-Mar-13 16:21pm

Just out of sheer curiosity how does one actually do that. I assume that one has to sniff at the connections being sendt and than send a try and error to the SQL server? It seems to be a rather heavy job?

I always use parameterized queries my self, as I have from the start (originally out of practical views) thought they were easyer to program, meaning easier to handle.

OriginalGriff 29-Mar-13 17:00pm

Unfortunately, it's far, far too simple.
s = "SELECT * FROM customers WHERE id ='" + tb.Text + "'";
With the text box containing
1';DROP TABLE customers;--
Will do it.

Kenneth Haugland 29-Mar-13 17:16pm

Thats nasty.

OriginalGriff 29-Mar-13 17:38pm

Very.
http://xkcd.com/327/

Kenneth Haugland 29-Mar-13 17:55pm

:laugh: Would be better if a secreat agent were called that :)

Maciej Los 29-Mar-13 18:58pm

My virtual 5!

Maciej Los 29-Mar-13 18:58pm

+5!