Solved

Saving Images in Sql database with VB 6.0

Posted on 2003-11-20
5
516 Views
Last Modified: 2013-12-25
Dear Experts,

I want to add images in the Sql Server Database Table with Field Type Image, Please help sort out this problem. I shall be very thankful to all of you.

Thanks,

Mansoor Alam
Lahore.
Pakistan
0
Comment
Question by:mansoor25
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
MYLim earned 60 total points
ID: 9794691
1st Post:
Using the ADO Stream Object to Manage BLOBs

The following is an unedited excerpt from my new book ADO Examples and Best Practices—2nd  Edition (Apress) due out this fall. I hope it proves helpful to developers who are struggling with the ADO Chunk methods. Watch http://www.betav.com/ for updates.

 

Since ADO 2.5, the Stream object has evolved into a pivotal component in the ADO data access paradigm. As we will see in later chapters, streams play an even more critical role in ADO.NET. As developers discover the flexibility and performance of Stream objects, and how well they integrate into ADO, I expect more and more code to include routines driven from Stream IO. The Stream is basically an in-memory data structure that can be used to persist columns, entire Recordsets, or virtually any form of data. Since developers often have to extract BLOB data from Recordsets into memory and immediately save this data to files, ADO’s Stream object is an ideal mechanism for managing these large blocks of binary or text data.

In the following example, you’ll see that in order to fetch a BLOB you simply need to return a Recordset containing the data and use the Stream object to extract the data from the Field.Value property. To save a BLOB, reverse the process, filling the Value property from the Stream. Once you set the Field.Value, all you need to do is use the Recordset Update method to save it—ADO takes care of the rest.

Note: Just before this section, the book discusses why you should avoid putting Binary Large Objects (BLOBs) in the database in the first place. This example is intended for those who have already decided to do so against my advice.

The first routine after the object setup declarations executes a stored procedure that returns all of the Cover images from the database whose titles match a given string.

Option Explicit

Dim cn As ADODB.Connection

Dim cmd As ADODB.Command

Dim rs As ADODB.Recordset

Dim stm As ADODB.Stream

Dim fso As FileSystemObject

 

Private Sub btnFindPicture_Click()

If rs.State = adStateOpen Then rs.Close

cn.GetCoverByTitle txtTitleWanted.Text, rs

If rs.RecordCount > 0 Then

    GetAndShowPicture rs

    If rs.RecordCount > 1 Then

        btnNextPicture.Enabled = True

    Else

        btnNextPicture.Enabled = False

    End If

Else

    MsgBox "No picture on file for this title"

End If

End Sub

 

This next routine (GetAndShowPicture) fetches the binary Cover column from the Recordset, and in one operation saves it to a temporary file (destroying any existing temp file by the same name in the process). The routine turns around and immediately uses this file to load an image control used to display the file in a WinForm application.

Sub GetAndShowPicture(rs As ADODB.Recordset)

Dim strTempFilename As String

strTempFilename = App.Path & "\" & fso.GetTempName

 

Set stm = New ADODB.Stream

With stm

    .Open

    .Type = adTypeBinary

    .Write rs.Fields("Cover").Value

    .SaveToFile strTempFilename, adSaveCreateOverWrite

   

End With

Image1.Picture = LoadPicture(strTempFilename)

Kill strTempFilename

End Sub

 

The Stream object can also be used to save BLOB data to the database. The following example saves TEXT data from a text file to the database using an updatable Recordset. Frankly, this scenario is a bit contrived as I would be hesitant to use an updatable Recordset for this task—I would much more likely use an UPDATE statement or a stored procedure. However, this is really pretty easy so it does have some appeal, but I also expect the overhead to be somewhat higher than query techniques I’ve already discussed.

The following example (yes, it’s on the CD that comes with the book), opens a file (based on the CommonDialog control), and uses the ADO Stream object to open it. The same stream object is used as a source to pass to the Recordset Value property. Yes, I created an updatable Recordset for this example. Not that I wanted to, but it was necessary for this example.

