saving bytearray to VarBinary column in SQL Server inserts only one byte

Question

I have a following problem - I'm trying to save byte[] to database and I just figured out that it works for one byte only.

I have number of floats that I convert to byte[] and use that as a parameter:

param = new SqlParameter(name, type, ((byte[])value).Length);

type is VarBinary, value is the byte array.

I add that parameter to my SqlCommand and just before it gets executed the whole byte array "sits" in that parameter and _msize of that parameter is correct (20 for 20 bytes I assume is correct). My SQL Server shows me only 1 byte saved, also trying to retrieve it back I'm getting only one byte. My column is VarBinary(200).

Any suggestions?

Answer 1

If you're using a stored procedure, and you've defined your parameter as just varbinary - you'll get a default length of 1 byte as per MSDN documentation:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

So if you have a stored procedure with

@MyData VARBINARY

then you have just one single byte - you need to change that to something like

@MyData VARBINARY(200) 

or something else that's suitable for you