CODEWITHSUNDEEP
c#sql-server
Corey Fleig
Corey Fleig

Reputation: 31

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

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!

Upvotes: 0

Views: 2105

Answers (2)

Szyper
Szyper

Reputation: 1

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)."

Upvotes: 0

agent-j
agent-j

Reputation: 27923

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

Upvotes: 2

Related Questions