Results 1 to 2 of 2

Thread: Database - How can I store images (or other files) in a database?

  1. #1

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Database - How can I store images (or other files) in a database?

    Most databases allow you to store data of various kinds, including differing kinds of numbers and text. Most also allow you to store binary data, which allows you to store entire files inside your records.


    One thing to bear in mind is that storing files can increase the size of your database dramatically - which can cause problems for some database systems if you have too many (MS Access especially). Storing images in the database does however mean that you can backup the images along with the rest of your data.

    An alternative to storing images in the database is to store them in an area that all users of the database can connect to, and just keep the path to the file in your database, eg: "\\MyServer\Pictures\Picture1.jpg". Which method you use is up to you.

    One final thing to think about, if you are storing files in the database you may want to create a text field too, so that you can store the name of the file. This way, when you get the file back out again you can save it with the original file name - otherwise you will have to make up the name (or let the user do it), but if you don't use the right extension (eg .jpg) you wont be able to open it!


    Anyway, on to the part you came here for... A tutorial by Beacon to store images in an Access database can be found in the attachment at the bottom of the first post in the thread Tutorials and Tips, which is now also shown in the post below!
    Last edited by si_the_geek; Apr 26th, 2006 at 10:44 AM.

  2. #2

    Thread Starter
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Database - How can I store images (or other files) in a database?

    With Beacon's permission, the following is a re-production of his attachment in the Tutorials and Tips thread, which is for VB6 (or VB5/VBA).

    Please note that the code can be used for any database system, not just Access - you just need to change the data type of the Picture field to a binary format, and use your usual connection string instead of the one posted.



    G’day so you want a way to store your happy snaps in a Access Database. Well here’s your answer, a simple tutorial on how to store pictures in a Access Database!

    It is relatively simple but does recquire a minimal knowledge of databases. Hopefully in the next few paragraphs you’ll learn how to do it. If not well I failed badly in my objective.

    Note: This will be done in MS Access 97. Why? Because…
    Also this is to store the image not just the path, this is only recommended for small to medium sized databases.

    STEP 1 - CREATING THE DATABASE

    Load Access and make a database call it images.mdb for the sake of it.

    Go into design view and construct a table with 3 fields.

    Code:
    Field Name			Type
    PicID				Number
    Description			Text
    Picture				OLE *
    * Note: for SQL Server (2000 or earlier), the data type needed for the Picture field is Image (which stores large binary data) rather than OLE.
    For SQL Server (2005 or later), the data type you should use is
    varbinary(max)
    For anything other than Access or SQL Server, see the documentation of your database system to find the data type (you want something like "large binary" or "BLOB").


    Save the table and call it what you want for the sake of this tutorial I called mine Table1.

    Great you have now constructed the table needed to house the Information. Proceed to Step 2.


    STEP 2 - CREATING THE FORM

    Load Visual Basic and create a standard .exe.

    Once the form has loaded you will need too put:

    4 command buttons. cmdPrevious, cmdNext, cmdDelete and cmdAdd
    1 text box: txtDescription
    1 image box: image1 and
    add the Microsoft Common Dialog Control from the components Menu call it dlgAdd.

    Your Form should now look something similar to this:



    If so you're on your way to Image Heaven, if not go straight to hell.


    STEP 3 - THE CODE:

    The code is pretty well straight forward and doesn’t need much explaining.

    Firstly declare these:
    VB Code:
    1. Option Explicit
    2.  
    3. Private cn As ADODB.Connection
    4. Private rs As ADODB.Recordset
    This is for the connection to the database.

    Now on the form load event add this code.
    VB Code:
    1. Private Sub Form_Load()
    2.    
    3.     Set cn = New ADODB.Connection
    4. 'make this the path to the image database.
    5.     cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;" & _
    6.       "Data Source= f:\image_library\images.mdb"
    7.     cn.Open
    8.    
    9.    
    10.     Set rs = New ADODB.Recordset
    11.     rs.Open "Table1", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    12.        
    13.    
    14.     FillFields 'this is a sub that I’ll explain later.
    15. End Sub
    On the cmdPrevious button have this:
    VB Code:
    1. Private Sub cmdPrevious_Click()
    2.       'making txtdescription show whats in the description field
    3.     rs.Fields("Description") = txtDescription.Text  
    4.  
    5.    
    6.     rs.MovePrevious
    7.       'just to make sure your at the start and so you don’t get that error.
    8.     If rs.BOF Then rs.MoveFirst
    9.    
    10.     FillFields
    11. End Sub
    On cmdNext have this:
    VB Code:
    1. Private Sub cmdNext_Click()
    2.       'making txtdescription show whats in the description field
    3.     rs.Fields("Description") = txtDescription.Text
    4.    
    5.     rs.MoveNext
    6.     If rs.EOF Then rs.MoveLast
    7.    
    8.     FillFields
    9.    
    10. If rs.EOF Then
    11.     MsgBox "At last record" 'avoiding that nasty error.
    12. End If
    13. End Sub
    On cmdDelete do this:
    VB Code:
    1. Private Sub cmdDelete_Click() 'this will delete the entire record.
    2.     If Not (rs.BOF And rs.EOF) Then
    3.         rs.Delete
    4.         If Not (rs.BOF And rs.EOF) Then
    5.             rs.MoveNext
    6.             If rs.EOF Then rs.MoveLast
    7.             FillFields
    8.         End If
    9.     End If
    10. End Sub
    Ok now the good one adding the image to the db!
    On cmdAdd have this:
    VB Code:
    1. Private Sub cmdAdd_Click()
    2.     Dim bytData() As Byte
    3.     Dim strDescription As String
    4.    
    5.     On Error GoTo err
    6.    
    7.     With dlgAdd 'remember the common dialog box.
    8.           'filtering so only jpg’s and gifs are shown!
    9.         .Filter = "Picture Files (*.jpg, *.gif)|*.jpg;*.gif"
    10.         .DialogTitle = "Select Picture"  'sets the title of it.
    11.         .ShowOpen  'show the open dialog box.
    12.            
    13.           'bit to “convert” the image to binary.
    14.         Open .FileName For Binary As #1  
    15.         ReDim bytData(FileLen(.FileName))
    16.     End With
    17.      
    18.     Get #1, , bytData
    19.     Close #1
    20.    
    21.       'show a input box to enter description to the description field.
    22.     strDescription = InputBox("Enter description.", "New Picture")    
    23.    
    24.     With rs
    25.         .AddNew
    26.         .Fields("Description") = strDescription  'adding record to db
    27.         .Fields("Picture").AppendChunk bytData  'adding the picture to the db
    28.         .Update
    29.     End With
    30.    
    31.     FillFields
    32.     Exit Sub  
    33. err:
    34.     If err.Number = 32755 Then     'simple error check.
    35.     Else
    36.         MsgBox err.Description
    37.         err.Clear
    38.     End If
    39. End Sub
    Here’s the all important fillfields sub:
    VB Code:
    1. Public Sub FillFields()
    2.     If Not (rs.BOF And rs.EOF) Then
    3.         txtDescription.Text = rs.Fields("Description")
    4.         Set Image1.DataSource = rs 'setting image1’s datasource
    5.         Image1.DataField = "Picture" 'set its datafield.
    6.     End If
    7. End Sub
    And lastly on form unload:
    VB Code:
    1. Private Sub Form_Unload(Cancel As Integer)
    2.     On Error Resume Next
    3.    
    4.     rs.Close
    5.     cn.Close
    6.     Set rs = Nothing
    7.     Set cn = Nothing 'closing all connections.
    8. End Sub

    Now press Run and add in a image you should now get something similar to this:



    Now you have a basic Image Library to store all your happy snaps in.
    Any problems don’t see me!

    Good Morning, Good Afternoon and if I don’t see you in the Forums Good Night!
    Last edited by si_the_geek; Jun 23rd, 2007 at 08:40 AM. Reason: added data type for SQL Server 2005+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width