Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Unsolved
Views: 9054

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Mar 16th, 2009

VB Add/Edit/Save Pictures in Access DB

Expand Post »

Hi everyone, is it possible to add/edit/save picture in MS Access DB using ADO, I have this code

If (MsgBox("Are you sure you entered correct data?" & vbNewLine & "Save now?", vbYesNo + vbQuestion, "Confirm Save") = vbYes) Then
squery = "": squery = "SELECT * FROM tblEmp"
Call ExecuteCommand
Set rs = New ADODB.Recordset
With rs
.Open squery, conn, adOpenStatic, adLockPessimistic
.AddNew
.Fields("EmpID") = txtEmpID.Text
.Fields("LName") = txtLast.Text
.Fields("FName") = txtFirst.Text
.Fields("MName") = txtMiddle.Text
.Fields("Pos") = txtPos.Text
.Update
End With

I want also to add/save picture together with this data, using Image or PictureBox.

Then I want to retrieve it later on and display the picture within the Form based on the user's QUERY. then is it also possible to display this picture in Data Report when the user click the command PREVIEW.

Thank you very much for your help.
I will highly appreciate it.

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Mar 17th, 2009

Re: VB Add/Edit/Save Pictures in Access DB

please try to use the sample code to store picture.
this sample code uses ADO for database connection

vb Syntax (Toggle Plain Text)
Dim CN As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim DataFile As Integer, Fl As Long, Chunks As Integer
Dim Fragment As Integer, Chunk() As Byte, i As Integer, FileName As String
 
Private Const ChunkSize As Integer = 16384
Private Const conChunkSize = 100
 
Private Sub cmdSave_Click()
    CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Pubs;Data Source=Test"
    Dim strSQL As String
 
    strSQL = "SELECT * FROM pub_info where pub_id = '9999'"
    RS.Open strSQL, CN, adOpenForwardOnly, adLockOptimistic
 
    RS.AddNew
      SavePicture
    RS.Update
 
    Set RS = Nothing
    Set RS = New Recordset
End Sub
 
Private Sub SavePicture()
    Dim strFileNm As String
    DataFile = 1
    Open strFileNm For Binary Access Read As DataFile
        Fl = LOF(DataFile)   ' Length of data in file
        If Fl = 0 Then Close DataFile: Exit Sub
        Chunks = Fl \ ChunkSize
        Fragment = Fl Mod ChunkSize
        ReDim Chunk(Fragment)
        Get DataFile, , Chunk()
        RS!logo.AppendChunk Chunk()
        ReDim Chunk(ChunkSize)
        For i = 1 To Chunks
            Get DataFile, , Chunk()
            RS!logo.AppendChunk Chunk()
        Next i
    Close DataFile
End Sub
Dim CN As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim DataFile As Integer, Fl As Long, Chunks As Integer
Dim Fragment As Integer, Chunk() As Byte, i As Integer, FileName As String

Private Const ChunkSize As Integer = 16384
Private Const conChunkSize = 100

Private Sub cmdSave_Click()
    CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Pubs;Data Source=Test"
    Dim strSQL As String

    strSQL = "SELECT * FROM pub_info where pub_id = '9999'"
    RS.Open strSQL, CN, adOpenForwardOnly, adLockOptimistic

    RS.AddNew
      SavePicture
    RS.Update

    Set RS = Nothing
    Set RS = New Recordset
End Sub

Private Sub SavePicture()
    Dim strFileNm As String
    DataFile = 1
    Open strFileNm For Binary Access Read As DataFile
        Fl = LOF(DataFile)   ' Length of data in file
        If Fl = 0 Then Close DataFile: Exit Sub
        Chunks = Fl \ ChunkSize
        Fragment = Fl Mod ChunkSize
        ReDim Chunk(Fragment)
        Get DataFile, , Chunk()
        RS!logo.AppendChunk Chunk()
        ReDim Chunk(ChunkSize)
        For i = 1 To Chunks
            Get DataFile, , Chunk()
            RS!logo.AppendChunk Chunk()
        Next i
    Close DataFile
End Sub

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Mar 17th, 2009

Re: VB Add/Edit/Save Pictures in Access DB

please try to use the sample code to retrieve picture from database tables.
again this sample code uses ADO for database connection.

vb Syntax (Toggle Plain Text)
Dim CN As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim DataFile As Integer, Fl As Long, Chunks As Integer
Dim Fragment As Integer, Chunk() As Byte, i As Integer, FileName As String
 
