How to insert an image into an Access OLE field via .NET

Question

I have an Access .mdb database and I want to insert an image from an application developed in visual C# 2010. Pictures are stored in the database in the field of OLE-object.

After adding images directly in Access they are stored in the format of an Bitmap Image. These pictures can be opened in Access with a double-click.

I have the following code:

OdbcConnection Connection = new OdbcConnection();
...
sql = "INSERT INTO film (poster) VALUES (" ' " + Image.FromFile(textBox8.Text) + " ' ");";
//texbox are stored the picture name
OdbcCommand Command = new OdbcCommand(sql, Connection);
Command.ExecuteNonQuery();

The code works well, but Access stores the picture as binary data and it cannot be opened again in Access. Please tell me how to insert the image as a Bitmap Image. Thanks.

Gord Thompson · Answer 1 · 2014-04-28T12:14:11.113

This is a somewhat unusual request. Most people asking about OLE imbedded images in Access are asking about how to convert them from OLE objects into raw binary data, not the other way around. Current versions of Access have features like the Image control that can display bitmap images without having to deal with the complications of OLE "wrappers" being added to the object data.

Still, here is one way to do what you requested. It uses an Access.Application object, so Access must be installed on the machine for this to work. It also requires a Form inside the Access database where

the form itself is bound to the table containing the OLE image field you want to insert,
the only control on the form is a Bound Object Frame, bound to the OLE field.

The sample code also assumes that the table being updated has a numeric primary key field named [ID].

private void button1_Click(object sender, EventArgs e)
{
    // test data
    int recordIdToUpdate = 15;
    string bmpPath = @"C:\Users\Gord\Pictures\bmpMe.bmp";

    var paths = new System.Collections.Specialized.StringCollection();
    paths.Add(bmpPath);
    Clipboard.SetFileDropList(paths);

    // COM Reference required:
    //     Microsoft Access 14.0 Object Library
    var accApp = new Microsoft.Office.Interop.Access.Application();
    accApp.OpenCurrentDatabase(@"C:\Users\Public\Database1.accdb");
    accApp.DoCmd.OpenForm(
            "PhotoForm",
            Microsoft.Office.Interop.Access.AcFormView.acNormal, 
            null,
            "ID=" + recordIdToUpdate);
    accApp.DoCmd.RunCommand(Microsoft.Office.Interop.Access.AcCommand.acCmdPaste);
    accApp.DoCmd.Close(
            Microsoft.Office.Interop.Access.AcObjectType.acForm, 
            "PhotoForm", 
            Microsoft.Office.Interop.Access.AcCloseSave.acSaveNo);
    accApp.CloseCurrentDatabase();
    accApp.Quit();
    this.Close();
}

i already have a method to retrieve a picture from ole-filed to the c# program (cutting the ole-header), now i need a reverse method. you suggested a very interesting method, but i need to do this only with C sharp tools, without any changes of db. thank you anyway! — user3260339, Feb 01 '14 at 17:47
@user3260339 As you wish. Keep in mind that the Access form does not necessarily have to be in the same database file as the table you are updating. It could be in a separate Access database file with a Linked Table. That would work just as well. (In fact, it would be required if the database file containing the table was a shared back-end in a multiuser environment.) — Gord Thompson, Feb 01 '14 at 18:54

score -2 · Answer 2 · edited Nov 11 '15 at 22:38

    private string ImageToBase64String(Image image)
    {
        using (MemoryStream stream = new MemoryStream())
        {
            image.Save(stream, image.RawFormat);
            return Convert.ToBase64String(stream.ToArray());
        }
    }

    private void SaveButton()
    {

        string Pic = ImageToBase64String(PicBox.Image);

        OleDbCommand PicSave = new OleDbCommand("INSERT INTO Picture(ID,PICTURE)VALUES(" + PicId.Text + ",'" + Pic + "')", con);
        con.Open();
        var SaveValue = PicSave.ExecuteNonQuery();
        if (SaveValue > 0)
        {
            MessageBox.Show("Record Saved", "Information");
            ValueClear();
        }
        else
            MessageBox.Show("Rocord Not Saved", "Erro Msg");
        con.Close();
    }

100 % Worked Code For Insert Image Into MS Access Database in OLE Field — Pintu Singh, Apr 24 '15 at 05:25

How to insert an image into an Access OLE field via .NET

2 Answers2

Linked

Related