Error while storing bytes in database ,Implicit conversion from data type varchar to varbinary(max) is not allowed

Question

public void InsertMails(string From, string To, string Date, string Content, string AttachmentPath, byte[] CCD)
{
   ClassLibrary.ConnectionClass.CurrentConnection.ExecuteNonReader("Insert into RecieveDirectMails([From],[To],Date,[Content],AttachmentPath,CreationDate,LastUpdatedDate,IsReviewed,LastUpdatedBy,IsDeleted,IsLocked,LockedBy,CCD) values ('" + From + "','" + To + "','" + Date + "','" + Content + "','" + AttachmentPath + "','" + DateTime.Now + "','" + DateTime.Now + "','" + "0" + "','" + "0" + "','" + "0" + "','" + "0" + "','" + "0" + "','" + CCD+ "')");
}

I am storing XML file bytes into Database but the error occurred.

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

Where I am doing it wrong can any one help me out .

In the database the CCD column is of datatype varbinary(MAX)

SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Dec 3, 2013 at 19:55
Simple question is that why i am facing this error "Implicit conversion from data type varchar to varbinary(max) is not allowed. Use the CONVERT function to run this query." — Umair Aslam
– Umair Aslam, Commented Dec 3, 2013 at 19:59
Well, your column is a VARBINARY so it expects binary data as the name implies - but your last values provided is in double quotes ( "........" ) so therefore it's a string and those cannot be converted automatically. Either you have the wrong datatype and it really should be a string format (e.g. VARCHAR(MAX)), or then you need to provide the data as a binary blob of data and not as a string in your INSERT statement — marc_s
– marc_s, Commented Dec 3, 2013 at 20:00

Dai · Accepted Answer · 2013-12-03 20:03:48Z

1

Binary values in T-SQL are represented by hexadecimal-encoded literals prefixed with 0x, like so:

INSERT INTO Foo ( Col ) VALUES ( 0xDEADBEEF )

However if you're doing it from code please use parameterised queries with SqlParameter to avoid injection attacks:

SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = "INSERT INTO Foo ( Col ) VALUES ( @col )";
cmd.Parameters.Add("@col", SqlDbType.VarBinary).Value = new Byte[] { 0xDE, 0xAD, 0xBE, 0xEF };
cmd.ExecuteNonQuery();

answered Dec 3, 2013 at 20:03

Dai

158k31 gold badges314 silver badges438 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Umair Aslam Over a year ago

Is this possible to do as the same way as i posted a question ? means I wanted to simply store my Bytes into Database

Collectives™ on Stack Overflow

Error while storing bytes in database ,Implicit conversion from data type varchar to varbinary(max) is not allowed

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related