Option Explicit

Dim cn As ADODB.Connection

Dim cmd As ADODB.Command

Dim rs As ADODB.Recordset

Dim stm As ADODB.Stream

Dim PubIDFound As Integer

 

Here we choose a file and use the Stream to open it as an ASCII file. Of course this approach could also be used to read binary files (such as Microsoft Word or Excel documents) just as easily, but you’ll have to change a few of the Stream properties to accommodate the binary file content. In this case I set the Stream Charset property to “ASCII” to reflect that I’m reading files created with Visual Notepad. If you don’t use this option, the Stream defaults to Unicode and the Visual Basic TextBox (and most other “Windows-based” controls don’t know how to display Unicode—so you get lots of “?” characters).

Tip: You can use the Visual Basic “Lightweight” controls to display Unicode

Private Sub btnGetFilename_Click()

With CommonDialog1

    .ShowOpen

    txtFileName.Text = .FileName

    txtComments.FileName = .FileName

End With

Set stm = New ADODB.Stream

With stm

     .Charset = "ascii"

     .Open

     .LoadFromFile txtFileName.Text

     .Position = 0

    txtComments.Text = .ReadText

End With

 

Next, the code reads from the Stream and writes to the Value property. Notice that I set the Stream Position property to start reading from the first (0) character of the Stream.

End Sub

Private Sub btnSaveBLOB_Click()

    stm.Position = 0

    rs.Fields("Comments").Value = stm.ReadText  ' Read from Stream into the Column

    rs.Update

End Sub

 

Folks, it does not get much easier than that.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9794701
2nd:
Save/Retrieve Image From SQL Server Database Using Ado 2.5 Stream Object
Author: Michael P. Gerety
Category: Database
Type: Snippets
Difficulty: Intermediate


Version Compatibility:  Visual Basic 6  


More information: *** NOTE *** You MUST Use ActiveX Data Objects 2.5 or HIGHER

I had been trying to use the AppendChunk()/GetChunk() functions with SQL Server 7.0 to no avail as when I did a select statement on my table which housed a column of type "Image", the dataType and Format were "unsupported" by VB.


Using the ADO Stream object it is much easier to get/retrieve image data from a SQL Server Database. Below is a very simplified version of my code.

This code has been viewed 28121 times.

===============================================
'***************************************************************
'* Save/Retrieve Image Data From SQL Server DataBase Using
'* ADOStream Objects.
'***************************************************************
'* Code By: Michael P. Gerety
'***************************************************************

Dim rstRecordset As ADODB.Recordset
Dim cnnConnection As ADODB.Connection
Dim strStream As ADODB.Stream

'*Setup:
'*Create a form and place 3 command buttons named:
'*cmdLoad, cmdSelectSave, and cmdClear
'*Place a CommonDialog Control Named Dialog
'*Place an ImageBox (or PictureBox) named Image1


'** The field type in Sql Server must be "Image"
'** Everywhere you see "***" in the code is where you must enter
'** your own data.

Private Sub cmdClear_Click()
    Image1.Picture = Nothing
   
End Sub

Private Sub cmdLoad_Click()
    If Not LoadPictureFromDB(rstRecordset) Then
        MsgBox "Invalid Data Or No Picture In DB"
    End If
End Sub

Private Sub cmdSelectSave_Click()
    'Open Dialog Box
    With dlgDialog
        .DialogTitle = "Open Image File...."
        .Filter = "Image Files (*.gif; *.bmp)| *.gif;*.bmp"
        .CancelError = True
procReOpen:
         .ShowOpen
         
        If .FileName = "" Then
            MsgBox "Invalid filename or file not found.", _
                vbOKOnly + vbExclamation, "Oops!"
            GoTo procReOpen
        Else
            If Not SavePictureToDB(rstRecordset, .FileName) Then
                MsgBox "Save was unsuccessful :(", vbOKOnly + _
                        vbExclamation, "Oops!"
                Exit Sub
            End If
        End If
           
    End With