Private Const ChunkSize As Integer = 16384
Private Const conChunkSize = 100
 
Private Sub Form_Load()
    CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Pubs;Data Source=Test"
    Dim strsql As String
 
    strsql = "SELECT * FROM pub_info where pub_id = '9999'"
    RS.Open strsql, CN, adOpenForwardOnly, adLockReadOnly
      ShowPic
    Set RS = Nothing
    Set RS = New Recordset
End Sub
 
Private Sub ShowPic()
    DataFile = 1
    Open "pictemp" For Binary Access Write As DataFile
        Fl = RS!logo.ActualSize ' Length of data in file
        If Fl = 0 Then Close DataFile: Exit Sub
        Chunks = Fl \ ChunkSize
        Fragment = Fl Mod ChunkSize
        ReDim Chunk(Fragment)
        Chunk() = RS!logo.GetChunk(Fragment)
        Put DataFile, , Chunk()
        For i = 1 To Chunks
            ReDim Buffer(ChunkSize)
            Chunk() = RS!logo.GetChunk(ChunkSize)
            Put DataFile, , Chunk()
        Next i
    Close DataFile
    FileName = "pictemp"
    Picture1.Picture = LoadPicture(FileName)
End Sub
Dim CN As New ADODB.Connection
Dim RS As ADODB.Recordset
Dim DataFile As Integer, Fl As Long, Chunks As Integer
Dim Fragment As Integer, Chunk() As Byte, i As Integer, FileName As String

Private Const ChunkSize As Integer = 16384
Private Const conChunkSize = 100

Private Sub Form_Load()
    CN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Pubs;Data Source=Test"
    Dim strsql As String

    strsql = "SELECT * FROM pub_info where pub_id = '9999'"
    RS.Open strsql, CN, adOpenForwardOnly, adLockReadOnly
      ShowPic
    Set RS = Nothing
    Set RS = New Recordset
End Sub

Private Sub ShowPic()
    DataFile = 1
    Open "pictemp" For Binary Access Write As DataFile
        Fl = RS!logo.ActualSize ' Length of data in file
        If Fl = 0 Then Close DataFile: Exit Sub
        Chunks = Fl \ ChunkSize
        Fragment = Fl Mod ChunkSize
        ReDim Chunk(Fragment)
        Chunk() = RS!logo.GetChunk(Fragment)
        Put DataFile, , Chunk()
        For i = 1 To Chunks
            ReDim Buffer(ChunkSize)
            Chunk() = RS!logo.GetChunk(ChunkSize)
            Put DataFile, , Chunk()
        Next i
    Close DataFile
    FileName = "pictemp"
    Picture1.Picture = LoadPicture(FileName)
End Sub

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Mar 18th, 2009

Re: VB Add/Edit/Save Pictures in Access DB

good evening:

how could this code be fitted to the above code (to what i have posted)..

for example, in my form i have imgPhoto in the form, having UPLOAD Picture button, then after locating the photo by this code:

Private Sub cmdUpload_Click()
cdPhoto.Filter = "Image Files (*.jpg;*.gif;*.bmp)|*.jpg;*.gif;*.bmp"
cdPhoto.ShowOpen
filepath = cdPhoto.FileName
imgPhoto.Picture = LoadPicture(filepath)
End Sub

by means of this code, i have successfully loaded the picture within the image on the form but for the SAVE BUTTON?

for database access:
i have field Photo defined as "OLE Object" in MS Access Datafield

what should be on the cmdSave button:

together with this,

Set rs = New ADODB.Recordset
With rs
.Open squery, conn, adOpenStatic, adLockPessimistic
.AddNew
.Fields("EmpID") = txtEmpID.Text
.Fields("LName") = txtLast.Text
.Fields("FName") = txtFirst.Text
.Fields("MName") = txtMiddle.Text
.Fields("Photo")=????
.Update

what should be added with this code or anything much simpler than the one you have given?

thank you very much for the reply.

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Mar 19th, 2009

Re: VB Add/Edit/Save Pictures in Access DB

You need to do the following .

1.add all the records except picture
2.update the recordset
3.edit the same record
4.update the picture field with the photo.
5.update the recordset again.

debasisdas

Featured Poster

Reputation Points: 665

Solved Threads: 427

