ADO and Data source controls (picturebox)

I am having problems working out how to bind a picturebox to a ADO recorset to save and load pictures to a database.

I have tried the following:


Option Explicit
Dim cnnCurtID As ADODB.Connection
Dim rstID As New ADODB.Recordset

Private Sub cmdExit_Click()
Unload Me
End Sub

Private Sub cmdLoad_Click()
If Not IsNumeric(Text1) Then Exit Sub
If rstID.State = adStateOpen Then rstID.Close
rstID.Open "SELECT * FROM ID WHERE MemberNo = " & txtMemNo & " ;", cnnCurtID, adOpenStatic, adLockOptimistic
If Not rstID.EOF Then
Set Signature.DataSource = rstID
Signature.DataField = "Signature"
End If
End Sub

Private Sub cmdSave_Click()
If Not IsNumeric(txtMemNo) Then Exit Sub
If rstID.EOF = True Then
rstID.AddNew
End If
rstID!MemberNo = CLng(txtMemNo)
rstID.Update
End Sub

Private Sub cmdSignature_Click()
If BrowseToFile(CD, "Signature.bmp", "Bitmap files (*.bmp)|*.bmp|JPeg files (*.jpg)|*.jpg", "Open Signature file", , App.Path) = False Then
MsgBox "Signature import cancelled", vbInformation + vbOKOnly, "Error"
Exit Sub
End If
Signature.Picture = LoadPicture(CD.FileName)
cmdSave.Enabled = True
End Sub

Private Sub Form_Load()
Set cnnCurtID = New ADODB.Connection
cnnCurtID.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\data\CurtID.mdb;Persist Security Info=False"
cnnCurtID.Open
End Sub

Private Sub Form_Unload(Cancel As Integer)
If cnnCurtID.State = adStateOpen Then cnnCurtID.Close
If rstID.State = adStateOpen Then rstID.Close
Set cnnCurtID = Nothing
Set rstID = Nothing
End Sub

Private Sub txtMemNo_Change()
txtMemNo.SelStart = 0
txtMemNo.SelLength = Len(txtMemNo)
If Not IsNumeric(txtMemNo) Then Exit Sub
If rstID.State = adStateOpen Then rstID.Close
rstID.Open "SELECT * FROM ID WHERE MemberNo = " & txtMemNo & " ;", cnnCurtID, adOpenStatic, adLockOptimistic
If Not rstID.EOF Then
lblEOF.Visible = False
Set Signature.DataSource = rstID
Signature.DataField = "Signature"
Else
lblEOF.Visible = True
End If
End Sub

Private Sub txtMemNo_GotFocus()
txtMemNo.SelStart = 0
txtMemNo.SelLength = Len(txtMemNo)
End Sub


Which works in a similar way to how I used to do it with a DataControl. However this does not seem to work in this case.

Any suggestions would be appreciated, as I am sure that I am just missing soneting simple.

If you are looking to load images into an Access database and retrieve them, I suggest reviewing this tutorial..it shows you a great method of doing this.

http://www.xtremevbtalk.com/showthread.php?t=11080

I would also suggest only storing the name and path of the image in the database. Storing images will cause the database to grow to a huge size, which will cause many different problems and could lead to corruption.

I have used that method for another part of the program where I have had the option, but I have been told I have to use a database and store the objects as an OLE object as we did using the datacontrol. There will also be OLE bitmaps stored in the database that we will have to be able to restore. So unfortunately I don't think I will be able to use this method.

Is there any way to save and restore pictures using ADO and DataSources?

First off I wouldn't use any ADODC or data controls for this. Use ADO connection and recordset objects. And, what within that tutorial example can't you use?

This is using ADO to connect to an Access database and retrieve an image. It also show you how to add a new image.

Maby i am wrong. I just thought that this metod wouldn't work when trying to load images stored using another method (i.e. DataControls with bound datafields). I will try it that way then, esspecialy as I have the coding for it.

Thanks for the reply.

:)