Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Problem to save picture file

Posted on 2013-11-27
6
Medium Priority
?
703 Views
Last Modified: 2013-11-28
Hi,
when saving the picture file to Access DB, like

                    fl_nm = "c:\\inetpub\\VS2012\\App17\\" + lb_ph_file2.Text;
                    cmd = new OleDbCommand("update ite_tab set photo_file=@image where ite_abbr=@abbr", conn);

                    cmd.Parameters.Add("@abbr", OleDbType.VarChar).Value = tb_iteabbr.Text.Trim();
                    cmd.Parameters.Add("@image", OleDbType.Binary).Value = ImageToStream(fl_nm);
                    if (cmd.ExecuteNonQuery() >= 1) 
                    ...

        private byte[] ImageToStream(string fileName)
        {
            Bitmap image = new Bitmap(fileName);
            MemoryStream stream = new MemoryStream();
            image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp);
            return stream.ToArray();
        }

Open in new window


I get this exception. why?
An UPDATE or DELETE query cannot contain a multi-valued field.Microsoft Access Database Engine
0
Comment
Question by:HuaMinChen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 21
ID: 39682782
AFAIK, you can use an append query to add an file to a field that ius a attachment data type.

Here is an VBA example using recordsets:

On Error GoTo Err_AddImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.Edit

' open a second recordset on the multi-value (attachment) field
Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

rsChild.Update
rsParent.Update

Exit_AddImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_AddImage:

If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage

End If

Open in new window

0
 
LVL 25

Expert Comment

by:chaau
ID: 39682788
I think you need to use OleDbType.LongVarBinary, like this:
QueryParameter par = new OleDbParameter("@image", OleDbType.LongVarBinary,
PictureByteArray.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current,
ImageToStream(fl_nm));
cmd.Parameters.Add(par);

Open in new window

0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 39682797
Sorry Chaau, I then get these 2 errors
Error      3      The type or namespace name 'QueryParameter' could not be found (are you missing a using directive or an assembly reference?)      C:\App17\App17\Default.aspx.cs      668      21      App17
Error      4      The name 'PictureByteArray' does not exist in the current context      C:\App17\App17\Default.aspx.cs      669      25      App17

due to this line
                    QueryParameter par = new OleDbParameter("@image", OleDbType.LongVarBinary,
                        PictureByteArray.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current,
                        ImageToStream(fl_nm));

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 25

Expert Comment

by:chaau
ID: 39682805
sorry, I meant this:
OleDbParameter par = new OleDbParameter....

Open in new window

Actually, change the code like this:
byte[] PictureByteArray = ImageToStream(fl_nm);
OleDbParameter par = new OleDbParameter("@image", OleDbType.LongVarBinary,
PictureByteArray.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current,
PictureByteArray);
cmd.Parameters.Add(par);

Open in new window

0
 
LVL 11

Author Comment

by:HuaMinChen
ID: 39682813
Sorry, I still get the same error
An UPDATE or DELETE query cannot contain a multi-valued field.Microsoft Access Database Engine

due to these
                    fl_nm = "c:\\inetpub\\VS2012\\App17\\" + lb_ph_file2.Text;
                    cmd = new OleDbCommand("update ite_tab set photo_file=@image where ite_abbr=@abbr", conn);

                    cmd.Parameters.Add("@abbr", OleDbType.VarChar).Value = tb_iteabbr.Text.Trim();
                    byte[] PictureByteArray = ImageToStream(fl_nm);
                    OleDbParameter par = new OleDbParameter("@image", OleDbType.LongVarBinary,
                    PictureByteArray.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current,
                    PictureByteArray);
                    cmd.Parameters.Add(par);
                    if (cmd.ExecuteNonQuery() >= 1) 
                    ...

Open in new window

0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 435 total points
ID: 39683857
The attachment data type is a type of sub recordset. Even within Access, to add image with code  you must use DAO not an update query.


From a fellow Microsoft VB MVP:

I don't know whether you can insert into an Access Attachment column using ADO.NET. The only solution that I am aware of requires DAO (Project...Add Reference...COM tab...Microsoft DAO 3.6 Object Library).

How to: Work With Attachments In DAO
at http://msdn.microsoft.com/en-us/library/bb258184%28office.12%29.aspx

Paul ~~~~ Microsoft MVP (Visual Basic)



Boyd Trimmell, Microsoft Access MVP
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question