Posting Genius

Offline

6,406 posts
since Feb 2007

Permalink

Mar 19th, 2009

Re: VB Add/Edit/Save Pictures in Access DB

good morning, i have successfully saved the picture together with the data by this code:

Private Sub cmdSave_Click()
Dim picsm As ADODB.Stream
Set picsm = New ADODB.Stream
picsm.Type = adTypeBinary
picsm.Open
picsm.LoadFromFile filepath
With rs
.AddNew
.Fields("Last") = txtLast.Text
.Fields("First") = txtFirst
.Fields("Pic").Value = picsm.Read
imgPic.Picture = LoadPicture(filepath)
.Update
MsgBox "success"
End With
picsm.Close
Set picsm = Nothing
End Sub

but my problem now is on how to retrieve the picture and load it into the image control on the form:

i have this code but it is working with data only, it retrieves the data but not the image.

Private Sub cmdSearch_Click()
On Error GoTo notfound
Dim photo As String
squery = "select * from tblpic where Last='" & txtLast.Text & "'"
Call ExecuteCommand
photo = rs.Fields("pic").Value
With Me
.txtLast = rs!Last
.txtFirst = rs!First
.imgPic.Picture = LoadPicture(photo)
End With
Exit Sub
notfound:
MsgBox "Not Found"

End Sub

any idea?

thank you...

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Feb 24th, 2010

-1

Re: VB Add/Edit/Save Pictures in Access DB

did u manage to do it? arvin2006.
im facing the same problm...
plz let me knw if u hv any idea to do it...
thanx!

onlyonebui

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Feb 2010

Permalink

Feb 24th, 2010

Re: VB Add/Edit/Save Pictures in Access DB

Click to Expand / Collapse Quote originally posted by arvin2006 ...

good morning, i have successfully saved the picture together with the data by this code:

Private Sub cmdSave_Click()
Dim picsm As ADODB.Stream
Set picsm = New ADODB.Stream
picsm.Type = adTypeBinary
picsm.Open
picsm.LoadFromFile filepath
With rs
.AddNew
.Fields("Last") = txtLast.Text
.Fields("First") = txtFirst
.Fields("Pic").Value = picsm.Read
imgPic.Picture = LoadPicture(filepath)
.Update
MsgBox "success"
End With
picsm.Close
Set picsm = Nothing
End Sub

but my problem now is on how to retrieve the picture and load it into the image control on the form:

i have this code but it is working with data only, it retrieves the data but not the image.

Private Sub cmdSearch_Click()
On Error GoTo notfound
Dim photo As String
squery = "select * from tblpic where Last='" & txtLast.Text & "'"
Call ExecuteCommand
photo = rs.Fields("pic").Value
With Me
.txtLast = rs!Last
.txtFirst = rs!First
.imgPic.Picture = LoadPicture(photo)
End With
Exit Sub
notfound:
MsgBox "Not Found"

End Sub

any idea?

thank you...

wow, it was a year ago.. anyway, i was able to manage it..

give me your email and i'll show.. i will find my code...

arvin2006

Reputation Points: 15

Solved Threads: 4

Junior Poster in Training

Offline

69 posts
since Sep 2006

Permalink

Oct 4th, 2010

Re: VB Add/Edit/Save Pictures in Access DB

hi, just try to create application for my kids, but facing problem with image. could you please share the code. TQ in advance.

amrahim

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Oct 2010

Permalink

Oct 4th, 2010

Re: VB Add/Edit/Save Pictures in Access DB

Amrahim, I see you are a new poster, so welcome. You will have to open a new post though, this is quite old. I'll post all the code needed for save, edit, open and show a picture in vb6/access.

How to open a new thread? Go to the top of the page, hover your mouse over 'Software' (Located between >>>Editorial, Hardware & Software, SOFTWARE, Web Development etc<<<.

Click on VB4/5/6. Once the page opens, at the top as well, it gives you the option to open a new thread. I have for now included the link below. Just click on it and post your question.

Thank you so much.

http://www.daniweb.com/forums/newthr...=newthread&f=4

AndreRet

Reputation Points: 329

Solved Threads: 347

Senior Poster

Offline

3,700 posts
since Jan 2008

Page 1
2
Next

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Atteching an exe to my brownser mozila fire fox

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: how can i make a software for mobiles in vb

DaniWeb Message

Cancel Changes

User Name
Password