Let a paramter calculate it's own size
-
I'm doing a program to try to learn some ADO.NET programming and I'm running into the following problem: One of the fields in my database is a VarBinary(MAX) that I want to store and image in. I created my own SqlDataAdapter to talk to the DataSet and such, now on the SqlDataAdapter for the InsertCommand and UpdateCommand I have the following parameter to correspond to the image field in the database:
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.Image, 15000, "jacket");
the problem is that I don't know how big the image is going to be so I don't want to have to specify the size in the parameter, and if I change the parameter to
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.Image);
when I try to update the data I get and exception with the message "Parameterized Query ... expects parameter @jacket, which was not supplied." Any help on this problem would be most appreciated. Thanks!
- Aaron
-
I'm doing a program to try to learn some ADO.NET programming and I'm running into the following problem: One of the fields in my database is a VarBinary(MAX) that I want to store and image in. I created my own SqlDataAdapter to talk to the DataSet and such, now on the SqlDataAdapter for the InsertCommand and UpdateCommand I have the following parameter to correspond to the image field in the database:
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.Image, 15000, "jacket");
the problem is that I don't know how big the image is going to be so I don't want to have to specify the size in the parameter, and if I change the parameter to
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.Image);
when I try to update the data I get and exception with the message "Parameterized Query ... expects parameter @jacket, which was not supplied." Any help on this problem would be most appreciated. Thanks!
- Aaron
Image and VarBinary are two different column types. You should use SqlDbType.VarBinary if that is what is defined in the DB structure. Image: Stored out of band (not on the same page as the other table data) and can be up to 2 gigs in size. VarBinary: Stored with the other table data and limited by page size ~8000 bytes give or take your other columns. Not sure if these descriptions changed with 2005.
-
Image and VarBinary are two different column types. You should use SqlDbType.VarBinary if that is what is defined in the DB structure. Image: Stored out of band (not on the same page as the other table data) and can be up to 2 gigs in size. VarBinary: Stored with the other table data and limited by page size ~8000 bytes give or take your other columns. Not sure if these descriptions changed with 2005.
According to the SQL Server 2005 BOL the image datatype is going to be removed in future versions of SQL Server, which is why I in the database I set the field type to varbinary(max), which indicates maximum storage is 2^31-1 bytes. The same problem still occurs in the program if I run it using
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.VarBinary);
I still get the same exception "Parameterized Query ... expects parameter @jacket, which was not supplied." Is there any way to set this up to have to parameter use as much space as is necessary to store the image? Thanks for the help.
- Aaron
-
According to the SQL Server 2005 BOL the image datatype is going to be removed in future versions of SQL Server, which is why I in the database I set the field type to varbinary(max), which indicates maximum storage is 2^31-1 bytes. The same problem still occurs in the program if I run it using
param_arr2[7] = new SqlParameter ("@jacket", SqlDbType.VarBinary);
I still get the same exception "Parameterized Query ... expects parameter @jacket, which was not supplied." Is there any way to set this up to have to parameter use as much space as is necessary to store the image? Thanks for the help.
- Aaron
Quoting from MSDN: "For variable-length data types, Size describes the maximum amount of data to transmit to the server. For example, for a Unicode string value, Size could be used to limit the amount of data sent to the server to the first one hundred characters." So, you should set the size to the largest expected size you can handle. For updates, set the size later (just before using it in the sqlCommand): ((SqlParameter)param_array[7]).size = mydata.length;