how to load a picture in a picturebox from a database??? 2

[TanTrazz]

Hi There

I made a database in acces. Now i want to make a link with the database and the picturebox

The thing i want is that the picture in the database (Hyperlink) is shown in the picturebox

How can i do that??

Tnx TanTrazz

 

[elvenmaiden]

Did this in a project at school, but really went around the mullberry bush to get there. Probably a better way, but here goes:

We put the file path in the database and then called the path to load the picture box.

Hope this helps

 

[krshaikh]

Hi,

As you are using Access the only thing that is possible is to keep the actual picture path in a field. elvenmaiden is right.

I wish to add one thing here make sure to verify the physical existence of the picture file every time you try to laod that picture in the picture box. Problem arisis some times if somebody changes the file name or worse deletes the file totally. You can use Microsoft's scripting runtime liabray for this purpose. If you are stuck somewhere please dont hesitate to ask.

Regards,

- krshaikh -

 

[CCLINT]

Under DAO you can use the OLE container control.
Under ADO this isn't possible. But you can use the Get Chunk method easily.

If you are storing long binary data in a MDB, then I would do it in a seperate db where only the foreign key field and the long binary field are stored.
This will keep it out of the way of your main data base and keep that from growing too large.
It may not matter so much with this MDB that just stores pictures, and you can easily compact it on-the-fly.

I tend though to agree with elvenmaiden that you will have less problems storing the pictures outside of the database.
The pictures can be swapped out easily by the user.
You shouldn't store them as long binary in a SQL Server table anyways, as this degrads performance. Rather, save the path pointer only.


Here is a sample on loading using ADO and the GetChunk, AppendChunk methods:

1. Open a new VB project, add a reference to ADO 2.5+. Use the default form, adding two command buttons:
cmdGetPictFromDb and cmdSavePictToDb

2. Save the project in some test folder

3. Create an MDB called "Pictures.Mdb" in the project folder and add a table called "Pictures". Create two fields: one is an AutoNumber field called "AutoNum", and the other is an OLE object field called "PictureData". Do not add any records.

4. Have some picture files ready and copy these to the project folder.

5. Add the following code to the form's code window:
'========================================================
Option Explicit
Private WithEvents rsADO As ADODB.Recordset
Private conn As ADODB.Connection
'========================================================

Private Sub Form_Load()

Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0" _
& ";Data Source=" & App.Path & "\Pictures.Mdb" _
& ";Jet OLEDB:Engine Type=5"

Set rsADO = New ADODB.Recordset
rsADO.CursorLocation = adUseServer
rsADO.CursorType = adOpenKeyset
rsADO.LockType = adLockOptimistic
rsADO.Source = "SELECT * FROM Pictures"
Set rsADO.ActiveConnection = conn

rsADO.Open , , , , Options:=adCmdText Or adAsyncFetch

End Sub
'========================================================

Private Sub Form_Unload(Cancel As Integer)

On Error Resume Next
rsADO.Close
conn.Close
Set rsADO = Nothing
Set conn = Nothing

End Sub
'========================================================

Private Sub cmdSavePictToDb_Click()
SavePictToDb App.Path & "\TestPict1.bmp"
End Sub
'========================================================

Private Sub SavePictToDb(sPictFile As String)
Dim lSize As Long
Dim lOffset As Long
Dim bytChunkData() As Byte
Dim iFileNum As Integer
Dim sTempFile As String

iFileNum = FreeFile

Open sPictFile For Binary As #iFileNum
ReDim bytChunkData(FileLen(sPictFile))

Get #iFileNum, , bytChunkData
Close #iFileNum

