Databind a PictureBox to a SQL Server...

[Jan Nielsen]

Hi
I would like to databind a Picturebox to a SQL Server.
I have a typed Dataset "DSBoernekirken 1"
It has a table named "tpersoner" with a column "Photo"
I of course would like to bind the image directly to tpersoner.photo using
Properties (like I do with text fields), but this does not seem to be
possible.
Then I searched some news groups and Knowledgebase and found the following
code:
Sub ShowPicture()
'get the data from the database and put it in the dataset
OleDbDataAdapte r1.Fill(DataSet 11)
'get the byte array and convert it to a picture
Dim arrPicture() As Byte = CType(DataSet11 .Photos.picture , Byte())
Dim ms As New IO.MemoryStream (arrPicture)
'show the picture in the picturebox
PictureBox1.Ima ge = Image.FromStrea m(ms)
End Sub

I changed this to
Dim arrPicture() As Byte = CType(DsBoerneK irken1.tPersone r.PhotoColumn,
Byte()) ' Intellisense only allows Photocolumn, not Photo. Then I receive
the error "Value of type System.data.Dat acolumn can not be converted to
1-dimensional array of Byte"
' I have also tried
'Dim arrPicture2() As Byte =
CType(DsBoerneK irken1.tPersone rDataTable.Phot oColumn, Byte())
'Dim arrPicture3() As Byte = CType(DsBoerneK irken1.tPersone rDataTable.Phot o,
Byte())
' Dim arrPicture4() As Byte = CType(DsBoerneK irken1.tPersone r.Photo, Byte())

Dim ms As New IO.MemoryStream (arrPicture)
'show the picture in the picturebox
PictureboxPhoto .Image = Image.FromStrea m(ms)

Can anyone tell me what I am doing wrong?

*****'
I also found

Dim bytBLOBData() As Byte = ds.Tables("BLOB Test").Rows(c -
1)("BLOBData")
Dim stmBLOBData As New MemoryStream(by tBLOBData)
picBLOB.Image = Image.FromStrea m(stmBLOBData)

Which I changed to
Dim bytBLOBData() As Byte = DsBoerneKirken1 .tPersoner.Phot oColumn
Dim stmBLOBData As New MemoryStream(by tBLOBData)
PictureboxPhoto .Image = Image.FromStrea m(stmBLOBData)
With about the same error.

Best regards

Jan

[Cor]

Hi Jan,

I think it has to do with the way you use the dataset

'Dim arrPicture3() As Byte = CType(......... ............... ..
DsBoerneKirken 1.tPersonerData Table.Photo

Maybe it can be something like
DsBoerneKirken1 .tPersonerDataT abele.rows(0)(" photo") and that is than the
first picture in your dataset.

But if that not works, you can try
DsBoerneKirken1 .tables(0).rows (0)("photo")

The rest was as far as I could see like has to be.

I hope this helps a little bit?

Cor

[Jan Nielsen]

Hi Cor
Thanks for answering
So if I would like the picture from the current row to be shown do I have to
make my own pointer to insert in the ...rows(Current Row) code?

I receive an error
"Invalid Parameter Used" when I use rows(0).

Here is my exact code:
Dim arrPicture() As Byte = CType(DsBoerneK irken1.tPersone r.Rows(0)("phot o"),
Byte())
Dim ms As New IO.MemoryStream (arrPicture)
Try
PictureboxPhoto .Image = Image.FromStrea m(ms)
Catch ex As Exception
MsgBox(ex.Messa ge)
End Try

It is the "PictureboxPhot o.Image = Image.FromStrea m(ms)" line
that throws the exception.

Best regards

Jan

"Cor" <no*@non.com> skrev i en meddelelse
news:3f******** *************** @reader20.wxs.n l...

Hi Jan,

I think it has to do with the way you use the dataset

'Dim arrPicture3() As Byte = CType(......... ............... ..
DsBoerneKirken 1.tPersonerData Table.Photo

Maybe it can be something like
DsBoerneKirken1 .tPersonerDataT abele.rows(0)(" photo") and that is than the
first picture in your dataset.

But if that not works, you can try
DsBoerneKirken1 .tables(0).rows (0)("photo")

The rest was as far as I could see like has to be.

I hope this helps a little bit?

Cor

[Cor]

Hi Jan,

