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

问题描述

我正在编写一个 VB 应用程序，需要在数据库中存储图像.用户在他们的计算机上选择图像，它为我提供了字符串形式的路径.这是我的尝试，但是我收到错误插入 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 语句将文件插入 Access 数据库中的 Attachment 字段.您必须使用 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	estData.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	estImage.jpg");
                rstAttach.Update();
                rstAttach.Close();
                rstMain.Update();
                rstMain.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}

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