CODEWITHSUNDEEP
c#attachment
Angelo
Angelo

Reputation: 41

attach files to ms access database using c#

I am currently working on a project. A document repository system. I am using C# windows forms and MS Access 2010 as my database. My table for the storing of documents is named "Documents" and has 2 columns namely Project ID and File (Attachment data type). I can now browse files using the openfiledialog but can't seem to upload it.

Here is my current code for my upload button.

        con = new OleDbConnection(cs);
        con.Open();     
        String num = lblPnum.Text.ToString();
        string a = "INSERT INTO [Documents]([ProjectID]) VALUES('"+ num + "')";

        cmd = new OleDbCommand(a);
        cmd.Connection = con;
        cmd.ExecuteReader();
        con.Close();
        MessageBox.Show("Document Successfully Added", "Prompt", MessageBoxButtons.OK, MessageBoxIcon.Information);
        this.Close();
        FrmHome home = new FrmHome();
        home.Show();
        home.statusPanel.Text = statusPanel.Text;

As of the moment, I can already get the project ID based on the project number that I have selected. what do I need to add to be able to attach files to my database and show it to the gridview.

Upvotes: 3

Views: 3102

Answers (3)

Charlie P
Charlie P

Reputation: 45

This article on Microsoft Docs has a working example: Work with attachments in DAO

var dbe = new DBEngine();
Database db = dbe.OpenDatabase(DBFilePath, ReadOnly: false);

// first record set is the table
Recordset rs = db.OpenRecordset("SELECT * FROM " + TableName); 
rs.MoveFirst();
rs.Edit(); 

// second record set is the actual field / cell in the table
Recordset2 rs2 = (Recordset2)rs.Fields["Attachments"].Value;

// add document
rs2.AddNew();
Field2 f2 = (Field2)rs2.Fields["FileData"];
f2.LoadFromFile(ImageFile);
rs2.Update();

rs2.Close();
rs.Update();
rs.Close()

Upvotes: 2

zawhtut
zawhtut

Reputation: 8551

Microsoft Access 14.0 Object Library

is required to handle Access attachment types. The sample code is following.

    private void insertImageFileToMemo(String memoId)
    {
        var dbe = new DBEngine();
        Database db = dbe.OpenDatabase(@"D:\yourdatabase.accdb");
        try
        {
            Recordset rstMain = db.OpenRecordset(
                    "SELECT memoId,memoImage FROM MyMemo WHERE MemoID = '" + memoId + "'",
                    RecordsetTypeEnum.dbOpenDynaset);
            rstMain.Edit();
            Recordset2 rstAttach = rstMain.Fields["memoImage"].Value;
            rstAttach.AddNew();
            Field2 fldAttach = (Field2)rstAttach.Fields["FileData"];
            fldAttach.LoadFromFile("memofile1.jpg");
            rstAttach.Update();
            rstAttach.Close();
            rstMain.Update();
            rstMain.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
    }

Upvotes: 1

Faraz Ahmed
Faraz Ahmed

Reputation: 1607

you can upload file like that in MS Access,keep in mind that particular DB is in Debug folder of application,and name of Access Database is DB

try
        {
            FileInfo file = new FileInfo("file.xlsx");


            using (var connection1 = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DB.mdb"))
            {

                OleDbCommand cmd = new OleDbCommand();
                //SqlDataAdapter cmd = new SqlDataAdapter();
                using (cmd = new OleDbCommand("INSERT INTO simple (doc) values (@file)", connection1))
                {
                    //cmd.Connection = connection1;
                    connection1.Open();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@file", file);
                    cmd.ExecuteNonQuery();
                }
            }

        }
        catch (Exception ex)
        {

        }

Upvotes: 0

Related Questions