General discussion

Locked

Question on Images in Access + SQL

By Hunterzh ·
I?m using Access + SQL Server to develop a database application, and now need to show pictures in the database. For example, customer can upload a scanned drawing in jpg or bmp format, and the all other users can view the drawing and make their comments.
How can I realize this? I know first need to add a field in SQL Server database, with image data type. What should I do then in Access?

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by unni_kcpm In reply to Question on Images in Acc ...

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

Collapse -

by unni_kcpm In reply to

In SQL Server 2000 there's similar Data Type called "Image".

Collapse -

by JRod86 In reply to Question on Images in Acc ...

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.

Collapse -

by frostbb In reply to Question on Images in Acc ...

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;
====================

Collapse -

by frostbb In reply to Question on Images in Acc ...

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( 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
====================

Collapse -

by frostbb In reply to Question on Images in Acc ...

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

Collapse -

by bob In reply to Question on Images in Acc ...

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.

Collapse -

by steve.nihan In reply to Question on Images in Acc ...

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.

Collapse -

by Hunterzh In reply to Question on Images in Acc ...

This question was closed by the author

Collapse -

Handle images in SQL Server database

by Max_D In reply to Question on Images in Acc ...

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.

Back to Software Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums