Database design help

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
I'm struggling to come up with a solution as my programming experience is limited. Basically i'm looking to create a program which will allow people to log the number of hours they have spent on a project, for each day of the week. They may spend time on several different projects each week.

I can't decide how to store the date.

tbl_Hours
ID
projectID
projectName
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

tbl_Projects
projectID
projectName
projectDescription
projectOwner

Could this be done better by just using a Day field instead? And then somehow encorporating a date into it? I was also thinking of maybe having a WeekStart field, which would just store the date of the start of the week for each new week.

If i'm not being clear please do ask me to clarify.

I'll be creating the program using ASP.net 2.0 and SQL Server for the database.

Any help really is appreciated :)
 
rpstewart, I need to store what project the user has worked on, so it needs to be in the Hours table...

The projects table is for storing all the project information. The user then must be able to select a project from this list when inputting their hours.

I think my database design looks all funky because I was trying to do it with using a ASP.net GridView in mind, because it would display exactly how I want it.

For example....

ID----DATE-----------Project----Monday----Tuesday----Wednesday----Thursday----Friday
1-----10/07/2007-----000001------5-----------3------------5------------3-----------7--
2-----10/07/2007-----000004------5-----------4------------2------------5-----------7--
3-----11/07/2007-----000001------5-----------3------------5------------6-----------7--

Cheers for helping guys I do appreciate it. Does that make it any clearer or?
 
The reason I include both fields is because I was hoping to somehow combine them into a single drop down menu/list to that it's easier for the user to select their project. It's easier to recognise a name than a number and helps to make sure they don't get it wrong.

You're right though I can just SELECT that from the projects table but I only need to store the projectID. Cheers :)

I'm struggling to work out how i'll be able to present the data in the above fashion, i'd like it to be editable in that format too.

ARghhhhhh
 
KevinCB said:
Are you still working with ASP.NET?

If so, you can create a drop down with the ID and Name using a select query.
The ID will be hidden, while the Name is displayed in the drop down.
Click to expand...

