[Resolved] Picture in ACCESS

[vbPoet]

how can picture file can be stored in Access Database...

** Don't advice me
i) to store path of file
ii) it increases OHD
iii) Bla Blah ...!!!!

[RhinoBull]

Here is a sample procedure to store image:

Public Sub AddNewImage(rstTemp As ADODB.Recordset, _
                       sTitle As String, sFileName As String)
'=============================================================
Dim file_num As String
Dim file_length As Long
Dim bytes() As Byte
Dim num_blocks As Long
Dim left_over As Long
Dim block_num As Long
 
On Error GoTo ErrHandler
 
    rstTemp.Find "Title='" & sTitle & "'"
    
    file_num = FreeFile
    Open sFileName For Binary Access Read As #file_num
    
    file_length = LOF(file_num)
    
    If file_length > 0 Then
        num_blocks = file_length / BLOCK_SIZE
        left_over = file_length Mod BLOCK_SIZE
        
        rstTemp("fImageSize") = file_length
        
        ReDim bytes(BLOCK_SIZE)
        For block_num = 1 To num_blocks
            Get #file_num, , bytes()
            rstTemp("fImage").AppendChunk bytes()
        Next block_num
        
        If left_over > 0 Then
            ReDim bytes(left_over)
            Get #file_num, , bytes()
            rstTemp("fImage").AppendChunk bytes()
        End If
        
        Close #file_num
    End If
    
    rstTemp.Update
    
    Exit Sub
 
ErrHandler:
 
    Debug.Print Err.Description
    Err.Clear
    'Resume Next
    Exit Sub
 
End Sub

But KIM that storing PATH is a better approach, though.

[dee-u]

Link

[vbPoet]

thx RhinO.
from your code snippet i realise that .. with your same code any file can be stored in database like .exe,.jpg,.swf,.doc
because you are first gaining binary access then appending this chunk into database ...
M i right ..?

[RhinoBull]

Absolutely yes!
The only problem is that MS Access is limited to 2GB ... but realistically after databse file reaches 1GB you will notice performance issues so frequently compacting mdb is highly preferable.

[vbPoet]

Absolutely yes!
The only problem is that MS Access is limited to 2GB ... but realistically after databse file reaches 1GB you will notice performance issues so frequently compacting mdb is highly preferable.

thx deU & RhinO
it means RhinO if we are using Oracle,mySQL then no Problem ..

[RhinoBull]

I haven't used it with MySql but Oracle, SQL Server, Sybase are fine.

[vbPoet]

thx RhinO
but unfortunately i cannot rate your post
it says
Spread your post any other place ( some thing like that )

[RhinoBull]

As you may already know I have no problem with that what so ever.

Cheers.

[RobDog888]

Hey RB, what is BLOCK_SIZE defined as? Nice code btw

[RhinoBull]

Thanks Rob ... it's near 10K in my app but you may redefine it:

Public Const BLOCK_SIZE = 10000

[RobDog888]

Oh, its a user defined const. Thanks.

[RhinoBull]

No problem.

[dee-u]

This might have some importance with you....

thx RhinO
but unfortunately i cannot rate your post
it says
Spread your post any other place ( some thing like that )

Why dont give me that rate instead?

[Krass]

RhinoBull, since you seem to be a guru in that field, my question is primarily aimed at you .......

I have a PictureBox filled with random colored pixels.

SavePicture pic1.Image, "c:\test\test.bmp" worked great. So I can now save test.bmp into a database. But what if I wanted to skip that step, and save the PictureBox *DIRECTLY* into my database... Can that be done?

I would appreciate to avoid saving the picture to disk first.

Thanks

[RhinoBull]

...I would appreciate to avoid saving the picture to disk first...

Why? What's the problem with that? You save it to disk, then to your database and then delete the file if you wish not to keep it...

[Krass]

Oh.. yes. This actually works great. Thanks.

I just usually try to avoid unnecessary steps. Even tho I still believe there *should* be a way, I wouldn't even bother to change my code, at that point.

Thank you

[tgcngb]

RB! do you have any codes that can make commandbutton into xp style without using a user control/class module. API? thanks!

[RhinoBull]

Welcome to Forums, tgcngb!

But how's your question relevant to this thread?

[tgcngb]

sorry. i forgot to think of the topic. but, if you still have something, hope you can share it. thanks!

[RhinoBull]

