size limit for the sql_variant exceeds

Question

I am trying to save image from fileupload control into the database

public Byte[] bytes;
Stream fs = FileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
bytes = br.ReadBytes((Int32)fs.Length);
SqlDataSource2.Update();

protected void SqlDataSource2_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
   e.Command.Parameters["@project_file"].Value = bytes;
}

My database project_file field is set to varbinary(MAX),

but it is throwing an error

Parameter '@project_file' exceeds the size limit for the sql_variant datatype.

Please suggest some solution

Answer 1

This is a quote from MSDN on binary and varbinary:

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. The storage size is the actual length of the data entered + 2 bytes. The data that is entered can be 0 bytes in length. The ANSI SQL synonym for varbinary is binary varying.

varbinary(MAX) can hold an image that is ~2GB of size.

Solution to your problem:

You forgot to specify the type in your code. You need to set the correct SqlDbType.

e.Command.Parameters["@project_file"].SqlDbType = SqlDbType.VarBinary

What you should also do i set the correct Size.

Answer 2

The problem is that sql server assign the type of the parameter as "SQL_Variant".

Try to assing the DbType :

e.Command.Parameters["@project_file"].SqlDbType = SqlDbType.Image
e.Command.Parameters["@project_file"].Value = bytes;