Image Storage using VB and SQL Server

(1) Open and read Binary File + (2) Insert Data into SQL
--------------------------------------------------------

Sub InsertImage(strFileName As String)

' Parameter Description :
' strFileName : Path to the Imagefile to insert

Private Const ChunkSize = 32768
Private Const TableName = "MyImages"
Private Const FieldName = "Picture"
Private Const DSN = ".....your DSN goes here...."
Private R AS ADODB.Recordset
Private B() As Byte
Private F AS Integer
Private Blocks as Long
Private Fragment AS Long
Private X AS Long

On Local Error Resume Next

Set R = New ADODB.Recordset
R.Open TableName, strDSN, adOpenDynamic, adLockOptimistic, adCmdTable
R.AddNew
F = FreeFile
Open strFileName For Binary Access Read As #F
Blocks = LOF(F) \ ChunkSize
Fragment = LOF(F) Mod ChunkSize
Redim B(Fragment)
Get #F,,B()
R.Fields(FieldName).AppendChunk B()
Redim B(ChunkSize)
For X = 1 To Blocks
 Get #F,,B()
 R.Fields(FieldName).AppendChunk B()
Next X
Close #F
Erase B
R.Close
Set R = Nothing

End Sub

(3) Retrieve Data from SQL
--------------------------------------------------------

Retrieving the Data is straightforward :

Const FName = "c:\mypic.gif"
Dim V As Variant
Dim SQL AS String
Dim DBC AS New ADODB.Connection
Dim R AS ADODB.Recordset
Dim F AS Integer

DBC.Open "...Your DSN..."
SQL = "SELECT Picture FRON MyImages"
Set R = DBC.Execute(SQL)
V = R.Fields(0)
R.Close
Set R = Nothing
DBC.Close
Set DBC = Nothing
F = FreeFile
Open FName For Binary Access Write As #F
Put #F,,V
Close #F
Erase V

Here is an example straight from the help to do this.

Public Sub AppendChunkX()

  Dim cnn1 As ADODB.Connection
  Dim rstPubInfo As ADODB.Recordset
  Dim strCnn As String
  Dim strPubID As String
  Dim strPRInfo As String
  Dim lngOffset As Long
  Dim lngLogoSize As Long
  Dim varLogo As Variant
  Dim varChunk As Variant
 
   Const conChunkSize = 100

  ' Open a connection.
  Set cnn1 = New ADODB.Connection
     strCnn = "Provider=sqloledb;" & _
     "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
  cnn1.Open strCnn
 
   ' Open the pub_info table.
  Set rstPubInfo = New ADODB.Recordset
  rstPubInfo.CursorType = adOpenKeyset
  rstPubInfo.LockType = adLockOptimistic
  rstPubInfo.Open "pub_info", cnn1, , , adCmdTable
 
   ' Prompt for a logo to copy.
  strMsg = "Available logos are : " & vbCr & vbCr
  Do While Not rstPubInfo.EOF
     strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
        Left(rstPubInfo!pr_info, InStr(rstPubInfo!pr_info, ",") - 1) & _
        vbCr & vbCr
     rstPubInfo.MoveNext
  Loop
  strMsg = strMsg & "Enter the ID of a logo to copy:"
  strPubID = InputBox(strMsg)
 
   ' Copy the logo to a variable in chunks.
  rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
  lngLogoSize = rstPubInfo!logo.ActualSize
  Do While lngOffset < lngLogoSize
     varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
     varLogo = varLogo & varChunk
     lngOffset = lngOffset + conChunkSize
  Loop
 
   ' Get data from the user.
  strPubID = Trim(InputBox("Enter a new pub ID:"))
  strPRInfo = Trim(InputBox("Enter descriptive text:"))
 
   ' Add a new record, copying the logo in chunks.
  rstPubInfo.AddNew
  rstPubInfo!pub_id = strPubID
  rstPubInfo!pr_info = strPRInfo

  lngOffset = 0 ' Reset offset.
  Do While lngOffset < lngLogoSize
     varChunk = LeftB(RightB(varLogo, lngLogoSize - lngOffset), _
        conChunkSize)
     rstPubInfo!logo.AppendChunk varChunk
     lngOffset = lngOffset + conChunkSize
  Loop
  rstPubInfo.Update
 
    ' Show the newly added data.
  MsgBox "New record: " & rstPubInfo!pub_id & vbCr & _
     "Description: " & rstPubInfo!pr_info & vbCr & _
     "Logo size: " & rstPubInfo!logo.ActualSize

  ' Delete new record because this is a demonstration.
  rstPubInfo.Requery
  cnn1.Execute "DELETE FROM pub_info " & _
     "WHERE pub_id = '" & strPubID & "'"

  rstPubInfo.Close
  cnn1.Close  

End Sub

Dhaest, I have considered the implications of storing images in the database.

The thing is we need to store them this way as the images will be distributed across an internal and external network.

I will have a look at the examples and some of the links.

V.