Expert: Srini Nagarajan - 11/27/2004

Question
Hello

I hope you are doing well.

I need to save the image file in SQL server using VB6 and also need to display the image in crystal report 8.5

Please let me know how to do that or it would be a great help if you could provide me with the code example of the solution.

Very Best Regards,
Usman

Answer
saving the image you can use the following code...

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


to show on  the crystal reports here is the site you can get the sample code.

http://vbmysql.com/samplecode/basicblob.html

Happy Programming!

-Sri