catti
asked on
Get images from access database to vb application
Hi!
I've got an Access database with bitmaps that I want to show in an image-control i my application. What's the code for that? I've tried to bind it to my adodc control as I've done with my other controls but it doesn't work.
/catti
I've got an Access database with bitmaps that I want to show in an image-control i my application. What's the code for that? I've tried to bind it to my adodc control as I've done with my other controls but it doesn't work.
/catti
'2nd:Save/Retrieve Image From SQL Server Database Using Ado 2.5 Stream Object
'*************************
'* 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
'*************************
'* 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
ASKER
Hi MyLim!
I've tried your solution, but in the statement
Image1.Picture = LoadPicture("C:\temp.bmp")
i get an error; Invalid picture.
Iv'e checked that the file temp.bmp i created at C: but it's not possible to open it. I get a message that it's not allowed to preview.
What can be the problem?
Thankful for help
/catti
I've tried your solution, but in the statement
Image1.Picture = LoadPicture("C:\temp.bmp")
i get an error; Invalid picture.
Iv'e checked that the file temp.bmp i created at C: but it's not possible to open it. I get a message that it's not allowed to preview.
What can be the problem?
Thankful for help
/catti
thinking
Invalid Picture
Only an icon, bitmap, or Windows metafile can be assigned to the Picture property or Picture object.
You attempted to assign a file to a Picture property or a Picture object that is not recognized as an icon, bitmap, or Microsoft Windows metafile. For the MouseIcon property, the valid pictures are .ICO and .CUR file types.
1.Please make sure your database have picture store.
2.try open your temp.bmp by some other program like bitmap,internet explorer,imaging and etc...
Only an icon, bitmap, or Windows metafile can be assigned to the Picture property or Picture object.
You attempted to assign a file to a Picture property or a Picture object that is not recognized as an icon, bitmap, or Microsoft Windows metafile. For the MouseIcon property, the valid pictures are .ICO and .CUR file types.
1.Please make sure your database have picture store.
2.try open your temp.bmp by some other program like bitmap,internet explorer,imaging and etc...
actually,i have use Adodc1 control and bind to picture box control with no problem,so
wat is ur problem? any error msg ?
wat is ur problem? any error msg ?
ASKER
Yes! When I start the app I get teh message: Unable to bind to field or data member: "bild". This is the name of my field in the access db. I have declared the field as an ActivX-object and inserted a bitmap file.
/catti
/catti
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi MYLim!
That's a lot of code! I didn't think it would be so complicated. Thank you for helping.
/catti
That's a lot of code! I didn't think it would be so complicated. Thank you for helping.
/catti
welcome
q(^ _ ^)p
q(^ _ ^)p
Using the ADO Stream Object to Manage BLOBs
The following is an unedited excerpt from my new book ADO Examples and Best Practices—2nd Edition (Apress) due out this fall. I hope it proves helpful to developers who are struggling with the ADO Chunk methods. Watch http://www.betav.com/ for updates.
Since ADO 2.5, the Stream object has evolved into a pivotal component in the ADO data access paradigm. As we will see in later chapters, streams play an even more critical role in ADO.NET. As developers discover the flexibility and performance of Stream objects, and how well they integrate into ADO, I expect more and more code to include routines driven from Stream IO. The Stream is basically an in-memory data structure that can be used to persist columns, entire Recordsets, or virtually any form of data. Since developers often have to extract BLOB data from Recordsets into memory and immediately save this data to files, ADO’s Stream object is an ideal mechanism for managing these large blocks of binary or text data.
In the following example, you’ll see that in order to fetch a BLOB you simply need to return a Recordset containing the data and use the Stream object to extract the data from the Field.Value property. To save a BLOB, reverse the process, filling the Value property from the Stream. Once you set the Field.Value, all you need to do is use the Recordset Update method to save it—ADO takes care of the rest.
Note: Just before this section, the book discusses why you should avoid putting Binary Large Objects (BLOBs) in the database in the first place. This example is intended for those who have already decided to do so against my advice.
The first routine after the object setup declarations executes a stored procedure that returns all of the Cover images from the database whose titles match a given string.
Option Explicit
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim stm As ADODB.Stream
Dim fso As FileSystemObject
Private Sub btnFindPicture_Click()
If rs.State = adStateOpen Then rs.Close
cn.GetCoverByTitle txtTitleWanted.Text, rs
If rs.RecordCount > 0 Then
GetAndShowPicture rs
If rs.RecordCount > 1 Then
btnNextPicture.Enabled = True
Else
btnNextPicture.Enabled = False
End If
Else
MsgBox "No picture on file for this title"
End If
End Sub
This next routine (GetAndShowPicture) fetches the binary Cover column from the Recordset, and in one operation saves it to a temporary file (destroying any existing temp file by the same name in the process). The routine turns around and immediately uses this file to load an image control used to display the file in a WinForm application.
Sub GetAndShowPicture(rs As ADODB.Recordset)
Dim strTempFilename As String
strTempFilename = App.Path & "\" & fso.GetTempName
Set stm = New ADODB.Stream
With stm
.Open
.Type = adTypeBinary
.Write rs.Fields("Cover").Value
.SaveToFile strTempFilename, adSaveCreateOverWrite
End With
Image1.Picture = LoadPicture(strTempFilenam
Kill strTempFilename
End Sub
The Stream object can also be used to save BLOB data to the database. The following example saves TEXT data from a text file to the database using an updatable Recordset. Frankly, this scenario is a bit contrived as I would be hesitant to use an updatable Recordset for this task—I would much more likely use an UPDATE statement or a stored procedure. However, this is really pretty easy so it does have some appeal, but I also expect the overhead to be somewhat higher than query techniques I’ve already discussed.
The following example (yes, it’s on the CD that comes with the book), opens a file (based on the CommonDialog control), and uses the ADO Stream object to open it. The same stream object is used as a source to pass to the Recordset Value property. Yes, I created an updatable Recordset for this example. Not that I wanted to, but it was necessary for this example.
Option Explicit
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim stm As ADODB.Stream
Dim PubIDFound As Integer
Here we choose a file and use the Stream to open it as an ASCII file. Of course this approach could also be used to read binary files (such as Microsoft Word or Excel documents) just as easily, but you’ll have to change a few of the Stream properties to accommodate the binary file content. In this case I set the Stream Charset property to “ASCII” to reflect that I’m reading files created with Visual Notepad. If you don’t use this option, the Stream defaults to Unicode and the Visual Basic TextBox (and most other “Windows-based” controls don’t know how to display Unicode—so you get lots of “?” characters).
Tip: You can use the Visual Basic “Lightweight” controls to display Unicode
Private Sub btnGetFilename_Click()
With CommonDialog1
.ShowOpen
txtFileName.Text = .FileName
txtComments.FileName = .FileName
End With
Set stm = New ADODB.Stream
With stm
.Charset = "ascii"
.Open
.LoadFromFile txtFileName.Text
.Position = 0
txtComments.Text = .ReadText
End With
Next, the code reads from the Stream and writes to the Value property. Notice that I set the Stream Position property to start reading from the first (0) character of the Stream.
End Sub
Private Sub btnSaveBLOB_Click()
stm.Position = 0
rs.Fields("Comments").Valu
rs.Update
End Sub
'Folks, it does not get much easier than that.