End Sub

Private Sub Form_Load()
    Set cnnConnection = New ADODB.Connection
    Set rstRecordset = New ADODB.Recordset

    cnnConnection.Open ("Provider=SQLOLEDB; " & _
        "data Source=**YourServer**;" & _
        "Initial Catalog=**YourDatabase**; " & _
        "User Id=**YourUID**;Password=***YourPass***")
    rstRecordset.Open "Select * from YourTable", cnnConnection, _
         adOpenKeyset, adLockOptimistic
   

End Sub


Public Function LoadPictureFromDB(RS As ADODB.Recordset)

    On Error GoTo procNoPicture
   
    'If Recordset is Empty, Then Exit
    If RS Is Nothing Then
        GoTo procNoPicture
    End If
   
    Set strStream = New ADODB.Stream
    strStream.Type = adTypeBinary
    strStream.Open
   
    strStream.Write RS.Fields("**YourImageField**").Value

   
    strStream.SaveToFile "C:\Temp.bmp", adSaveCreateOverWrite
    Image1.Picture = LoadPicture("C:\Temp.bmp")
    Kill ("C:\Temp.bmp")
    LoadPictureFromDB = True

procExitFunction:
    Exit Function
procNoPicture:
    LoadPictureFromDB = False
    GoTo procExitFunction
End Function

Public Function SavePictureToDB(RS As ADODB.Recordset, _
    sFileName As String)

    On Error GoTo procNoPicture
    Dim oPict As StdPicture
   
    Set oPict = LoadPicture(sFileName)
   
    'Exit Function if this is NOT a picture file
    If oPict Is Nothing Then
        MsgBox "Invalid Picture File!", vbOKOnly, "Oops!"
        SavePictureToDB = False
        GoTo procExitSub
    End If
   
    RS.AddNew
   

    Set strStream = New ADODB.Stream
    strStream.Type = adTypeBinary
    strStream.Open
    strStream.LoadFromFile sFileName
    RS.Fields("***YourImageField***").Value = strStream.Read
   
    Image1.Picture = LoadPicture(sFileName)
    SavePictureToDB = True
   
   

procExitSub:
    Exit Function
procNoPicture:
    SavePictureToDB = False
    GoTo procExitSub
End Function


0
 
LVL 1

Expert Comment

by:VB-Expert
ID: 9805027

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20686823.html

The code I have included focuses around SQL Server 2000 and Access.  With the methods using a stream, the code can be applied anywhere as it is generic, and uses the parameters passed to it.
Appendchunk & Getchunk both require extra lines of coding, and also make the code less user friendly.  A lot of documenting is used when useing both Appendchunk & Getchunk functions.

'If you're using ADO 2.5 or better, use the Stream object instead of Getchunk/Appendchunk:

'There are 2 methods by which this can be achieved,
'Getchunk(),Appendchunk() or a Stream.

'Using a stream make the code cleaner & easier to manage.

'Included are both methods:

'AppendChunk() Write to the file

'The database can store the picture as an object using the following code

