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 "DSBoernekirken1"
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
OleDbDataAdapter1.Fill(DataSet11)
'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.Image = Image.FromStream(ms)
End Sub

I changed this to
Dim arrPicture() As Byte = CType(DsBoerneKirken1.tPersoner.PhotoColumn,
Byte()) ' Intellisense only allows Photocolumn, not Photo. Then I receive
the error "Value of type System.data.Datacolumn can not be converted to
1-dimensional array of Byte"
' I have also tried
'Dim arrPicture2() As Byte =
CType(DsBoerneKirken1.tPersonerDataTable.PhotoColumn, Byte())
'Dim arrPicture3() As Byte = CType(DsBoerneKirken1.tPersonerDataTable.Photo,
Byte())
' Dim arrPicture4() As Byte = CType(DsBoerneKirken1.tPersoner.Photo, Byte())

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

Can anyone tell me what I am doing wrong?

*****'
I also found

Dim bytBLOBData() As Byte = ds.Tables("BLOBTest").Rows(c -
1)("BLOBData")
Dim stmBLOBData As New MemoryStream(bytBLOBData)
picBLOB.Image = Image.FromStream(stmBLOBData)

Which I changed to
Dim bytBLOBData() As Byte = DsBoerneKirken1.tPersoner.PhotoColumn
Dim stmBLOBData As New MemoryStream(bytBLOBData)
PictureboxPhoto.Image = Image.FromStream(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(..........................
DsBoerneKirken1.tPersonerDataTable.Photo

Maybe it can be something like
DsBoerneKirken1.tPersonerDataTabele.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(CurrentRow) code?

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

Here is my exact code:
Dim arrPicture() As Byte = CType(DsBoerneKirken1.tPersoner.Rows(0)("photo"),
Byte())
Dim ms As New IO.MemoryStream(arrPicture)
Try
PictureboxPhoto.Image = Image.FromStream(ms)
Catch ex As Exception
MsgBox(ex.Message)
End Try

It is the "PictureboxPhoto.Image = Image.FromStream(ms)" line
that throws the exception.

Best regards

Jan

 

[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

 

[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]

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.BindingContext(DsBoerneKirken1, "tPersoner").Position = CurrentRow
DsBoerneKirken1_PositionChanged()
End sub
*****************
Sub DsBoerneKirken1_PositionChanged
Me.lblPostAfPost.Text = (((Me.BindingContext(DsBoerneKirken1,
"tPersoner").Position + 1).ToString + " af ") _
+ Me.BindingContext(DsBoerneKirken1, "tPersoner").Count.ToString)

GetPicture()

End sub
*****************
Sub GetPicture()
If DsBoerneKirken1.tPersoner.Rows(CurrentRow)("Photo") Is DBNull.Value
Then
PictureboxPhoto.Image = Nothing
Else
Dim arrPicture() As Byte =
CType(DsBoerneKirken1.tPersoner.Rows(CurrentRow)("Photo"), Byte())
Dim ms As New IO.MemoryStream(arrPicture)
Dim im As Image = Image.FromStream(ms)
PictureboxPhoto.Image = im
End If
End Sub
*****************
Saving a picture in the current row:
Private Sub btnIndsaetBillede
OpenFileGetPathForPicture.ShowDialog()
Dim fs As New FileStream(OpenFileGetPathForPicture.FileName,
FileMode.OpenOrCreate, FileAccess.Read)
Dim MyData(fs.Length) As Byte
fs.Read(MyData, 0, fs.Length)
fs.Close()
DsBoerneKirken1.tPersoner.Rows(CurrentRow)("Photo") = MyData
SqlDA_tPersons.Update(DsBoerneKirken1, "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

 

[Fergus Cooney]

Hi Jan,

~~ I have a public variable called CurrentRow that keeps
~~ track of the current row (I bet that took you by surprise!)

ROFL. Really, Jan, you should name your variables after what they do. It's
so misleading otherwise.

;-))

Regards,
Fergus

 

[Cor]

Hi Jan,

I find what you did make looking very nice and clean, I think you will
become (are already) a good contributor to this newsgroup.

I am struggling with the same problem as you, what is wisdom.

Last years my way of thinking about transactions is become a kind of
Internet thinking that means (always still) low band wide and thinking in a
transactional way (there is no sure connection with the database).

At the moment I am making a kind of same application like you are doing, it
will be my entrée in the bitmap VB world. I already did make Herfried attend
on it.

For Internet your solution does not work. As far as I know you cannot use an
image in a XML dataset.

That brings me back to the case.

For Internet an image is stored in the Internet Temp Folder. So the user
gets it only once.

In your situation and probably mine soon, the user has to get it every time
from the database, that he changes the view on his picture.

So what is wisdom for a non Web application? Is that making a dataset of
with images and bind that in the way you use your textboxes or just do it
the way you're doing now.

I think that it will depend how the user will use it. I think that using a
dataset with images will take a lot of power from the server if the user is
collecting 100 images and uses only 1.

The picturebox can be bound in the same way as the textbox I saw, but if it
works I don't know because I told you I did do nothing before with bitmaps
(images in a database).

But maybe we will come together to the best solution.

Just some thoughts,

Cor

 

[Jan Nielsen]

Hi Cor,
You are actually addressing one of the problems I have just encountered. How
do you send a dataset that contains a picture (in an XML file). Since XML is
purely textbased this is not possible.
Maybe an ADO solution will be the best in this situation. But then the
disconnected thing is of course lost.

I would like to know how you bind a picture box like you bind a textbox. I
have not been able to do this, and the code from MS Knowledgebase suggests
something like the lookup solution I use.


BTW just in case you did not know:
My CurrentRow pointer failed on the MoveLast button if I deleted rows from
the dataset.
I realized that this is because I used
CurrentRow = Me.DsBoerneKirken1.Tables("Tpersoner").Rows.Count() ' This
code includes deleted rows. Then when I tried to use the moveNext button I
got en exception.
This code does not include the deleted rows:
CurrentRow = Me.BindingContext(DsBoerneKirken1, "tPersoner").Count() - 1

Best regards

Jan

 

[Fergus Cooney]

Hi Jan, Cor,

Here's an experiment for you
Create a Form and stick a PictureBox on it.
Assign an image file to it.
Save the Form.
Open up the Forms's resx file in Notepad.

Voila. Xml with embedded graphics.

Regards,
Fergus

 

[Cor]

Hi Fergus,

GoodMorning I saw it was very late again, Hi, I did not get an message about
my express fault about BSI and BST from you , that has its own story,
which you like.

(Jan I have your problem in my answering map, but I think this is a part of
your question. And although I never used a currency manager, I will probably
look to it, I was looking if I could use it some time ago, but did say no
to myself)

Fergus I don't have to try that what you say.
Now you say it I know it, it is clever of course from you.
It gives a good lead to investigate that.
The imgagelist uses it too.
(Sh....., Jan when I write that, I think that we can investigate, if that
will fit for your problem, but as far as it is me not for tomorrow)..

Fergus, I shalll, when I am there with the problems I am dealing with now,
going to investigage how I can use your idea.

You remember that we both did that problem with the one who wanted a picture
on the web withouth saving it first?
That is almost the same solution Jan uses.

Therefore I did make that solution with that extra page, that is made from
the memorystream and then refering to it just in time in the VB..net
procedure.
When I did that, I saw nowhere a lead to a bitmap in a XML file. (I still
will remember me that, I had made it very nice with real a managed page and
then the OP said he wanted the same dump picture pages as Google uses when
you have choosen to give them in orginial size).

But I will investigate that RESX for sure and now you did bring me on a
good track.

Cor

ps.
That answer from me about "for each mare as horse in stable" was total
different.
I did make an example that you with "as horse", could see that it was not
Mary the stable maid.
The procedure was not 'let them drink some water'

 

[Fergus Cooney]

Howdy Cor,

I didn't mention the BSI/BST because I thought that would just be
nit-picking. An honest mistake needs no slap on the wrist. And the actual
times themselves were not really important - it was the hours between them.

Lol. The reason resx and images was fresh in my mind is because when I
receive a v2003 project from an OP, it won't work in my v2002 - due, in part,
to the resx file and the embedded images!!

But I'm glad that it provides an idea that may be useful.

I'm looking forward to seeing how your app develops. I'm only getting
snippets of information so I'm still guessing what it does. ;-)

Regards,
Fergus

 

[Cor]

Hi Jan,

I think I found your problem.

With a dataset you can use methodes datarow.remove, datarow.removeat and
datarow.delete.

The diference is, that the datarow delete does not automatic the
"acceptchanges" while the methode datarow.remove and removeat does that
automaticly.

I think you are using the datarow.delete and no changes are made to the
dataset while you want it automaticly.

Maybe you can try it with datarow.removeat(something as currentposition).
or with datarow.remove(datarow to remove).

This is not stuff where I say to myself, this is sure.

I hope we are a little bit further

Cor

 

[Jan Nielsen]

Hi again Cor
Actually it was not a problem.
It was a solution (to a problem you did not mention)
I just fixed an error in my own application and since it seemed you are
building an application of your own I just wanted to make sure you did not
repeat my mistake.
I was trying to inform you about a trap:
CurrentRow = Me.DsBoerneKirken1.Tables("Tpersoner").Rows.Count()
is not the same as
CurrentRow = Me.BindingContext(DsBoerneKirken1, "tPersoner").Count()

The first includes the rows marked for deletion whereas the last does not.

But thanks for your time.

Have fun

Jan