Storing an image into an Attachment field in an Access database
I'm writing a VB application where I need to store an image in the database. The user selects the image on their computer, which gives me the path as a string. Here's my attempt at it, however I'm getting the error "An INSERT INTO query cannot contain a multi-valued field."
Here is my code:
Dim buff As Byte() = Nothing
Public Function ReadByteArrayFromFile(ByVal fileName As String) As Byte()
Dim fs As New FileStream(fileName, FileMode.Open, FileAccess.Read)
Dim br As New BinaryReader(fs)
Dim numBytes As Long = New FileInfo(fileName).Length
buff = br.ReadBytes(CInt(numBytes))
Return buff
End Function
Sub ....
Dim connImg As New OleDbConnection
Dim sConnString As String
Dim cmdImg As New OleDbCommand
sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Settings.DB & ";Persist Security Info=False;"
connImg = New OleDbConnection(sConnString)
connImg.Open()
cmdImg.Connection = connImg
cmdImg.CommandType = CommandType.Text
If d.slogo <> "" Then
cmdImg.CommandText = "INSERT INTO Logo ( refId, [type], [img] ) VALUES(@refId, @type, @imgBinary)"
cmdImg.Parameters.Add("@refId", OleDbType.Double).Value = refId
cmdImg.Parameters.Add("@type", OleDbType.Double).Value = 0
cmdImg.Parameters.Add("@imgBinary", OleDbType.VarBinary).Value = ReadByteArrayFromFile(PathToImage)
cmdImg.ExecuteNonQuery()
End If
....
End Sub
I've tried searching for other solutions online, but it seems everything I find is VB6 or VBA code. And I know people are going to argue that images should not be stored in the database, but in this case, it is my only option.
Thank-you for any help!
As you have discovered, you cannot use a SQL statement to insert files into an Attachment
field in an Access database. You have to use the LoadFromFile()
method of an ACE DAO Field2
object. The following C# code works for me. It is adapted from the Office Blog entry here.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Access.Dao;
namespace daoConsoleApp
{
class Program
{
static void Main(string[] args)
{
// This code requires the following COM reference in your project:
//
// Microsoft Office 14.0 Access Database Engine Object Library
//
var dbe = new DBEngine();
Database db = dbe.OpenDatabase(@"C:\__tmp\testData.accdb");
try
{
Recordset rstMain = db.OpenRecordset(
"SELECT refId, img FROM Logo WHERE refId = 1",
RecordsetTypeEnum.dbOpenDynaset);
if (rstMain.EOF)
{
// record does not already exist in [Logo] table, so add it
rstMain.AddNew();
rstMain.Fields["refId"].Value = 1;
}
else
{
rstMain.Edit();
}
// retrieve Recordset2 object for (potentially multi-valued) [img] field
// of the current record in rstMain
Recordset2 rstAttach = rstMain.Fields["img"].Value;
rstAttach.AddNew();
Field2 fldAttach =
(Field2)rstAttach.Fields["FileData"];
fldAttach.LoadFromFile(@"C:\__tmp\testImage.jpg");
rstAttach.Update();
rstAttach.Close();
rstMain.Update();
rstMain.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
}
}
I did the same thing based off of the above code and the blog entry from here.
Here is my vb.net code which allows me to do an OpenFileDialog and multiple selections and the function will handle storing multiple files just fine. Though I did have to add a reference to my project for Microsoft Office 15.0 Access database engine Object Library to get it to work properly. I think you can go down to 12.0 or 14.0 as well.
Private Sub AddAttachment(ByVal Files() As String)
Const Caller = "AddAttachment"
Dim dbe = New Microsoft.Office.Interop.Access.Dao.DBEngine()
Dim db As Microsoft.Office.Interop.Access.Dao.Database
db = dbe.OpenDatabase(dbPath)
Try
Dim rstMain As Microsoft.Office.Interop.Access.Dao.Recordset
rstMain = db.OpenRecordset("SELECT ID, fieldName FROM tableName WHERE ID = " + (dt.Rows(currentRow).Item("ID").ToString), Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset)
If (rstMain.EOF) Then
rstMain.AddNew()
rstMain.Fields("ID").Value = 1
Else
For Each value As String In Files
rstMain.Edit()
Dim rstAttach As Microsoft.Office.Interop.Access.Dao.Recordset2
rstAttach = rstMain.Fields("ATTACHMENTS").Value
rstAttach.AddNew()
Dim fldAttach As Microsoft.Office.Interop.Access.Dao.Field2
fldAttach = rstAttach.Fields("FileData")
fldAttach.LoadFromFile(value)
rstAttach.Update()
rstAttach.Close()
Next
rstMain.Update()
rstMain.Close()
End If
Catch ex As Exception
If Err.Number <> 3820 Then
MsgBox(ex.Message)
Else
MsgBox("File of same name already attached", MsgBoxStyle.Critical, "Cannot attach file" & Caller)
MessageBox.Show(ex.Message)
End If
End Try
End Sub
I'm now working on the functions to RemoveAttachments and save files from the attachements field. I'll post those here later.