String or binary data would be truncated when uploading file in MVC

Question

The file is only 14kb (14,000 bytes). I have read that the varbinary(max) column type (which is what I am using) only supports 8,000 bytes. Is that correct? How can I upload my file into the database?

if (file.ContentLength < (3 * 1048576))
{
    // extract only the fielname
    var fileName = Path.GetFileName(file.FileName);

    using (MemoryStream ms = new MemoryStream())
    {
        file.InputStream.CopyTo(ms);
        byte[] array = ms.GetBuffer();
        adj.resumeFile = array;
        adj.resumeFileContentType = file.ContentType;
    }
}

The error:

String or binary data would be truncated. The statement has been terminated.

Where are you getting the error? Sounds like a SQL error but I don't see the SQL code. — Jason Goemaat, May 09 '13 at 19:06
The error is in MVC on my `db.SubmitChanges();` line of code. — user1477388, May 09 '13 at 19:13
Are you sure it is the file column that is throwing this error and not the content type column? — Tommy, May 09 '13 at 19:25
I think you are exactly right! I didn't think it could possibly be that but it appears that it was. Post as an answer and I will accept it. — user1477388, May 09 '13 at 19:27

score 5 · Accepted Answer · answered May 09 '13 at 19:28

5

Check your other columns that you are inserting into during this process. I would especially check the ContentType column as this will be something like image/jpeg and not simply image or jpeg.

Here is a list of possible content types so that you can create enough space in your ContentType column accordingly.

answered May 09 '13 at 19:28

Tommy

39,592
10
90
121

1

@user1477388 - no problem. I wish SQL was a little more helpful in those truncated messages, maybe with which column was having issues! :) – Tommy May 09 '13 at 19:31

score 1 · Answer 2 · answered May 09 '13 at 19:00

1

varbinary  [ ( n | max) ]  
Variable-length binary data. n can be a value from 1 through 8,000.

max indicates that the maximum storage size is 2^31-1 bytes.

http://msdn.microsoft.com/en-us/library/ms188362.aspx

So that is 2GB.

answered May 09 '13 at 19:00

Pleun

8,856
2
30
50

1

So, if the limit is 2GB and my file is only 14KB, why won't it let me save it? – user1477388 May 09 '13 at 19:02
1

Is your database large enough to hold the file? Enough free space. Do you have autogrowth large enough (read somewhere sometime ago this could influence it) – Pleun May 09 '13 at 19:05
I have the run `sp_spaceused ` command on my SQL server and the "unused" column reads "112KB." Does this mean I only have 112KB of space on my database!? – user1477388 May 09 '13 at 19:16
no, that just means SQL has pre-allocated 112KB of space but hasn't put anything there yet. use sp_helpdb to check the current and max sizes of the files. – Michael Edenfield May 09 '13 at 21:16

score 1 · Answer 3 · edited May 23 '17 at 10:32

1

If you defined your column as VARBINARY(MAX) in the table definition, then you should have up to 2 GB of storage space. If you specified the maximum column size as a number then you can only explicitly ask for up to VARBINARY(8000).

See this question for more details

AFAIK VARBINARY(MAX) only appeared in SQL Server 2008, so if your database pre-dates that version you might need to upgrade it.

edited May 23 '17 at 10:32

Community

1
1

answered May 09 '13 at 19:03

Michael Edenfield

28,070
4
86
117

My table definition has `[resumeFile] [varbinary](max) NULL,` and I am using SQL Server 2008 R2. Is there something else I can try? – user1477388 May 09 '13 at 19:07

score 1 · Answer 4 · answered May 09 '13 at 23:35

1

I know this isn't the answer to your question, but ms.GetBuffer() will get the underlying buffer which probably isn't the exact size of your data. The MemoryStream allocates extra room for writing and you are probably inserting extra bytes from the unused buffer. Here you can see that GetBuffer() returns a 256 byte array even though the file is only 5 bytes long:

using (MemoryStream ms = new MemoryStream())
{
    using (FileStream fs = File.OpenRead("C:\\t\\hello.txt"))
    {
        fs.CopyTo(ms);
        byte[] results = ms.GetBuffer();
        Console.WriteLine("Size: {0}", results.Length); // 256
        byte[] justdata = new byte[ms.Length];
        Array.Copy(results, justdata, ms.Length);
        Console.WriteLine("Size: {0}", justdata.Length); // 5
    }
}

answered May 09 '13 at 23:35

Jason Goemaat

28,692
15
86
113

Thanks for this. So, you are saying that I am wasting space? Instead of `byte[] array = ms.GetBuffer();` I should use `byte[] justdata = new byte[ms.Length];`? – user1477388 May 10 '13 at 12:48
1

Yes, MemoryStream allocates a buffer to write into, looks like 256 bytes to start with. As you write to the buffer it sets the values of those underlying bytes and if it gets to the limit it allocates a larger buffer and copies the bytes to it, then starts filling up that buffer. GetBuffer() gets the actual underlying buffer which has extra room, 0 bytes at the end until they are written to. – Jason Goemaat May 10 '13 at 21:39

String or binary data would be truncated when uploading file in MVC

4 Answers4

Linked