CODEWITHSUNDEEP
sql-servervb.net
Aaron M
Aaron M

Reputation: 2563

Is it possible to update a binary field in SQL Server?

I know how to insert a new row into a table that has a binary column. But is there any way to update the binary column once that row has been added? So far an exhaustive google search has turned up nothing.

MY solution so far is this

  1. Get the current row that I wish to update
  2. delete the row that I wish to update
  3. Create a new row with the information from the row I wish to update plus the new binary

It just doesnt seem very elegant, and I was hoping I could do the same thing with an update statement.

Here is the updated code I am using now for reference

Dim objCommand As New SqlCommand("UPDATE " & Tablename & " SET " _ 
& column & " = Data  WHERE " & criteria)
objCommand.Parameters.Add("@Data", SqlDbType.Image)
objCommand.Parameters("@Data").Value = BinaryData

Upvotes: 0

Views: 7363

Answers (2)

Phill Pafford
Phill Pafford

Reputation: 85318

INSERT BINARY/BYTES

INSERT INTO table_name
(field_name)
VALUES
(4|8|16|32)

UPDATE BINARY/BYTES

UPDATE table_name 
SET field_name=field_name|8|16|32 
WHERE field_name_filter=@this_value

NOTE: Once the Byte flag is set you can't update it to remove it. So you can't use the UPDATE example to remove a byte thats been set to 4 and then want to change it to 8 it just adds the 8 so now you'll have 4|8. Also placing the reverse byte is harder than it seams as well, it's not just UPDATE -4|8.

It's easier to query the results, Delete the record and insert it as a new record with the old data (From the first query) and reformatted with the correct Byte flags.

At least this is my limited experience with this

Upvotes: 0

Tetsujin no Oni
Tetsujin no Oni

Reputation: 7365

Take a look at this article at TechRepublic for technique. You'll need to use the GetBytes / PutBytes methods, much more painful than most ADO.Net code. Also see MSDN.

Upvotes: 1

Related Questions