4

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?

Nagaraj S
  • 12,994
  • 6
  • 31
  • 51
pzaj
  • 1,040
  • 1
  • 15
  • 35

1 Answers1

5

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

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
  • @JoelCoehoorn: yes - in C# - but what if that C# code calls a T-SQL stored procedure that uses that parameter *without* a length? C# has the length and all - but inside the T-SQL stored procedure, you get **1 byte** of `varbinary` data .... – marc_s Apr 23 '15 at 13:44
  • Excellent answer that deals with the root of the issue! It could be set to varbinary(max) as well. – usefulBee Sep 10 '15 at 17:11