Try to use the search - it's very handy. Searching this forum for something like "XP styles" will most definitely return you bunch of pages with samples.

[tgcngb]

ok. tnx rb!

[CharlieK]

Hi guys,
do you know how can i use this code with vb2005?
= RhinoBull's posted - The insertion pictor to database =

[RhinoBull]

It's not designed for VB.Net.

[CharlieK]

Thanks Rhino. But expected similar result, for vb.net, how i write the code?

[nUflAvOrS]

If I want to save the file or image to database
What type of Data Type i should use ?

Thanks for advice

[Krass]

hey nUflAvOrS..

I use the "OLE Object" data type in my databases to store pictures......

[RhinoBull]

That would be any type that is compatible with BLOB - different db engines have different naming conventions but some of them identical so you really need to experiment.

[nUflAvOrS]

Thanks Krass and RhinoBull, I will try your method.
If got any problem i will ask the question here again.
Thanks !

[dong]

It isn't run, I sent to attachments file: http://www.mediafire.com/?j0kq1ityoch7r9i , can you help me ?

Here is a sample procedure to store image:

Public Sub AddNewImage(rstTemp As ADODB.Recordset, _
                       sTitle As String, sFileName As String)
'=============================================================
Dim file_num As String
Dim file_length As Long
Dim bytes() As Byte
Dim num_blocks As Long
Dim left_over As Long
Dim block_num As Long
 
On Error GoTo ErrHandler
 
    rstTemp.Find "Title='" & sTitle & "'"
    
    file_num = FreeFile
    Open sFileName For Binary Access Read As #file_num
    
    file_length = LOF(file_num)
    
    If file_length > 0 Then
        num_blocks = file_length / BLOCK_SIZE
        left_over = file_length Mod BLOCK_SIZE
        
        rstTemp("fImageSize") = file_length
        
        ReDim bytes(BLOCK_SIZE)
        For block_num = 1 To num_blocks
            Get #file_num, , bytes()
            rstTemp("fImage").AppendChunk bytes()
        Next block_num
        
        If left_over > 0 Then
            ReDim bytes(left_over)
            Get #file_num, , bytes()
            rstTemp("fImage").AppendChunk bytes()
        End If
        
        Close #file_num
    End If
    
    rstTemp.Update
    
    Exit Sub
 
ErrHandler:
 
    Debug.Print Err.Description
    Err.Clear
    'Resume Next
    Exit Sub
 
End Sub

But KIM that storing PATH is a better approach, though.

[RhinoBull]

Why don't you open a new thread (with meaningful title) and explain in details what you do, how you do it and what isn't working?

Thanks.

[dong]

The compress this file, this is example for yourself, the subject pictures insert into database and picture load from database up PictureBox or Image are very rare. I was copy your source code in VB6.0 and try run but it is not, so ask you. I want to know directions for use and declare. If i know, i never ask you.

[SuperDre]

If you need to store a picture into your database and want to read it directly into a picturebox (or any Picture(IPictureDisp) property) without first having to write the image to the tempfolder(or any folder) and use loadimage, you can use the propertybag... I found this method somewhere and I found it ingenius use of VB specific methods:

Field ImageData is of varbinary type.

Private Const csPropertyBagNameImageData As String = "ImageData"

Private property_picPicture As IPictureDisp

Private Sub ReadRecord(ByVal rs As RecordSet)
   Dim abBuffer()  As Byte
   Dim PB          As PropertyBag

      If IsNull(rs.Fields!ImageData) Then
        Set property_picPicture = Nothing
      Else
        abBuffer() = rs.Fields!ImageData
        Set PB = New PropertyBag
        PB.Contents = abBuffer()
        Set property_picPicture = PB.ReadProperty(csPropertyBagNameImageData)
        Set PB = Nothing
      End If
End Sub

Private Sub pWriteRecord(ByVal rs As RecordSet)
  Dim PB  As PropertyBag
    If property_picPicture Is Nothing Then
      rs.Fields!ImageData = Null
    Else
      Set PB = New PropertyBag
      Call PB.WriteProperty(csPropertyBagNameImageData, property_picPicture)
      rs.Fields!ImageData = PB.Contents
      Set PB = Nothing
    End If
    Call rs.Update
End Sub

The above code only has problems with image of ICON type, which will not have it's transparency anymore and the palet is fubar, but I never bothered YET to find a solution for that..