Hope you guys aren't getting sick of me, yet at least

Here's what I'd like to do:
Have a database that contains all of the information about the DVD's I have. I'd like to store various things like name of movie, length, format (dvd/bluray), studio, date filmed.

• I'd like to create a small web app that will allow me to do various things with this information. This will be done in asp.net with focus on c# (and not vb).
• I'd like to first be able to display the information in the database, and have the option to sort based on the different categories (ascending/descending).
• I'd like to be able to have a drop down box that lets me select either bluray or dvd's, and have that list displayed.
• I'd like to be able to modify (as well as add/delete) an existing movie with my aspx page, without having to go into the database
• Have the output display no more than say 15 titles per page, and give me an option to move to the 2nd page

• Since I want to have just bluray or just dvd's show, should I have a separate table that contains this information for the dropdown menu?
• Should everything be in 1 table, or all broken up?

Since there will be two possible answer (DVD or Bluray)  I guess it will be a good thing to separate it to other table( you could name it as Format Table) and just reference it to your main table.

Populating it to a dropdownlist will be too easy right. So you dont have to worry.

I guess you already know how to delete, add, edit using a gridview and showing 15 records per page. There's a lot of things that a gridview can do out of the box.

tgabe213,

Based on your 'requirements', i'd suggest a 5 table design (for starters). These would be:

Film (DVDID, MovieName, GenreID (ForeignKey), Length, FormatID (ForeignKey), StudioID (ForeignKey), GuidenceID (ForeignKey), ReleaseDate) - [you might want to also add 'Rating' i.e. 1-5]

Genre (GenreID, GenreDescription) 

Format (FormatID, FormatName)

Studio (StudioID, StudioName)

Guidence (GuidenceID,  Description)

This would allow you to filter by genre, format, studio etc, etc. Obviously, there would be other supporting tables that you could add as the application progressed (Cast, Production Company, etc, etc), but for now, I think that [the 5 tables above] would be a good starting place.

I've created a little test asp.net app in c# for you to look thro to get an idea of what i would envision (when you compile it, click on the 'admin' folder in the browser to get going).

Hope it helps.

http://www.originaltalent.com/downloads/DVDCollection.zip

Cheers for now.

jimi

Thank jimibt,

 I'm a little lost though. I downloaded and extracted your folder, but I'm not sure what I'm exactly supposed to be compiling. Do I open the entire directory as a project in Visual Studio?

What is the table Guidence for?

Thanks!

Hi there - if you open it up in vs2008 (i.e. point to the base folder that you extracted it to in vs and open as 'web site' from the file menu). once you open that into vs, then you'll need to compile it in order for it to display in your browser (i'm assuming IE tho it will open just fine in Firefox etc). Once it's been compiled and run (you can press F5 to do this in a single action), you'll see a folder list in your browser. This was the option i was referring to - i.e. click on the folder that you see named 'Admin'. You can of course bypass this view in the browser by selecting the Default.aspx file (under Admin folder in vs2008 Solution Explorer) and right clicking on it and selecting that as 'Set As Start Page' before compiling. Ok, hopefully haven't confused you there.

Let me know how you get on. (i'm 'out' for and hour or so, so might not get back to you straight away)

jim

btw - Guidence is purely related (and may be a UK thing) to the audience that it's appropriate for (i.e. (U)niversal, (P)arental Guidence, 12, 18 etc..). It may be called 'Certification' in the US??

 Oh duh. Yea, it all makes sense. I compiled it (just after opening the entire directory, I was thinking I needed to open a specific page). I click on Admin, then get to the menu select. After selecting any of them, I get some sort of DB error.

Exception Details: System.Data.SqlClient.SqlException: Directory lookup for the file "C:\Documents and Settings\timg\Desktop\DVDCollection\App_Data\DVDCollection.mdf" failed with the operating system error 5(Access is denied.).
Cannot attach the file 'C:\Documents and Settings\timg\Desktop\DVDCollection\App_Data\DVDCollection.mdf' as database 'DVDFilms'.

Source Error:

Line 38:                 cmd.Parameters.Add("@Start", SqlDbType.Int).Value = (pageIndex * pageSize) + 1;
Line 39:                 cmd.Parameters.Add("@End", SqlDbType.Int).Value = ((pageIndex + 2) * pageSize);
Line 40:                 cn.Open();
Line 41:                 return this.GetFormatCollectionFromReader(ExecuteReader(cmd));
Line 42:             }

Tim,

There is another way around this (it looks like it doesn't like running a 'local user' copy of the database). You'd need to re-extract the files (or move the folder) to (perhaps) the root of your c:\ drive (i.e. c:\DVDCollection\*.*). It could be that windows doesn't like running db stuff from the desktop area. Next, go into SQL Server Management Studio Express and on the Databases tree, right click on any entry named 'DVDFilms' and select 'Tasks->Detach'.

Then try running it again (from the new c:\ DVDCollection location) and hopefully that should resolve it. If that still doesn't work, then you may have to change the way that you are accessing the database file from the web.config file. basically, rather than attempting to re-attach the database file, you'd 'point' to that instance by changing the lines in the web.config as shown below:

<!--<add name="LocalSqlServer" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|DVDCollection.mdf;user instance=false;Integrated Security=True;Initial Catalog=DVDFilms;" providerName="System.Data.SqlClient"/>-->

</connectionStrings>

This would hopefully sort out the issues you've been seeing. I'm presuming that you've got full rights to the drive and sqlserver (express or otherwise) as well.

Let me know how you progress.

jimi

[edit] - there is also a case (as ever) that the db may still need to be 'manually' attached if you opt for the revised web.config section above. this would entail going into SQL Server Management Studio Express and attaching the database (in c:\DVDCollection\App_Data) and naming it DVDFilms (as long as an entry by that name didn't already exist). Just thought i'd better add that (this is actually the way that you'd 'normally' do things)

Lee - i think the best thing is to attach the db manually from the sql server management studio express. i tried this on my laptop and after a similar erroneous start to your own, managed to get it working correctly doing it this way.

worth a try anyway...

jimi

[edit] i wonder if some other kind reader would mind downloading the code and trying it out and reporting on any issues and resolutions encountered. thanks. the url again:

http://www.originaltalent.com/downloads/DVDCollection.zip