Images and Equipment

Question

0.00/5 (No votes)

See more:

I have two tables one with images of equipment and another with description of the equipment (which includes Company, Branch, Equipment number, VIN). Each equipment record has a potential to have 6 photos. The photos are separate and I am trying to figure out how to relate them to the equipment table.
I was able to use (SELECT * FROM OPENROWSET(BULK N''' + @imgString + ''', SINGLE_BLOB) as tempImg))' With a SET WHILE statement to iterate through a list of images and the filename
This gives me a table with ImageID, ImageDescription(extracting the filename from the path), and the Image. On my Equipment table I have 6 fields with a potential to have 6 images and the names are the filename. I am wondering how to link the two. Not every equipment has an image.

Posted 11-Nov-14 4:58am

adamolson

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2014-11-11T05:08:00

Solution 1

The way I would do it is to have an ID column on your equipment row, and link that to your images table with another row:

Equip Table:
id, name, part no, description

Images Table:
id, immageData, EquipId

You can then use the Equipment ID to return all the relevant images for a piece of equipment with a trivial query.

Posted 11-Nov-14 5:08am

OriginalGriff

Comments

Shweta N Mishra 11-Nov-14 11:24am

+5

adamolson 21-Nov-14 18:01pm

Should I use an Identity Column for Equipment ID

OriginalGriff 22-Nov-14 4:28am

Depends: personally I prefer GUID's, unless it's a log file where the ID is just there for ordering and to ensure uniqueness.

If I want to insert a new row to a table and I will need the ID to link to a different table, I generally use GUID rather than identity, so I can assign the value in my code rather than having to retrieve it's new value from SQL after the insert.