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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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(PictContro
'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).Appen
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.GetCh
Put datafile, , chunk()
For i = 1 To chunks
ReDim Buffer(ChunkSize)
chunk() = oRs!Photo.GetChunk(ChunkSi
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
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")
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")
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
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
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(rstRecor
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(rstRecords
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=**
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("**YourImageFiel
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("***YourImageFie
Image1.Picture = LoadPicture(sFileName)
SavePictureToDB = True
procExitSub:
Exit Function
procNoPicture:
SavePictureToDB = False
GoTo procExitSub
End Function