MS Access: Sending image as varbinary to stored procedure

Question

Gear: MS Access 2016, SQL Server 2014

Languages: VBA, T-SQL

The Problem: The project is a "store" that use MS ACCESS as a front-end to connect to the SQL Server, and when adding items to the Database (SQL Server database) i need to add the item's image as well. So what i do before that is to to be able to add stand-alone image to Images table in the database, the table has 2 fields an AUTO id as integer and ImageData as VARBINARY(MAX).

In order to add an image to the database i have created a stored procedure with one parameter as VarBinary(MAX) that should be able to exceed 8k byte, but when i am sending more 8k bytes i am getting error message "[SQL Server Native Client 11.0]string data, right truncation".

Researching the problem online didn't solve my problem, all i found was maybe the data is too big and to solve it i should change variable size to MAX indication of data that may exceed 8k byte

This is the trial VBA Code to use the stored procedure with an image:

Dim Connection As New ADODB.Connection
Dim strm As new ADODB.stream
strm.Type = adTypeBinary
strm.Open
strm.LoadFromFile "C:\out.png"
strm.Position = 0

Connection.Open 'Connection string that should not be published'
If Connection.State = 1 Then
    Dim cmd As New ADODB.command
    Set cmd.ActiveConnection = Connection
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "InsertImage"
    cmd.Parameters("@image").Size = -1
    cmd.Parameters("@image").Value = strm.Read

    If Not IsNull(cmd.Parameters("@image").Value) Then cmd.Execute ' Crushes on execute
End If

This is the stored procedure :

USE [Supply]
GO
ALTER PROCEDURE [dbo].[InsertImage]
(
    @image as VARBINARY(MAX)
)
AS
BEGIN
    INSERT INTO Images values(@image)
    SELECT Scope_Identify()
END

When using less than 8k byte image the process above works or when doing the following script in SQL Server Management Studio with an image weight more than 8k bytes :

INSERT INTO Images
SELECT * FROM OPENROWSET(BULK 'C:\out.png', SINGLE_BLOB) as img

score 0 · Accepted Answer · answered Mar 08 '18 at 14:23

In the end i used the above stored procedure to "initialize" the image in the data base, and then created another procedure that receives image id and more data and then concatenating the data received with the data exists while adding the table an aging mechanism so the data will not be changed again and destroys the image in the database.

MS Access: Sending image as varbinary to stored procedure

1 Answers1

Linked