My "photo" was an example, maybe it is the same, but when I look at your
code it is then probably with an upercase. (it is the name of your photo
item in your SQL database (or the way you have named in your SQL select
script).

Cor

Here is my exact code:
Dim arrPicture() As Byte = CType(DsBoerneK irken1.tPersone r.Rows(0)("phot o"), Byte())
Dim ms As New IO.MemoryStream (arrPicture)
Try
PictureboxPhoto .Image = Image.FromStrea m(ms)
Catch ex As Exception
MsgBox(ex.Messa ge)
End Try

[Jan Nielsen]

Hi Cor
Thanks again for your time.
"Photo" (with a capital P is the correct column name)
But I still receive the error even after correcting this.
And I am sure that the row contains a bitmap picture. (I can view it in MS
Access if I link the same table)

While searching newsgroups I found the solution or rather the problem:
"The code is correct. My guess is that the image you're getting was inserted
into the database either through Access or the VB OLE Container
control. If so, the image is wrapped with an OLE header. If the image is
stored purely as binary data, then this approach should work fine. "

I used Access to add picture.
I tried to add a picture from file using .Net code and then it works. But
then I cannot view it in Access. But I can live with that for the moment.

Jan


'

"Cor" <no*@non.com> skrev i en meddelelse
news:3f******** *************** @reader20.wxs.n l...

Hi Jan,

My "photo" was an example, maybe it is the same, but when I look at your
code it is then probably with an upercase. (it is the name of your photo
item in your SQL database (or the way you have named in your SQL select
script).

Cor

Here is my exact code:
Dim arrPicture() As Byte =

CType(DsBoerneK irken1.tPersone r.Rows(0)("phot o"),

Byte())
Dim ms As New IO.MemoryStream (arrPicture)
Try
PictureboxPhoto .Image = Image.FromStrea m(ms)
Catch ex As Exception
MsgBox(ex.Messa ge)
End Try

[Cor]

Hi Jan,
That I did not want to ask
..
What you was doing was reading a bitmap from the SQL database and then using
a memory stream loading it in the picture control.

But I am happy you found your solution.

Cor

[Fergus Cooney]

Hi Cor,

I don't there's a solution yet. Do you remember that guy who wanted to
know how to get an image of of an OLE Wrapper? I think Jan's saying that he's
fallen into that tarpit as well. :-(

Regards,
Fergus

[Cor]

Hi Fergus,

I misunderstood the answer from Jan, it was early this morning and today I
had the same problem as you yesterday so I did it a little bit slowly today.

But I am sure you saw my messages about bitmaps in an other thread, the
brain connector for that is not yet connected. I saw this problem more as a
database problem, and that is solved I think.

So what is needed, that Jan gets a filter to skips that OLE header, maybe a
nice idea if Crirus looks to that, he had had such much help, that I think
he is best willing to give something back.

I am sure you know how to ask him.

:-)

Cor

[Fergus Cooney]

Hi Cor,

I don't think this is one for Crirus as it's an OLE problem. It needn't be
a bitmap in the field, it could be a wave file, for example. The question is
how to open that damn wrapper. Who's got the OLE tin opener? ;-)

Unfortunately no solution came out of that other thread.

Regards,
Fergus

[Jan Nielsen]

Hi again Cor
Now I have a working solution but I wonder: Is my way a smart way of doing
it?
The picturebox is not really bound to the SQL Server.

Here is how I do:
I have a public variable called CurrentRow that keeps track of the current
row (I bet that took you by surprise!)
Whenever the user clicks the (next, previous, first, last) button the
Currentrow variable is changed accordingly.

Example - Move to next row
Sub Nextrow
CurrentRow += 1
Me.BindingConte xt(DsBoerneKirk en1, "tPersoner").Po sition = CurrentRow
DsBoerneKirken1 _PositionChange d()
End sub
*************** **
Sub DsBoerneKirken1 _PositionChange d
Me.lblPostAfPos t.Text = (((Me.BindingCo ntext(DsBoerneK irken1,
"tPersoner").Po sition + 1).ToString + " af ") _
+ Me.BindingConte xt(DsBoerneKirk en1, "tPersoner").Co unt.ToString)

GetPicture()

End sub
*************** **
Sub GetPicture()
If DsBoerneKirken1 .tPersoner.Rows (CurrentRow)("P hoto") Is DBNull.Value
Then
PictureboxPhoto .Image = Nothing
Else
Dim arrPicture() As Byte =
CType(DsBoerneK irken1.tPersone r.Rows(CurrentR ow)("Photo"), Byte())
Dim ms As New IO.MemoryStream (arrPicture)
Dim im As Image = Image.FromStrea m(ms)
PictureboxPhoto .Image = im
End If
End Sub
*************** **
Saving a picture in the current row:
Private Sub btnIndsaetBille de
OpenFileGetPath ForPicture.Show Dialog()
Dim fs As New FileStream(Open FileGetPathForP icture.FileName ,
FileMode.OpenOr Create, FileAccess.Read )
Dim MyData(fs.Lengt h) As Byte
fs.Read(MyData, 0, fs.Length)
fs.Close()
DsBoerneKirken1 .tPersoner.Rows (CurrentRow)("P hoto") = MyData
SqlDA_tPersons. Update(DsBoerne Kirken1, "tPersoner" )
fs = Nothing
GetPicture() ' show the just added picture
End sub
*************** *************** ********
The picturebox is not bound to the sql server (at least not like the other
(textbox) controls.
It just "looks up" the picture
Is there a smarter way of doing this?

Best regards

Jan


"Cor" <no*@non.com> skrev i en meddelelse
news:3f******** *************** @reader21.wxs.n l...

Hi Jan,
That I did not want to ask
.
What you was doing was reading a bitmap from the SQL database and then using a memory stream loading it in the picture control.

But I am happy you found your solution.

Cor