store images in database to be accessable by asp

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

store images in database to be accessable by asp

Author

Topic

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 07:11:29

Hi i have created a table as follows

ProductID (unique identifier)
Name (text)
Description (text)
ProductImage (variable MAX)

Now for every product i wish to have an image associated with it
How do i attatch the image to each row of products in SQL server management 2008?

Is there a way to store the images without code like in MS Access ?

I want to do that so i can create a website that views all the images and product details from the database

please help

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 07:20:42

see
http://chiragrdarji.wordpress.com/2007/03/05/storing-and-retrieving-image-in-sql-server/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 07:24:26

or just use OPENROWSET like


UPDATE table
SET ProductImage = (SELECT * 
               FROM OPENROWSET(BULK 'your image path', 
                               SINGLE_BLOB) AS a )
WHERE ProductID = youridvalue

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 07:44:44

ok can you explain how this works?

Does this add all images to each row or is this only to add one single image?

Where do i put that sql code into?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 07:44:55

Or use FILESTREAM access?

N 56°04'39.26"
E 12°55'05.63"

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 07:46:07

"I want to do that so i can create a website that views all the images and product details from the database"

Normally NOT a good idea to do this. You got a good reason? (Security for example ...)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 07:49:06

quote:
Originally posted by bazzano

ok can you explain how this works?

Does this add all images to each row or is this only to add one single image?

Where do i put that sql code into?

it adds one image to a row

you can put it in your insert procedure.

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 07:49:18

i just need something that is simple

Iv read you can have a link to the image file on the file system for each but how do i do that in the table view

something that is easy in access is so hard for sql server i dont get

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 07:50:15

so does that mean i need to have copy that procedure for each row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 07:53:10

quote:
Originally posted by bazzano

so does that mean i need to have copy that procedure for each row?

you need to call procedure for each insertion of new image

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 07:54:29

Ok so where do i go to put that code in?

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 08:06:41

i mean there has to be a simple way of adding images to each row in a table. How do eccomerce websites get built then?

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 08:08:29

"Iv read you can have a link to the image file on the file system for each but how do i do that in the table view"

By Table View do you mean HTML table, or something in ASP.NET?

Sorry if you know all this:

In HTML you just have

<table>
<tr>
<td>Lorem Ipsum</td><td><img src="/path/imagename.jpg"></td></tr>
</table>

/path/imagename.jpg will be a path relative to the ROOT of your web site (e.g. c:\INetPub\www\path\imagename.jpg) or can be on a virtual path - so you might define "/images/" to be c:\INetPub\ClientName\Images and then use HTML for "/images/imagename.jpg" and you might put the website itself at c:\INetPub\ClientName\WWW - i.e. the Client's Images folder is NO LONGER a sub-folder of WWW (we do this so that each client has two websites, LIVE and TEST, but they both reference the single (virtualised) Images folder.

Visakh has started a thread on Pros / Cons of putting images in the Database, or on the Disk. I've added my two-pennyworth to that thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138862

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 08:09:37

"How do eccomerce websites get built then?"

FWIW we build big eccomerce sites. All (and I mean *all*!!) images are in a folder on the IIS server, NONE are in the database.

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 08:10:25

Well i want the products to be searchable so i need them stored in a database along with there productID name and image?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 08:13:21

You don't search the content of the image do you?
For each product, store the path to the image in a column, and return that path value to your client application.

N 56°04'39.26"
E 12°55'05.63"

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 08:14:47

so how do i store the path to the image of each product?

Can you give me some basic steps because every answer is unclear to me so far

thanks

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 08:16:11

Ah, OK. I see. We store the Image Name with the Product.

Then when we render the HTML we do something like


"...<td>Lorem Ipsum</td><td><img src='/path/" & DatabaseResultSet[ProductImageName].value & "'></td></tr>"

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 08:17:08

(by "I See" I meant "I think I see which bit you are missing now"

)

bazzano
Starting Member

11 Posts

Posted - 2010-01-26 : 08:20:13

so how do i actually store the image in sql server lol

does anyone know?

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 08:27:20

My view would be that you don't store them in SQL Server. Instead you store them on the disk that IIS is using. (Although you might have IIS and SQL on the same machine of course ...)

Two main choices there:

1) FTP the images up to the IIS server (allows bulk upload of multiple files)

2) Build a Form into your application to allow a user to "upload" them from their browser. The HTML spec for file upload is pretty crappy, so you are basically stuck at uploading them one-by-one using a browser (unless you use a Flash object or some other workaround).

So ... lets say you have a Maintenance Page for a product (Qty, Price, ...) you then provide the user with means to select an image to associate with the product (from one they have already uploaded) or they use the single-file-upload form that is in your application to upload one, or they just type in the name of the image (and upload it as a separate step - e.g. by FTP).

Depends how sophisticated you want it to be.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources