Question on Images in Access + SQL

There's a Data type called OLE Object in which you can store pictures (ACCESS). Possibly there will be similar one in SQL Server

Not sure what you need Access for...You can have the data type Image in SQL and create a view. Haven't tried it personally, but everything you need should be in SQL Server.

If you do need access, and you are using Office 2003, you should be able to link to the SQL database and pull in the image and display it in a view/form/report there.

Hunter,

Because of this forum's 2000 character limit I'll have to send my
response in three separate messages.

Part #1

The following code snippets are in C# and Transact SQL. It assumes an
active SqlConnection to the Sql Server database.

Just went thru this drill a few weeks ago as a proof of concept for our
database migration to Sql Server. The test C# application automates
Adobe Acrobat to scan documents into .pdf format files. When acrobat
finishes scanning the document it saves the document to the hard drive
and returns control to the C# app.

The code below reads the .pdf file from the harddrive and places it into
a byte[] array variable that can then be passed into an Sql Server Image
type column.

====================
//' EXTRACT DB PDF IMAGE TO A BYTE ARRAY AND LOAD IT INTO THE Wr IMAGE Table Record structure
//' Create Memory Stream: Used to move .pdf doc from the harddrive to a local byte [] array
Stream strm = new MemoryStream();

//' Use the Memory Stream to transfer the .pdf doc from the harddrive to a local byte [] array

strm = new FileStream(mPathToTargetPdfDocumentOnHarddrive, FileMode.Open, FileAccess.Read);
byte[] aPdfDocByteArray = new Byte[strm.Length];
strm.Read(aPdfDocByteArray, 0, System.Convert.ToInt32(strm.Length));
strm.Close();

byte[] baCertImage = aPdfDocByteArray;
====================

Part #2

Once the .pdf file has been moved into a byte[] array we pass it into
Sql Server wr_doc_image table using the wr_doc_image_insert stored
procedure.

====================
CREATE TABLE wr_doc_image
(
doc_image_id int IDENTITY(1,1) NOT NULL,
doc_char char(2) NULL,
doc_nbr int NULL,
doc_image image NULL,
last_updt_date datetime NULL,
last_updt_userid char(8) NULL
)

CREATE PROCEDURE wr_doc_image_insert
(
@new_serial_id INT = -1 OUTPUT,
@return_msg CHAR(250) = 'Insert Successful' OUTPUT,
@doc_char CHAR(2) = NULL,
@doc_nbr INT,
@doc_image IMAGE = NULL
)
AS
BEGIN
BEGIN TRAN

DECLARE @err_msg CHAR(250)

SET @err_msg = 'Insert FAILED - see returned ERROR NBR'

INSERT INTO wr_doc_image
(
doc_char,
doc_nbr,
doc_image,
last_updt_date,
last_updt_userid
)
VALUES
(
@doc_char,
@doc_nbr,
@doc_image,
GETDATE(),
USER
);

SET @new_serial_id = (SELECT SCOPE_IDENTITY())

IF (@@error=0)
BEGIN
COMMIT TRAN
SET @return_msg = 'Insert Successful'
END
ELSE
BEGIN
ROLLBACK TRAN
SET @new_serial_id = -1
SET @return_msg = @err_msg
END

RETURN @@error

END
go
====================

Part #3

The following code is used to load the stored procedure paramters and call the stored procedure.
====================

//' INSTANTIATE an OleDb Command object(s)
OleDbCommand oCmd = new OleDbCommand("wr_doc_image_insert", mCn);

//' STORED PROCEDURE NAME:
oCmd.CommandType = CommandType.StoredProcedure;

//' Create and load oCmd parameters

//' RETURN PARAMETER return_value
OleDbParameter prmReturnValue = oCmd.Parameters.Add("@return_value",OleDbType.Integer);
prmReturnValue.Direction = ParameterDirection.ReturnValue;

//' OUTPUT PARAMETER new_serial_id
OleDbParameter prmNewSerialID = oCmd.Parameters.Add("@new_serial_id",OleDbType.Integer);
prmNewSerialID.Direction = ParameterDirection.Output;

//' OUTPUT PARAMETER return_msg
OleDbParameter prmReturnMsg = oCmd.Parameters.Add("@return_msg",OleDbType.Char, 250);
prmReturnMsg.Direction = ParameterDirection.Output;

//' INPUT PARAMETER doc_char
OleDbParameter prmdocChar = oCmd.Parameters.Add("@doc_char",OleDbType.Char,2);
prmdocChar.Value = oChkForNullOrBlank.Main(pWrdocImageRec.prwdocChar,DBNull.Value);
prmdocChar.Direction = ParameterDirection.Input;

//' INPUT PARAMETER doc_nbr
OleDbParameter prmdocNbr = oCmd.Parameters.Add("@doc_nbr",OleDbType.Integer);
prmdocNbr.Value = oChkForNullOrBlank.Main(pWrdocImageRec.prwdocNbr,DBNull.Value);
prmdocNbr.Direction = ParameterDirection.Input;

//' INPUT PARAMETER doc_image
OleDbParameter prmdocImage = oCmd.Parameters.Add("@doc_image",OleDbType.Binary);
prmdocImage.Value = pWrdocImageRec.prwdocImage;
prmdocImage.Direction = ParameterDirection.Input;

mNewSerialID = "-1";
int iReturnValue = -1;
string sReturnMsg = "";

try{
oCmd.ExecuteScalar();
mNewSerialID = oCmd.Parameters["@new_serial_id"].Value.ToString();
sReturnMsg = oCmd.Parameters["@return_msg"].Value.ToString().Trim();
iReturnValue = (int)oCmd.Parameters["@return_value"].Value;
}

Hope this helps.

Barry
in Oregon

Rather than store large images in the DB, why don't you store a filepath reference instead and refer to this in your app to fill a picture box.

Bit overkill harry. Could have just shown a link. I have to say this though.

The actual practice of storing photo's within a database is a bit silly in my eyes. When you develop a data driven web application, You develop it to be quick, dynamic, and powerful. When you store an image in that database, you run the risk of slowing your site down by leaps and bounds. God forbid an mdb becomes corrupted.

A better approach may be to store the graphics in an alternate location, and reference them through the database. Less bandwidth on your part, less server processing time, and much easier to develop if you don't mind building in an upload script which overwrites existing files.

This question was closed by the author

You can try an ActiveX control called AccessImagine ( http://access.bukrek.net ), makes adding images to database more convenient - you can load from file, scan, paste from buffer or drag-n-drop. You can crop image right inside the database and resample it automatically. It handles external image storage automatically if you need it.