Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Dec 09, 2007 03:42 AM by doKtorhallam
Dec 06, 2007 12:16 AM|LINK
i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything
to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the
"key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will
create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery
and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.
as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am
trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing
i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all
photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default
to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in
the order he wants? and then put the photos under the jobs in the order he wants as well?
it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know
any ideas, suggestions, and help is greatly appreciated.
Dec 06, 2007 02:56 AM|LINK
Have a look at
Dec 06, 2007 09:25 PM|LINK
thank you for your help, the articles were informative on the level of coding the pictures into and out of the database.
what i need is some help on how to work a more complex database scheme than i have seen on any of the tutorials and walkthroughs that i have thus far found.
here is how i am setting it up.
tblAlbums with albumID primary key and album names,etc. then tblJobs with jobID primary key and albumID foriegn key, jobName,etc. and finally tblPhotos with photoID primary key, and jobID foriegn key, and photoData(the binary img),photoType, photoLength,
on the upload photo page, i first have dropdown control that is databound to albumID and displaying albumName. next i have same dropdown for job, and next to it a txtbox to create new job. then i will have the fileupload and comment fields to import tblPhotos
what i need at this point is a little help in how to code so that each photo is associated with a specific job, and each job is associated with a given album. i am working this out in a very rough around the edges manner, but a little suggestion, help,
or refinement would be a great help.
Dec 06, 2007 09:40 PM|LINK
i am slowly working this out. i have databound the dropdownJob and put a where clause to display only jobs that are in the album that is chosen in dropdownAlbums. then, i have included txtJob to create a new job, and btnJob next to it. i will wire the btnJob
click event to create the new job in tblJobs, then the page will refresh. then the user will be free to fileupload their photos under their newly created jobs. i will code in the jobID when writing the file info to tblPhotos.
i believe (writing, coding, and testing now) that this should give me all necessary associations.
Dec 06, 2007 10:02 PM|LINK
Please drop me a note on Saturday, and I will look into this more. I will create a SQL Script. Please let me know what version of SQL Server you are using. Also whether c# or vb.net.
Dec 06, 2007 11:11 PM|LINK
Dec 07, 2007 06:29 PM|LINK
ok, i will attempt to explain here quickly. i have the photo upload page complete and working perfectly. the photo display is another matter. i am using a gridview, and i believe i have all the proper code in place aside from a single sql statement. the
albumID - int - PK
AlbumName - varchar
jobID - int - PK
albumID - int - FK
jobName - varchar - unique constraint
jobComment - varchar
photoID - int - PK
jobID - int - FK
photoData - image
photoType - varchar
photoLength - bigint
photoComment - varchar
all 3 tables have 2 columns each named albumOrder, jobOrder, and photoOrder, AND albumVisible, jobVisible, and photoVisible. the order columns are datatype int so the site Admin can place items in the order he chooses. the visible columns are datatype bit,
default to False, so site Admin can approve and mark True all photos before they post to site. finally, tblPhotos has a bit datatype called photoThumbnail, with the intent that site Admin only marks True on one photo per job.
the set-up for the photo gallery page of the site is a dropdown to select the album, then the gridview will display a gridview populated with the thumbnail for all jobs belonging to that album. as i said, i believe i have code in place to display the images
in gridview (using a go between imgGrab.aspx file). i need help writing the SQL SELECT statement to pull only photos with thumbnail=True, only from jobID that belongs to chosen albumID. the statement i tried is this:
i am getting errors on this statement that seem to me that it is invalid to put the variable into the query. (the albumChoice variable is a string that contains the dropdown.SelectedItem.
i assume my query is the problem, and need help with it. if it seems ok, and the problem could be something else, let me know and i can paste the error and stack trace that i am getting.
and after this phase, i need to create each thumbnail into a link to display all photos(not just the thumbnail) in a separate gridview or on a separate page. but i am barely thinking about that just yet. once that is all accomplished, i put together the
set of Admin pages to control all this. but i am getting there....[;)] ..slowly but deliberately.
thanks for any and all help here.
Dec 07, 2007 06:35 PM|LINK
and my SQL leaves much to be desired, i realize already that i completely omitted any check for photoThumbnail="True"
but still, i am quite proud of this baby thus far, (considering i have only seriously been developing with html, aspx, VB, C#, and SQL for about 2 months.
this section of this project is my proudest moment to date!
thanks again for any help and suggestions.
Dec 07, 2007 07:13 PM|LINK
ok, i have altered and cleaned a few things up a bit. i set dropdownAlbum to display albumName but hold the value of albumID, then changed the corresponding variable to intAlbum. i have redone the query, and it feels like it is so close, but it is telling
me that intAlbum is an invalid column name. below is the VB code behind page for reference.
intAlbum = dropdownAlbum.SelectedValue
galleryGrid.DataSource = ds
Dec 07, 2007 09:23 PM|LINK
....inching closer and closer....
i clean up a couple syntax issues, and rewrite the query to the following:
and it now partially works, it is pulling the correct jobComment and photos as per album dropdown, but the gridview displays the comment but no image, just that dreaded little red x-box.