How to store 16000 bytes in varbinary

Question

I have a web application which allows e-signature. When I get the signature, it is the exact size(mostly greater than 8000 bytes).But when it goes to the database, it gets truncated to 8000 bytes all the time. When I checked varbinary, it says 'Implicit conversion fails if the byte array is greater than 8,000 bytes. Explicitly set the object when working with byte arrays larger than 8,000 bytes.' How do I do that in the following scenario? I don't want to loose any bytes.

public DataSet InsertInformation(FormInformation info)
{
    SqlCommand command = new SqlCommand("SPInsertSig");
    command.Parameters.Add("@Signature", SqlDbType.VarBinary).Value = info.Signature; 
    DataSet resultDataSet;
    resultDataSet = ExecuteStoreProcedure(command);
    return resultDataSet;
}

When I googled, someone mentioned trying this way but it doesn't work either and truncates the value to 8000 bytes.

SqlParameter param = command.Parameters.Add("@Signature", SqlDbType.VarBinary);
param.Value = info.Signature;
param.Size = -1;

Any suggestions will be really appreciated. Thanks

Update After changing it to Int32.MaxValue, it worked.

use data type VARBINARY(MAX), also have a look at this [`Question`](http://stackoverflow.com/questions/5824620/what-sqldbtype-maps-to-varbinarymax) — M.Ali, Nov 24 '14 at 21:56
My data type is already varbinary(max) and I believe the code I pasted above is truncating the size somehow. — user2908229, Nov 24 '14 at 22:11
What is the .NET data type of info.Signature? I would expect the second code snippet (Size -1) to work with a byte array. — Dan Guzman, Nov 25 '14 at 01:07

score 2 · Accepted Answer · answered Nov 24 '14 at 22:08

2

try to use a new SqlParemeter c'tor:

SqlParameter(String, SqlDbType, Int32)

or in your case:

command.Parameters.Add(new SqlParameter("@Signature", SqlDbType.VarBinary, -1)).Value = info.Signature;

answered Nov 24 '14 at 22:08

ymz

6,602
1
20
39

yackov mandes zaig I tried that and it is still truncating and storing only 8000 when I query the datalength. – user2908229 Nov 24 '14 at 22:18
how about changing -1 to Int32.MaxValue? – ymz Nov 24 '14 at 22:22
@user2908229 how are you mesuring the datalength? – Scott Chamberlain Nov 25 '14 at 13:52
@ymz , after I changed to Int32.MaxValue, it worked. I was measuring the data length with a qurey. SELECT DATALENGTH(SIGNATURE),* FROM customer.dbo.Information WHERE cid='245565' Thank You all – user2908229 Nov 26 '14 at 17:15
great! thanks fot the update! can you please add an **edit** in your post that mark the solution to help other users as well? – ymz Nov 26 '14 at 20:51

UnhandledExcepSean · Answer 2 · 2014-11-25T13:58:41.797

No one can definitively give you an answer of exactly what it is because of the lack of depth in the question, so you really need to follow a checklist to determine the issue. This is the order you should check in my opinion; most common reasons to least common in my experience. My bet in on #4 being the culprit due to the message you are seeing.

Is the field in the table declared as varbinary(max)?
Is the SQL Parameter in code sized correctly (i.e. .size=-1)?
Does the stored procedure being called have the field sized correctly in the parameter declaration?
Is there a CAST or CONVERT call specifying VARBINARY, but not the correct size in the stored procedure?
Is there a trigger on the table that makes a backup copy of the data to another table? If so, is that table sized correctly? Also if there is a trigger, is there a CAST or CONVERT call specifying VARBINARY, but not the correct size in the stored procedure?
Are there any places in the stored procedure where the original value is copied into a different variable and that variable is not sized correctly?
Is there a view that is indexed against the field that might be declared incorrectly?

How to store 16000 bytes in varbinary

2 Answers2