vb.net 将图像存储到 Access 数据库的附件字段中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18764314/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing an image into an Attachment field in an Access database
提问by Michael
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."
我正在编写一个 VB 应用程序，我需要在数据库中存储一个图像。用户在他们的计算机上选择图像，它为我提供了字符串形式的路径。这是我的尝试，但是我收到错误“插入 INTO 查询不能包含多值字段”。
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.
我试过在线搜索其他解决方案，但似乎我找到的一切都是 VB6 或 VBA 代码。我知道人们会争辩说图像不应该存储在数据库中，但在这种情况下，这是我唯一的选择。
Thank-you for any help!
感谢您的任何帮助！
回答by Gord Thompson
As you have discovered, you cannot use a SQL statement to insert files into an Attachmentfield in an Access database. You have to use the LoadFromFile()method of an ACE DAO Field2object. The following C# code works for me. It is adapted from the Office Blog entry here.
正如您所发现的，您不能使用 SQL 语句将文件插入到AttachmentAccess 数据库的字段中。您必须使用LoadFromFile()ACE DAOField2对象的方法。以下 C# 代码适用于我。它改编自此处的 Office 博客条目。
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());
}
}
}
}
回答by Tim Ryder
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.
这是我的 vb.net 代码，它允许我执行 OpenFileDialog 和多项选择，并且该函数可以很好地处理存储多个文件。尽管我确实必须为 Microsoft Office 15.0 Access 数据库引擎对象库添加对我的项目的引用才能使其正常工作。我认为你也可以降到 12.0 或 14.0。
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.
我现在正在研究 RemoveAttachments 和从附件字段保存文件的功能。稍后我会在这里发布它们。
回答by Bazag
Another thing to add to this. If your database is encrypted then you will need to add to the OpenDatabase command.
另一件事要添加到此。如果您的数据库已加密，那么您将需要添加到 OpenDatabase 命令。
This is the code I used in C# but the VB.NET code will be very similiar. db = dbe.OpenDatabase(dbPath, false, false,"MS Access;PWD=password");
这是我在 C# 中使用的代码，但 VB.NET 代码将非常相似。db = dbe.OpenDatabase(dbPath, false, false,"MS Access;PWD=password");
It took me ages to try and track this down on my own and I will try break down the various parts of the method. The MSDN Article for it can be found here.
我花了很长时间才尝试自己追踪这个问题，我将尝试分解该方法的各个部分。可以在此处找到它的 MSDN 文章。
1st Argument: dbPath, that's the same as the usage in the original post. The location and filename of the database you want to open.
第一个参数：dbPath，与原帖中的用法相同。要打开的数据库的位置和文件名。
2nd Argument: false. This is a true/false argument that if true opens the database in Exclusive-Mode. So that only this single program can use it. Most of the time this should be false. Only use exclusive mode if you have to.
第二个论点：错误。这是一个真/假参数，如果为真，则以独占模式打开数据库。这样只有这个单一的程序可以使用它。大多数时候这应该是错误的。仅在必要时使用独占模式。
3rd Argument: false. This is another true/false argument. This time if it's true then it opens the database in Read-only mode.This means you can only use recordsets to read information and you cannot use the Edit, Add or Delete methods. This can be true or false depending on your needs.
第 3 个参数：错误。这是另一个真/假的论点。这次如果它是真的，那么它会以只读模式打开数据库。这意味着您只能使用记录集来读取信息，而不能使用 Edit、Add 或 Delete 方法。根据您的需要，这可以是对的，也可以是错的。
4th Argument: This sets specific properties in how to open the database. In this case. It says to set the Microsoft Access property 'PWD' to 'password'. In this property setting will tell method to open up an encrypted database using the password 'password'. Of course you will need to change "password" to whatever the database's actual password is to open but I'd been hunting this down for a while.
第 4 个参数：这设置了如何打开数据库的特定属性。在这种情况下。它说将 Microsoft Access 属性“PWD”设置为“密码”。在此属性设置中，将告诉方法使用密码“password”打开加密数据库。当然，您需要将“密码”更改为要打开的任何数据库的实际密码，但我已经搜索了一段时间。
I hope this helps.
我希望这有帮助。