Yes I am Kevin (you're a helpfully bugger). I'd like to display both of them next to each other really.

My only problem is working out how i'm going to display the week like I shown above.
 
Cheers for the heads up PAz.

So has anyone got any idea how I can get it to display as above? Remember there can be multiple tasks entered for each day.
 
Now I can't even work out how to get in to insert when using a uniqueidentifier for ID fields.

Trying to setup a page to insert projects using a DetailsView control. Obviously when inserting the ProjectID field should be hidden and should auto-generate, how do I go about doing this?

If I just remove the field from the DetailsView and the INSERT query then it shouts at me because uniqueidentifiers can not contain null values. How do I get it to automatically generate this and hide it from the user?

Please help :(

Thanks for the help so far guys you've been great :)
 
Last edited:
Cheers Duncan that;s pretty much what i've got now anyways from the above posts :)

Read my last post for where i'm stuck now ha-ha.
 
When i've tried to insert pretty much using that query but using a DetailsView control it's telling me that projectID can not be null.

Can I manipulate the GridView to allow me to EDIT/DELETE records even with ComboBox's added etc?

Or am I going to have to learn about editing/deleting files and forget it?
 
Well i've seen methods to allow the GridView to add data relatively simply, and by standard is allows editing.

What do you mean by bound mode?
 
I can't even get it to insert just using the DetailsView. Here's the query...

Here's the error:

Code: 
Cannot insert the value NULL into column 'ProjectID', table 'Training.lward.Projects'; column does not allow nulls. INSERT fails.
The statement has been terminated. 
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ProjectID', table 'Training.lward.Projects'; column does not allow nulls. INSERT fails.
The statement has been terminated.


Here's the code:

Code: 
SELECT [ProjectID], [ProjectName], [ProjectDescription], [ProjectOwner] FROM [Projects]

Code: 
INSERT INTO [Projects] ([ProjectName], [ProjectDescription], [ProjectOwner]) VALUES (@ProjectName, @ProjectDescription, @ProjectOwner)

:(
 
I can't download/watch in work but i'm definitely going to when I get home about 1pm!

I'll let you know how it goes. People are pretty helpful in this section of the forum, thanks so much.
 
Now I can't edit data, ARGH.

Setup a DAL, added a GridView to the page and then linked an ObjectDataSource, selected 'ManHoursTableAdapters.ProjectsTableAdapter'.

Selected the update method:

Code: 
Update(Int32 ProjectID, String ProjectName, String ProjectDescription, String ProjectOwner, Int32 Original_ProjectID), returns Int32

Then enabled editing/sorting/deleting on the GridView. When I try to edit I get:

ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'Update' that has parameters: ProjectName, ProjectDescription, ProjectOwner, .
 
KevinCB said:
Have you done a BLL, or are you just linking your DAL to the GridView?

Can you post the objectdatasource code on the aspx page for the gridview.
Click to expand...

I was just linking my GridView to the DAL. The BLL tutorial I was following from the ASP site really isn't great and I wasn't too sure what was going on.

Update code:

Code: 
Update(Int32 ProjectID, String ProjectName, String ProjectDescription, String ProjectOwner, Int32 Original_ProjectID), returns Int32
 
Code: 
<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False"
            BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
            CellPadding="4" DataKeyNames="ProjectID" DataSourceID="ObjectDataSource1" ForeColor="Black"
            GridLines="Vertical">
            <FooterStyle BackColor="#CCCC99" />
            <Columns>
                <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                <asp:BoundField DataField="ProjectID" HeaderText="ProjectID" ReadOnly="True" SortExpression="ProjectID" />
                <asp:BoundField DataField="ProjectName" HeaderText="ProjectName" SortExpression="ProjectName" />
                <asp:BoundField DataField="ProjectDescription" HeaderText="ProjectDescription" SortExpression="ProjectDescription" />
                <asp:BoundField DataField="ProjectOwner" HeaderText="ProjectOwner" SortExpression="ProjectOwner" />
            </Columns>
            <RowStyle BackColor="#F7F7DE" />
            <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
            <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
        <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"
            InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GetProjects"
            TypeName="ManhoursTableAdapters.ProjectsTableAdapter" UpdateMethod="Update">
            <DeleteParameters>
                <asp:Parameter Name="Original_ProjectID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="ProjectID" Type="Int32" />
                <asp:Parameter Name="ProjectName" Type="String" />
                <asp:Parameter Name="ProjectDescription" Type="String" />
                <asp:Parameter Name="ProjectOwner" Type="String" />
                <asp:Parameter Name="Original_ProjectID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="ProjectID" Type="Int32" />
                <asp:Parameter Name="ProjectName" Type="String" />
                <asp:Parameter Name="ProjectDescription" Type="String" />
                <asp:Parameter Name="ProjectOwner" Type="String" />
            </InsertParameters>
        </asp:ObjectDataSource>

Here's the code from the auto-generated update command.

Code: 
UPDATE    Projects
SET              ProjectID = @ProjectID, ProjectName = @ProjectName, ProjectDescription = @ProjectDescription, ProjectOwner = @ProjectOwner
WHERE     (ProjectID = @Original_ProjectID)
 
Ok if I just changed the value to 0 on that property, I don't get an error but nothing actually happens. It doesn't change the data in the DB.

Doing all this from a DAL is a royal PITA. I know it's the best way to learn to do it though soooo.

I am stuck though lol.
 
Do you code the BLL yourself?

I really need to find a good tutorial on learning this BLL lark. I'm still trying to teach myself all of this at the same time and stuff isn't great that i've found. The BLL tutorial on the ASP website doesn't even explain any of the code :\

Kev, nice to see someone so helpful. I'll make it up to you some day :D
 
Getting there!

Got stuff inserting now, but I do have a question. Ideally I would like the user to be able to pick the date for something from a menu (Calendar for example). Also, when displaying data that uses a datetime field, is there anyone to get it to stop displaying the second byte (the time information)?

I just want the date.

The reason the datetime field isn't being done automatically is because users could perhaps be inserting data for the day before. It could possibly default to the current date, then have the option to be changed.

Getting there though guys, thank you!

(I'm not using a BLL at the moment, i'm being naughty :x)
 
You must log in or register to reply here.
Back
Top Bottom