Link to home
Start Free TrialLog in
Avatar of mansoor25
mansoor25

asked on

Saving Images in Sql database with VB 6.0

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
ASKER CERTIFIED SOLUTION
Avatar of MYLim
MYLim

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MYLim
MYLim

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



https://www.experts-exchange.com/questions/20686823/how-to-store-the-images-using-VB-COM-dll-into-Oracle-database-let-mwknow.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 
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