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?

think the answer in this post will do it: stackoverflow.com/questions/1064121/… — Dominic Scanlan
– Dominic Scanlan, Commented Apr 23, 2015 at 13:09

marc_s · Accepted Answer · 2015-04-23 13:11:12Z

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

answered Apr 23, 2015 at 13:11

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

marc_s Over a year ago

@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 ....

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related