Public Function SavePictureToDB(PictControl As Object, rs As Object, FieldName As String) As Boolean

  'PURPOSE: SAVES PICTURE IN IMAGEBOX, PICTUREBOX, OR SIMILAR
  'CONTROL TO RECORDSET RS IN FIELD NAME FIELDNAME
 
   'FIELD TYPE MUST BE BINARY (OLE OBJECT IN ACCESS)
     
  Dim oPict As StdPicture
 
   Dim sDir As String
  Dim sTempFile As String
  Dim iFileNum As Integer
  Dim lFileLength As Long
 
   Dim abBytes() As Byte
  Dim iCtr As Integer
 
   On Error GoTo ErrorHandler
  If Not TypeOf rs Is ADODB.Recordset Then Exit Function
  Set oPict = PictControl.Picture
  If oPict Is Nothing Then Exit Function
 
   'Save picture to temp file
  sDir = GetTempDir
  If sDir = "" Then sDir = "C:\"
  sTempFile = sDir & "0X2341KLZX.dat"
  SavePicture oPict, sTempFile
 
   'read file contents to byte array
  iFileNum = FreeFile
  Open sTempFile For Binary Access Read As #iFileNum
  lFileLength = LOF(iFileNum)
  ReDim abBytes(lFileLength)
  Get #iFileNum, , abBytes()
  'put byte array contents into db field
  rs.Fields(FieldName).AppendChunk abBytes()
  Close #iFileNum
 
   'Don't return false if file can't be deleted
  On Error Resume Next
  Kill sTempFile
  SavePictureToDB = True
ErrorHandler:
  Close #iFileNum
  'MsgBox Err.Description
End Function


'The code below uses the Getchunk() to write this to a file.

'GetChunk() Read from the file

Private Sub List1_Click()
  Dim chunk()             As Byte
  datafile = 1
  Const ChunkSize = 16384
  sSql = "SELECT * FROM cars WHERE ID = " & List1.Text
  oRs.Open sSql, oCOn, adOpenDynamic, adLockOptimistic
  Open "pictemp" For Binary Access Write As datafile
  FL = oRs!Photo.ActualSize
  chunks = FL \ ChunkSize
  Fragment = FL Mod ChunkSize
  ReDim chunk(Fragment)
  chunk() = adoPrimaryRS!Picture.GetChunk(Fragment)
  Put datafile, , chunk()
  For i = 1 To chunks
     ReDim Buffer(ChunkSize)
     chunk() = oRs!Photo.GetChunk(ChunkSize)
     Put datafile, , chunk()
  Next i
  Close datafile
  FileName = "pictemp"
  Picture1.Picture = LoadPicture(FileName)
End Sub

OR

The coding below is generic and can be used in any situation.

Public Sub SaveFileToField(FieldName As String, FileName As String, ConnectionString As String)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim mstream As ADODB.Stream

  Set cn = New Connection
  cn.open ConnectionString

  Set mstream = New Stream
  mstream.Type = adTypeBinary
  mstream.open

  mstream.LoadFromFile FileName
  rs.fields(FieldName).Value = mstream.read
  rs.Update

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing
End Sub

Public Sub SaveFieldToFile(FieldName As String, FileName As String, ConnectionString As String)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim mstream As ADODB.Stream

  Set cn = New Connection
  cn.open ConnectionString

  Set mstream = New Stream
  mstream.Type = adTypeBinary
  mstream.open

  mstream.write rs.fields(FieldName).Value
  mstream.SaveToFile FileName, adSaveCreateOverwrite

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing
End Sub


OR


''The code below uses a stream to read the picture from the database and then write this to a file

'Write the picture stored in the Access Database to a temporary file

'Streams - Read from the file

Dim mstream As ADODB.Stream

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write adoAccRS.Fields("picture").Value
mstream.SaveToFile "C:\<Filename>.jpg",  adSaveCreateOverWrite
mstream.Close
       
'Read the picture stored in from the Temporary file  & Write to the SQL Database
strFilename = "C:\<Filename>.jpg"
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "C:\<Filename>.jpg"
adoSQLRS.Fields("Picture").Value = mstream.Read    'Write the value held in the stream to the SQL Picture field
adoSQLRS.Update
mstream.Close
       
'Using a Stream makes the code a lot cleaner and more efficient.

http://www.betav.com/Files/Content/Whitepapers/Using%20the%20ADO%20Stream%20Object%20to%20Manage%20BLOBs.htm
0
 
LVL 1

Expert Comment

by:ayufans
ID: 10512095
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- answered by MYLim

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now