insert files into database

[winnie]

HI everyone..

I am trying to find a way to insert a file (e.g. jpg, gif or Microsoft Word) into a Database (preferably SQL). I can't seem to find the method to do it. I know that the column in the SQL table has to be of type 'image'. But I am not sure how to 'open' the file wanted, read it, and pass it into the table.

I am also not very sure how to take these files from database and display it.

Any help will be greatly appreciated.

Thanks

Vincent

[Phil McKracken]

I got this out of the MSDN it's for Oracle but it sould work if you change it a little

' This application demonstrates using ADO with the AppendChunk
' and GetChunk methods against an Oracle 7.3 database.
'
Option Explicit
Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim strConn As String
Dim strSQL As String
Dim FileLength As Long 'Used in Command1 and Command2 procedures.
Dim Numblocks As Integer
Dim LeftOver As Long
Dim i As Integer
Const BlockSize = 100000 'This size can be experimented with for
'performance and reliability.

Private Sub Form_Load()
Command1.Caption = "AppendChunk"
Command2.Caption = "GetChunk"
Command3.Caption = "Exit"
Command2.Enabled = False

'Make Connection
Set Cn = New ADODB.Connection
strConn = "UID=MyUID;PWD=MyPassword;" & _
"driver={Microsoft ODBC for Oracle};" & _
"SERVER=MyServer;"
Cn.Open strConn
Debug.Print Cn.ConnectionString

End Sub

Public Sub Command1_Click()
' AppendChunk button
' This procedure prompts for a BMP file,
' converts that file to a Byte array,
' and saves the Byte Array to the table
' using the Appendchunk method.
'
Dim PictBmp As String
Dim ByteData() As Byte 'Byte array for Blob data.
Dim SourceFile As Integer

' Open the BlobTable table.
strSQL = "Select MyID, BLOBfld from BLOBTABLE WHERE MyID = 1"
Set Rs = New ADODB.Recordset
Rs.CursorType = adOpenKeyset
Rs.LockType = adLockOptimistic
Rs.Open strSQL, Cn

' Retrieve the picture and update the record.
CommonDialog1.Filter = "(*.bmp;*.ico)|*.bmp;*.ico"
CommonDialog1.ShowOpen
PictBmp = CommonDialog1.filename
Me.MousePointer = vbHourglass
Me.Caption = "Retrieving the picture"

' Save Picture image to the table column.
SourceFile = FreeFile
Open PictBmp For Binary Access Read As SourceFile

FileLength = LOF(SourceFile) ' Get the length of the file.
Debug.Print "Filelength is " & FileLength

If FileLength = 0 Then

Close SourceFile
MsgBox PictBmp & " empty or not found."
Exit Sub
Else

Numblocks = FileLength / BlockSize
LeftOver = FileLength Mod BlockSize

ReDim ByteData(LeftOver)
Get SourceFile, , ByteData()
Rs(1).AppendChunk ByteData()

ReDim ByteData(BlockSize)
For i = 1 To Numblocks
Get SourceFile, , ByteData()
Rs(1).AppendChunk ByteData()
Next i

Rs.Update 'Commit the new data.

Close SourceFile
End If

Me.Caption = "Picture Retrieved"
Command2.Enabled = True
Me.MousePointer = vbNormal
End Sub

Private Sub Command2_Click()
' GetChunk Button
' This procedure retrieves the picture image
' from the table using the GetChunk method,
' converts the data to a file and
' displays that file in the Picture box.
'
Dim ByteData() As Byte 'Byte array for picture file.
Dim DestFileNum As Integer
Dim DiskFile As String

Me.MousePointer = vbHourglass
Me.Caption = "Creating Picture File"

' Remove any existing destination file.
DiskFile = App.Path & "\image1.bmp"
If Len(Dir$(DiskFile)) > 0 Then
Kill DiskFile
End If

DestFileNum = FreeFile
Open DiskFile For Binary As DestFileNum

Numblocks = FileLength / BlockSize
LeftOver = FileLength Mod BlockSize

ByteData() = Rs(1).GetChunk(LeftOver)
Put DestFileNum, , ByteData()

For i = 1 To Numblocks
ByteData() = Rs(1).GetChunk(BlockSize)
Put DestFileNum, , ByteData()
Next i

Close DestFileNum

Picture1.Visible = True
Picture1.Picture = LoadPicture(App.Path & "\image1.bmp")
Rs.Close
Debug.Print "Complete"
Me.Caption = "Success!"
Me.MousePointer = vbNormal
End Sub

Private Sub Command3_Click()
'Exit button.
Cn.Close
Unload Me
End Sub

Ooh! What a beautiful reply!!
Sos...bit OTT.

I've been messing around for ages trying to get a GIF into an image field in a SQL Server DB. Few mods an the code you found worked.

Am bookmarkin' this page.
Ta

[faisalkm]

i get an error Invalid Conversion