Issue in updating a sql server `varbinary` column using C#

Question

When I code an insert, everything works great. For example:

dataCommand.CommandText = "use mydb; INSERT INTO mytable VALUES(@binaryvalue);
dataCommand.Parameters.Add("@binaryvalue", SqlDbType.VarBinary, 256).Value = mycard;
dataCommand.ExecuteNonQuery();

However, if I try to update, it won't work. I used the .WRITE method, but it won't do:

dataCommand.CommandText = "use mydb; update mytable set mycolumn .Write(@binaryvalue, 0, NULL) where myid = " + wid;
dataCommand.Parameters.Add("@binaryvalue", SqlDbType.VarBinary, 256).Value = mycard;
dataCommand.ExecuteNonQuery();

How can I perform an update instead of deleting/inserting?

Thanks for any response!

Stop using the use mydb; inside your SQL script - by means of the connection string, you're already defining what databas you're running in.... — marc_s
– marc_s, Commented Aug 4, 2011 at 19:36

agent-j · Accepted Answer · 2011-08-04 19:04:08Z

2

Maybe the problem is the NULL:

.WRITE (expression,@Offset,@Length)         (reference)
.Write(@binaryvalue, 0, NULL)               (your code)

Try putting the length of your content instead of NULL.

@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length is bigint and cannot be a negative number. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.

More info here: http://msdn.microsoft.com/en-us/library/ms177523.aspx

answered Aug 4, 2011 at 19:04

agent-j

28k5 gold badges55 silver badges81 bronze badges

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

2 Comments

Corey Fleig Over a year ago

I changed the length, and still get the same error message: "Cannot call methods on varbinary." According to MSDN, my syntax is perfect, and MSDN says you need to use ".WRITE" for varbinary updating, so I don't understand why it won't work! Any other ideas?

agent-j Over a year ago

I was assuming you just typed in your example code incorrectly, but your update... where myId=... looks at the identity, but in your example insert doesn't specify the id. Do you have a primary key on the table?

Amar Palsapure · Accepted Answer · 2012-02-13 07:29:26Z

0

What's the exact type of the binary column in the table? Is it VARBINARY(MAX)? If not please try it and see if it works. According to the documentation:

"Use the .WRITE (expression, @Offset, @Length) clause to perform a partial or full update of varchar(max), nvarchar(max), and varbinary(max) data types"

and

"To achieve the same functionality of .WRITE with other character or binary data types, use the STUFF (Transact-SQL)."

edited Feb 13, 2012 at 7:29

Amar Palsapure

9,6561 gold badge29 silver badges46 bronze badges

answered Aug 4, 2011 at 23:55

Szyper

1

Collectives™ on Stack Overflow

Issue in updating a sql server `varbinary` column using C#

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related