Retrieve and Save Images in SQL 7

Category:

Database

Type:

Applications

Difficulty:

Advanced

Author:

Sunil Kumar Patil

Version Compatibility: Visual Basic 6

More information:

In this project, you will see how to save and retrieve a .bmp, .jpg or any Image file in a SQL 7 database.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
'******************************************************* '* Save/Retrieve Image Data From SQL Server DataBase Using '* ADOStream Objects. '*************************************************************** '* Design & Develop By : Sunil Kumar Patil Indore (India) +91 (0) 98261 72709 ' : kumars321@rediffmail.com '*************************************************************** ' What you have to do just follow the setup '*Setup: '*Create a form and place 6 command buttons named: '*cmdLoad, cmdSelectSave, cmdClear, cmdExit, cmdPrv, cmdNext '*Place a CommonDialog Control Named Dialog '*Place an ImageBox (or PictureBox) named Image1 and set the property Stretch as True '*----------------------------------------------- ' References '*Ms Activex data objects 2.5 or higher '*Ms Activex data objects record ' components '*Ms common dialog contorl box 6 '*----------------------------------------------- ' Now Create a table in sql called PHOTOTEST ' there are two fiels i.e. ' 1. i_id int ' 2. imgvc_photo image '** The field imgvc_photo type in Sql Server must be "Image" Dim rstRecordset As ADODB.Recordset Dim cnnConnection As ADODB.Connection Dim strStream As ADODB.Stream Const Imagefield = "imgvc_photo"

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 cmdNext_Click()
    If Not rstRecordset.EOF Then
        rstRecordset.MoveNext
    Else
        rstRecordset.MoveFirst
    End If
    LoadPictureFromDB rstRecordset
End Sub

Private Sub cmdPrv_Click()
    If Not rstRecordset.BOF Then
        rstRecordset.MovePrevious
    Else
        rstRecordset.MoveLast
    End If
    LoadPictureFromDB rstRecordset
End Sub

Private Sub cmdSelectSave_Click()
    'Open Dialog Box
    With dlgDialog
        .DialogTitle = "Open Image File...."
        .Filter = "Image Files (*.gif; *.bmp;*.jpg)| *.gif;*.bmp;*.jpg"
        .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 cmdExit_Click()
    End
End Sub

Private Sub Form_Load()
    Dim DataBase As String
    Dim Servername As String
    Dim TableName As String
    DataBase = "DECCAN"
    Servername = "SUNIL"
    TableName = "PHOTOTEST"
    
    Set cnnConnection = New ADODB.Connection
    Set rstRecordset = New ADODB.Recordset
    cnnConnection.Open ("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog='" & DataBase & "';Data Source=" & Servername)
    
    rstRecordset.Open "Select * from " & TableName, cnnConnection, adOpenKeyset, adLockOptimistic
    LoadPictureFromDB rstRecordset
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(Imagefield).Value
    Text1 = RS!I_ID
    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(Imagefield).Value = strStream.Read
    
    Image1.Picture = LoadPicture(sFileName)
    SavePictureToDB = True
procExitSub:
    Exit Function
procNoPicture:
    SavePictureToDB = False
    GoTo procExitSub
End Function