rsADO.AddNew
rsADO("PictureData&quot.AppendChunk bytChunkData
rsADO.Update

End Sub
'========================================================

Private Sub cmdGetPictFromDb_Click()
GetPictFromDb 1
End Sub
Private Sub GetPictFromDb(lRecNumber As Long)
Const cChunkSize = 100

Dim lSize As Long
Dim lOffset As Long
Dim bytChunkData() As Byte
Dim iFileNum As Integer
Dim sTempFile As String

sTempFile = App.Path & "\TmpPict.jpg"

If Len(Dir(sTempFile)) > 0 Then
Kill sTempFile
End If

rsADO.Find "AutoNum = " & lRecNumber

If Not rsADO.EOF Then
iFileNum = FreeFile
Open sTempFile For Binary As #iFileNum

lSize = rsADO("PictureData&quot.ActualSize

Do While lOffset < lSize
bytChunkData = rsADO(&quot;PictureData&quot.GetChunk(cChunkSize)
Put #iFileNum, , bytChunkData
lOffset = lOffset + cChunkSize
Loop

Close #iFileNum
Image1.Picture = LoadPicture(sTempFile)
Kill sTempFile
Else
rsADO.MoveFirst
End If
End Sub
'========================================================

Now you will notice in the cmdSavePictToDb_Click event, I am passing a file name for a picture file. You will need to cheng the name to fit your needs.
And, in the cmdGetPictFromDb_Click event I am passing a record key, which is a value for the AutoNum field.
For both of these, you will need to change accordingly.
This is only an example. How you implement it is your thing.

Have fun! [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

Oh yes. I forgot to mention to add an Image or Picture cobtrol to the form as well. You can see where the Picture property for the Image control is getting set at the end of the GetPictFromDb proceedure:

Image1.Picture = LoadPicture(sTempFile)

Please note again that this is only an example and by no means a finished product (concerning the Append and Get Chunck proceedures). You will need to do some other things such as adding error handling.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

And if you are using ADO 2.5 or higher you can change the functions as follows:

Private Sub SavePictToDb(sPictFile As String)
Dim strmStream As New ADODB.Stream

If Len(Dir(sPictFile)) > 0 Then
With strmStream
.Type = adTypeBinary
.Open
.LoadFromFile sPictFile

rsADO.AddNew
rsADO(&quot;PictureData&quot = .Read
rsADO.Update
.Close
End With
End If
Set strmStream = Nothing
End Sub [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[TanTrazz]

The thing that wont work is to load the picture from the database into the picurebox.

something like this : picture1.picture = m_rstcontacts.Fields(&quot;Scan&quot.Value

 

[chelseatech]

Hey thanks guys, this is exactly what I'm looking for to load an in memory ADO recordset with an Image.

Much appreciated at 2am in the morning when I've finally got this working. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[jojo11]

I noticed this thread and had an issue. Everything goes we try and load the picture. When I debug and stop just before the image is deleted and it cannot be viewed in any type of image viewer. I am running XP and backend of Access 2002. The image is an OLE object. When I build a form in Access it can be seen fine so I am pretty sure that the image is valid.

 

[jojo11]

I should also note that the images were already in there. They were simply placed in the table using drag and drop. I also think that they were originally .bmp files.

 

[MrVB50au]

I have a DB file to store the path list of pictures and One that holds the physical picture that was chosen from the list.

If a user Chooses to use a picture from the DB List it is past to the other DB in physical form and updated.

The DB that holds the physical Picture, does not use the AddNew method, it only hold one and only one record that is updated everytime a user chooses to choose a picture from the DB list (that only stores the path).

 

[dalec]

I have the same issue as JoJo11, I am trying this routine,which seems to work fine, but, I still can't view the image from the file (it also does not display from the picture box or a image box)

Help is appreciated.

 

[Rufassa]

I created a clone to this logic in C# but when I attempt to Load the Image from a File I get an Out of Memory exception. And when I try to load the image from a Memory stream created using the byte[] from the DB I get Invalid Parameter Error. I don't have any problem reading from a SQL Server Image field using the memory stream; I just can't read a valid image from access of an Image field in MSSQL that was pulled directly from Access. Can anyone help me with this issue. Sorry it is not VB but my project is in C# (I am a converted Java coder).

const int cChunckSize = 100;
int ISize = 0, IOffset = 0;
string sTempFile, ConnectionString;
Byte[] bytChunkData;

sTempFile = Application.StartupPath + "\\..\\..\\img\\TmpPict.bmp";
if (File.Exists(sTempFile))
File.Delete(sTempFile);



ADODB.Connection conn = new ADODB.ConnectionClass();
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Data Source=\"" +
"C:\\Documents and Settings\\rufassa\\My Documents\\util\\Product Database_Lite.mdb\";Mode=Share Deny None;Jet " +
"OLEDB:Engine Type=5;Provider=\"Microsoft.Jet.OLEDB.4.0\";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist " +
"security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt " +
"Database=False;Jet OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on Compact=False;User " +
"ID=Admin;Jet OLEDB:Global Bulk Transactions=1";

ADODB.Recordset rsADO = new ADODB.RecordsetClass();
rsADO.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
conn.Open(ConnectionString,"","",ADODB.ConnectOptionEnum.adConnectUnspecified.GetHashCode());
rsADO.Open("Select ID, Picture FROM [Merch Photos] WHERE ID = " + this.objdsAccess.Tables["Merch Photos"].Rows[this.BindingContext[objdsAccess,"Merch Photos"].Position]["ID"],conn,ADODB.CursorTypeEnum.adOpenForwardOnly,ADODB.LockTypeEnum.adLockOptimistic,ADODB.RecordOpenOptionsEnum.adOpenRecordUnspecified.GetHashCode());

if(!rsADO.EOF)
{

FileStream iFileNum = new FileStream(sTempFile,FileMode.OpenOrCreate,FileAccess.Write);
ISize = rsADO.Fields["Picture"].ActualSize;

while (IOffset < ISize)
{
bytChunkData = (byte[])rsADO.Fields["Picture"].GetChunk(cChunckSize);
iFileNum.Write(bytChunkData,0,bytChunkData.Length);
IOffset += bytChunkData.Length;
}
iFileNum.Close();
}

if (File.Exists(sTempFile))
this.pctAccess.Image = Bitmap.FromFile(sTempFile,true);

----------Alternate--------------------------

if(!rsADO.EOF)
{
MemoryStream imgStream = new MemoryStream();

ISize = rsADO.Fields["Picture"].ActualSize;

while (IOffset < ISize)
{
bytChunkData = (byte[])rsADO.Fields["Picture"].GetChunk(cChunckSize);
imgStream.Write(bytChunkData,0,bytChunkData.Length);
IOffset += bytChunkData.Length;
}
imgStream.Close();
}

this.pctAccess.Image = Bitmap.FromStream(imgStream);