Database design help

So your database is sorted, now were on to DetailsView. :cool:

Are you trying to do this using DAL's and BLL's?

Anyway, if you have set up your DAL correctly, if you add a new query and select insert query, it should have automatically created the code.
And if you used the tutorial on the ASP.NET for BLL, then the rest should be straightforward.

When you add the DetailsView onto the page and link the datasource, make sure that you have selected the insert query.
By default the ID isn't visible when inserting, so you shouldn't have to remove it from the DetailsView. If it is, there is a property for the ID field called InsertVisible, set it to false.

Here's a quick example I knocked up yesterday based on your original post. Ignore the design of the database, just created it quickly. :o
But it will mainly give you an idea of how to create a 3 tiered design, which is based on the tutorials from asp.net. ;)
It's an avi file 65MB, about 5 mins long.
 
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.
 
when i saw this thread I thought it could be me writing it lol

In the near future i'm planning on making an almost identical database for where i work, I have hundreds of employees and about 10 projects that i log the hours for. The woman I took over from had everything all over the place in spreadsheets and I manually have to enter the hours every day from timesheets.

The only difference i can think of is that I need the hours entered by the employee verified by their project manager, not a great difference though.

I'm going to subscribe to this thread and watch with interest :)
 
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, .
 
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.
 
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
 
You are getting the error because the parameters in the DAL, don't match with the ObjectDataSource.

From the code you posted I'm not sure how you are updating the gridview.
Can you post the html code on the aspx page for the objectdatasource, and the actual SQL for the update from the DAL.
 
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, two things you can try.

1. Do you have 'Optimistic Concurrency' enabled in your tableadapter, if so untick it and try it again.

2. If the above doesn't work, where it says OldValuesParameterFormatString="original_{0}" in your html code change it to OldValuesParameterFormatString="{0}".
 
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.
 
General comment

You might want to include some "cost center" type stuff (most big places use it). Basically make another table with something like:
Cost_Code_ID, Cost_Code_Desc

Just so you can have

Date, Time, Code_Code, Project

As the main table view they enter details into. IMO you should allow null project. From a management perspective it allows you ask "how many hours are my staff spening training as apposed to xxxx".

Cheers

David
 
Right, let's start from the beginning. :D

Database
Do you want your Projects table to have an autonumber?
If so, have you set the ProjectID field to an int, with Identity Increment set to Yes in the properties?
Assigned ProjectID as a primary key?

DAL
Create a new DAL, assign to the Projects table.
In Advanced Options tick only 'Generate Insert, Update, Delete statements'.
Select All from the table, and save the query.
Right click to add a new query.
Select use sql statements, and select 'Update'.
Save query as UpdateProject

BLL
Create a new class in the App_Code folder.
Save it as ProjectBLL.
I have created this BLL based on the table design your posted here.
Code: 
Imports ProjectTableAdapters

<System.ComponentModel.DataObject()> _
Public Class ProjectBLL
    Private _projectAdapter As ProjectsTableAdapter = Nothing
    Protected ReadOnly Property Adapter() As ProjectsTableAdapter
        Get
            If _projectAdapter Is Nothing Then
                _projectAdapter = New ProjectsTableAdapter()
            End If

            Return _projectAdapter
        End Get
    End Property

    <System.ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Select, True)> _
    Public Function GetHours() As Project.ProjectsDataTable
        Return Adapter.GetProjects
    End Function

    <System.ComponentModel.DataObjectMethodAttribute(ComponentModel.DataObjectMethodType.Update, True)> _
    Public Function UpdateProject(ByVal ProjectName As String, ByVal ProjectDescription As String, ByVal ProjectOwner As String, _
                                        ByVal ProjectID As Nullable(Of Integer)) As Boolean
        Return Adapter.UpdateProject(ProjectName, ProjectDescription, ProjectOwner, ProjectID)
    End Function
End Class

aspx page
Add your GridView then add a new objectdatasource, and select the ProjectBLL file.
Add the edit button.
Change the OldValuesParameterFormatString to '={0}'
Test it.

I have just tested this and it works for me. :)
 
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
 
Wardie said:
Do you code the BLL yourself?
Click to expand...

Yep, you create a new vb class file in your project, in the App_Code folder.

Wardie said:
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 :\
Click to expand...

The one on the ASP site is good, even thought it doesn't explain much, but you really need to copy and run through the code and create the same DALs to understand it.
The BLL I posted just uses the queries so is easier to understand than the one on the ASP site.

Look at the BLL code I posted.

Firstly you import the TableAdapter from your DAL, first line of the BLL.

In the first part of the BLL file you create an Adapter Property, which is linked to your TableAdapter in the DAL. This will give you access to the queries in your DAL.

You then create Functions to represent your queries, using the Adapter you created to access the queries.
Because a Function needs to return something, return the query.

The <System.ComponentModel...> bits before the Functions assign to the ObjectDataSource you create for the GridView.
So...'(ComponentModel.DataObjectMethodType.Select, True)' section of the System.ComponentModel line will tell the ObjectDataSource that this is to be the default select statement.
Change the '.Select' at the end to '.Update', will tell the ObjectDataSource that this will be the default update statement.

I keep the structure of my BLL's the same, so I just copy and paste the code across each BLL and change the TableAdapter, Function names and the queries.

Hope that makes it clearer. :)

Wardie said:
Kev, nice to see someone so helpful. I'll make it up to you some day
Click to expand...

No problem ;)
If I'm not sure on something I keep looking at the code and try and work out what it is trying to do, eventually it clicks and I manage to sort it out.
 
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