Let me explain first that I'm moving stage and the steps I have taken to solve the problems of storing a null value in a field of type image in a SQL database.
The SQL database I have a table with a field of type image that supports zero.
In this same database I create a stored procedure to insert and update the table
From. NET created a Typed Data Set where I create a DataTable with the fields of the SQL table and I assign to each field's data type. In the case of image field I assign the data type. NET System.Byte (). I use this DataTable to load the fields from multiple tables in the SQL database.
When calling the stored procedure from. NET I would point out the parameters in a SqlCommand and execute it as ExecuteNonQuery.
At first glance all the steps are working properly and I have no compilation error.
First error happens when I try to save a null value in the image field
For the point where error occurs in the class is automatically generated from the DataSet. Is taking place in the column in the DataTable image I created. Occurs because when you create the image I left field in the properties indicated that generates an exception if it encounters invalid.
I see two options to avoid this error
-
The first is a change of ownership NullValue Image field so that instead of generating an exception when they contain zero, that does not give anything (is an option down in that field) -
The second is to change the code where I add the parameter @ image. Instead of using a shortcut IIF which evaluates the two sides (and then skips the error) If using a separate, so that if the image is null, does not come to assess.
seems that everything is fine, but when we re-run with another error.
In this case the error is generated when running the stored procedure command. The first thing you notice is that the speech error data type nvarchar , we have not used at any time.
If you put a breakpoint and look at the picture we sent to parameter stored procedure surprise we found that the type of data we are sending is a nvarchar (SqlDbType).
This is because adding the parameter to the command does not indicate any kind of data, but only pass the parameter name and value
solution is an indication in the command parameter the sql data type is an Image
So if we repeat the performance we see in the inspection of the @ image the command has disappeared type nvarchar the image and everything is working correctly
