将图像存储到Access数据库中的附件字段 [英] Storing an image into an Attachment field in an Access database

问题描述

我在写一个VB应用程序，我需要在数据库中存储一个图像。用户在他们的计算机上选择图像，这将我的路径作为字符串。这是我的尝试，但是我得到错误一个INSERT INTO查询不能包含多值字段。

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."

这是我的代码：

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

我已经尝试在线搜索其他解决方案，但似乎我发现的一切是VB6或VBA代码。我知道人们会认为图像不应该存储在数据库中，但在这种情况下，这是我唯一的选择。

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.

感谢任何帮助

推荐答案

正如您所发现的，您不能使用SQL语句将文件插入 / code>字段。您必须使用ACE DAO Field2 对象的 LoadFromFile（）方法。下面的C＃代码适用于我。它是从Office博客条目修改的此处。

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());
            }
        }
    }
}

这篇关于将图像存储到Access数据库中的附件字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！