Image-field in DataTable (SQL Server)

[Michael Maes]

Hello,

I want to select an image with the OpenFileDialog and set it to a field in a datatable. Calling the DataSet.Update() should save the image (among other changes in the DataSet) to the DataSource (SQL Server 2000 sp3).

The thing is: I get it to work if I do it directly with SQL, but if I have the DataSet.Update() do it, I get an error when the when the DataSet get Re-loaded (and I move to the Updated record):

System.ArgumentException: Invalid parameter used.
at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement)
at System.Drawing.Image.FromStream(Stream stream)
at ........

This is the code I use:

' Save the image to the Database using SQL
With Me.picProduct_Thumbnail
.Image = Image.FromFile(OpenFileDialog1.FileName)
.SizeMode = PictureBoxSizeMode.CenterImage
End With
Dim mStream As New System.IO.MemoryStream
picProduct_Thumbnail.Image.Save(mStream, picProduct_Thumbnail.Image.RawFormat)
Dim bytImage() As Byte = mStream.GetBuffer
mStream.Close()
Dim sProductID As String = Me.DsPicture1.tblProducts(Me.cmPic.Position).productID
Dim strSQL As String = _
"UPDATE tblProducts SET thumbnail = @thumbnail" & _
" WHERE ([productID] = '" & sProductID & "' AND [companyID] = 1)" ' & Session.CompanyID & ")"
Dim cmd As New SqlCommand(strSQL, Adapters.sdcStegoBase)
With cmd
.Parameters.Add(New SqlParameter("@thumbnail", _
SqlDbType.Image)).Value = bytImage
End With
Adapters.sdcStegoBase.Open()
cmd.ExecuteNonQuery()
Adapters.sdcStegoBase.Close()

' Save the image to the Database using Dataset.Update()
Me.DsPicture1.tblProducts.Rows(cmPic.Position)(Me.DsPicture1.tblProducts.thumbnailColumn.ToString) = bytImage
Me.SqlDataAdapter1.Update(Me.DsPicture1)
Me.DsPicture1.AcceptChanges()

a.. I use Bitmaps for testing
b.. thumbnail has an Image DataType in the Database and base64Binary in the Schema
c.. I'm using vb 2003
I've looked at all the postings I've found (many of Cor) but I "simply do not see it" :-( - My brains are boiled I guess.....

Thanks for any help,

Michael

 

[Michael Maes]

Hi Bin,

Thanks for your concern.
I probably wasn't clear in my explenation:

the code under "Save the image to the Database using SQL" does work.
it's the 'DataAdapter.Update-Method' which fails.

I can set the image to the DataSet.DataTable-Field and you can browse
through the records with the image displaying fine. You can call the Update
(DataSource gets updated) but then, if you reload the dataset (eighter new
instance or refresh) then the image-field is "corrupt".

So:
saving the image to SQL Server with 'cmd.ExecuteNonQuery()' works.
saving the image to SQL Server with DataAdapter.Update fails (wrong format I
think?)

Regards,

Michael

 

[Kevin Yu [MSFT]]

Thanks for Bin's quick response!

Hi Michael,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that the SqlDataAdapter.Update method cannot
update the records with image information to the data source. If there is
any misunderstanding, please feel free to let me know.

I have tried the code you have provided on my machine. However, the
exception was not thrown this time. I'm using the following code.

With Me.picProduct_Thumbnail
.Image = Image.FromFile(OpenFileDialog1.FileName)
.SizeMode = PictureBoxSizeMode.CenterImage
End With
Dim mStream As New System.IO.MemoryStream
picProduct_Thumbnail.Image.Save(mStream,
picProduct_Thumbnail.Image.RawFormat)
Dim bytImage() As Byte = mStream.GetBuffer
mStream.Close()
Me.DsPicture1.tblProducts.Rows(cmPic.Position)(Me.DsPicture1.tblProducts.thu
mbnailColumn.ToString) = bytImage
Me.SqlDataAdapter1.Update(Me.DsPicture1)
Me.DsPicture1.AcceptChanges()

Since you are getting the error when the DataSet get Re-loaded, could there
be something wrong with the CurrencyManager object? So please set a
breakpoint in code and check the value for cmPic.Position to see if you
have get the correct CurrencyManager object for the binding context.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Michael Maes]

Hi Kevin and Bin,

I've included a test-project which has the Issue (on my computer anyway).
Explenation is found in the project.
Should you need any further clarification, please contact me.

The project is in vb.Net 2003

Kind regards,

Michael

 

[Michael Maes]

Hey Bin,

The NG won't swallow an attachment "this large" (89Kb).
So if you'd like to have a copy, provide me your email-address.
Kevin already received his copy.

Kind regards,

Michael

 

[Kevin Yu [MSFT]]

Hi Michael,

Thanks for you code. Based on my research, the ArgumentException was thrown
when the form loads. It occurs because the image was not fully updated to
the database due to the following line of code in the designer generated
code.

Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Photo",
System.Data.SqlDbType.VarBinary, 16, "Photo"))

Notice that the size has been set to 16. So only 16 bytes of the image was
updated. I don't know why this happens, because it works well on my machine
with the following code generated.

Me.SqlUpdateCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Photo",
System.Data.SqlDbType.VarBinary, 2147483647, "Photo"))

Please replace the size 16 with 2147483647, so everything works fine.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Michael Maes]

Hi Kevin,

Thanks for your research.
While I was reading your reply, I was thinking "figures" because I had
noticed (in the debugger) that the size was always 16 bytes.

Did you report this behavior to the "IDE-Team"?

Anyway: now I know where to look, it's easy to correct this bug.

Once again Kevin: Thanks!

Best regards,

Michael

 

[Kevin Yu [MSFT]]

Hi Michael,

We will report it to the corresponding team if we have confirmed this.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."