如何从ADO记录集字段将图片加载到MS-Access Image控件？ [英] How to load picture into MS-Access Image control from ADO recordset field?

问题描述

使用：

1. MS-Access 2013 SP1 x86


2. MS-SQL Server 2012 SP1 CU8 x64


3. 通过ODBC连接DSN，SQL Server驱动程序


4. 用于MS-SQL Server的UpScene Database Workbench Pro v4.4.4 Pro

1. MS-Access 2013 SP1 x86
2. MS-SQL Server 2012 SP1 CU8 x64
3. Connection via ODBC DSN, SQL Server driver
4. UpScene Database Workbench Pro v4.4.4 Pro for MS-SQL Server

My Access 2013应用程序使用SQL Server 2012作为ODBC的后端数据库。我正在使用VBA / ADO来读/写数据到数据库。

My Access 2013 application uses SQL Server 2012 as the backend database with ODBC. I'm using VBA/ADO to read/write data to the database.

到目前为止，我从数据库中检索图像并将其分配给图像时失败了控制Access表单。图像存储在SQL Server表中（作为VARBINARY（MAX）字段。

I have been unsuccessful so far in retrieving an image from the database and assigning it to an image control on an Access form. The image is stored in a SQL Server table (as a VARBINARY(MAX) field.

在我将字段分配给Image控件的位置，它给出了运行时错误：类型不匹配。图像存储在数据库中作为Bitmap图像。我之前尝试过使用Jpeg，但这是同样的错误。如何解决？

At the point where I'm assigning the field to the Image control, it gives a runtime error: "Type mismatch". The image stored in the database as a Bitmap image. I tried with Jpeg earlier, but it was the same error. How can this be resolved?

SQL Server表定义和存储过程定义：

SQL Server table definition and stored procedure definition:

CREATE TABLE dbo.tbPhoto (
    row_id Int IDENTITY NOT NULL,
    student_id Int NOT NULL,
    picture VarBinary(max),
    date_updated DateTime NOT NULL,
    date_created DateTime NOT NULL, 
    CONSTRAINT PK_tbPhoto PRIMARY KEY CLUSTERED (
      row_id
    )
)

检索图片的访问程序：

Private Sub load_studentdetails(AStudentID As Integer)
 On Error GoTo errhnd

 objStudent.GetStudentDetails AStudentID, StudentDetailsRS

 Set Me.fmStudentReg_DtlA.Form.Recordset = StudentDetailsRS
' Me.fmStudentReg_DtlA.Form.Requery

 objStudent.GetStudentPicture AStudentID, Me.fmStudentReg_DtlA!imgStudentPic

 Exit Sub
errhnd:
 MsgBox "Error: " & Err.Description
End Sub

Public Sub GetStudentPicture(AStudentID As Integer, ByRef APicture As Image)
 On Error GoTo errhnd

 Dim rs As New ADODB.Recordset
 Set cmd.ActiveConnection = GetDBConnection

 cmd.CommandType = adCmdStoredProc
 cmd.CommandText = "dbo.StudentPicture_S"

 cmd.Parameters.Refresh
 cmd(1) = AStudentID

 rs.CursorLocation = adUseClient
 rs.Open cmd

 Set APicture = rs("picture")  '<-----Raises the error: "Type mismatch"

 Exit Sub
errhnd:
 MsgBox "Error: " & Err.Description

End Sub

推荐答案

由于您已经在使用ADODB.Recordset，我建议您使用ADODB.Stream对象作为中介。我刚刚尝试了以下代码，它对我有用：

Since you are already using an ADODB.Recordset I would suggest that you use an ADODB.Stream object as an intermediary. I just tried the following code and it worked for me:

Option Compare Database
Option Explicit

Private Sub cmdGetPhoto_Click()
    Dim cdb As DAO.Database
    Dim con As ADODB.Connection, rst As ADODB.Recordset, stm As ADODB.Stream

    Set cdb = CurrentDb
    Set con = New ADODB.Connection
    con.Open Mid(cdb.TableDefs("dbo_Clients").Connect, 6)
    Set rst = New ADODB.Recordset
    rst.Open "SELECT Photo FROM Clients WHERE ClientID=1", con, adOpenStatic, adLockOptimistic
    Set stm = New ADODB.Stream
    stm.Type = adTypeBinary
    stm.Open
    stm.Write rst("Photo").Value  ' write bytes to stream
    stm.Position = 0
    Me.Image0.PictureData = stm.Read  ' load bytes into Image control on form

    stm.Close
    Set stm = Nothing
    rst.Close
    Set rst = Nothing
    con.Close
    Set con = Nothing
    Set cdb = Nothing
End Sub

这篇关于如何从ADO记录集字段将图片加载到MS-Access Image控件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！