ASP.net Auto-Generated SQL Update

Bakedgoods

Bakedgoods

Bakedgoods

Capodecina
Permabanned
Joined
31 Dec 2003
Posts
5,172
Location
Barrow-In-Furness
All was well, realised I had a slight mistake in my database design and I needed to add a third primary key, this has ruined the auto-generated update commands created by the DataSet.

I've tried re-creating the tableAdapter and the web page but no joy.

When I try edit an item i'm getting the following error:

"ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'Update' that has parameters: Hours, Comments, Approved, original_TimeCode, original_Date, original_Username."

The update query that is generated looks like this:

Code: 
UPDATE    tblHours
SET              TimeCode = @TimeCode, Date = @Date, Hours = @Hours, Username = @Username, Comments = @Comments, Approved = @Approved
WHERE     (TimeCode = @Original_TimeCode) AND (Date = @Original_Date) AND (Username = @Original_Username)

And finally, the table looks like this...

tblHours
ID
TimeCode - PK
Date - PK
Hours
Username - PK
Comments
Approved

The reasoning behind the three primary keys is to ensure that a user can not log hours for the same Date and Timecode more than once.

Anyone got any ideas? I'm lost here and my SQL knowledge is far from great :(
 
I suppose I could learn about doing that.

I thought it's best to start and get your database solid first, rather than building your database from a coding/application point of view.
 
Haircut said:
I would agree with getting your database sorted out first.

What is the ID field in tblHours?
On initial glance I would suggest that if you have an ID field this would be the primary key and then you create a separate unique key for the other three columns to guarantee that they can't break the validation you have mentioned.
Click to expand...

The ID field is simply used for easily referencing a row, if you think about what i've said the ID field doesn't actually identify a unique row though.

For a row to be unique, the fields ID, Date and Username must not have a duplicate entry.

This is for a timesheet system, so it means that a User can only log hours for a paticular timecode ONCE for each day.

Unfortunately this has completely ruined the auto-generated update queries for that DataSet :(
 
From reading a little it looks like it's fine to use a surrogate key in this case, but I can't help thinking it's a workaround rather than a proper solution.

I don't use a BLL so i'll have to check if an entry exists from VB code, although I suppose I could just create a custom SQL query in the DataSet.

I have two options really...

1) Change to one primary key and write some VB code/SQL that will check if an entry already exists before updating or inserting.

2) Keep the compound key and write a custom update/delete query (this will also have to check if an entry exists before updating).

What do you reckon?
 
Last edited:
Ok I decided to keep the primary key the same but alter the Update and Delete queries..

I've tired it two ways but I keep getting the same error:

"ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'UpdateQuery' that has parameters: Hours, Comments, Approved, Original_ID, original_TimeCode, original_Date, original_Username."

I've tried by editing the auto-generated query in my DataSet, and my adding a new Update Query to the dataset.

The update query is...

Code: 
UPDATE    tblHours
SET              TimeCode = @TimeCode, Date = @Date, Hours = @Hours, Username = @Username, Comments = @Comments, Approved = @Approved
WHERE     (ID = @Original_ID)

The primary keys are still TimeCode, Date and Username.

The plan is to try update with that query, then catch the exception it will produce is there's a duplicate entry, rather than performing a select statement first.

The code for my page is..

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
&nbsp;
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="TimeCode,Date,Username"
DataSourceID="ObjectDataSource1" Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="TimeCode" HeaderText="TimeCode" ReadOnly="True" SortExpression="TimeCode" />
<asp:BoundField DataField="Date" HeaderText="Date" ReadOnly="True" SortExpression="Date" />
<asp:BoundField DataField="Hours" HeaderText="Hours" SortExpression="Hours" />
<asp:BoundField DataField="Username" HeaderText="Username" ReadOnly="True" SortExpression="Username" />
<asp:BoundField DataField="Comments" HeaderText="Comments" SortExpression="Comments" />
<asp:CheckBoxField DataField="Approved" HeaderText="Approved" SortExpression="Approved" />
<asp:CommandField ShowEditButton="True" />
</Fields>
</asp:DetailsView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
SelectMethod="GetHours" TypeName="ManHoursTableAdapters.tblHoursTableAdapter"
UpdateMethod="UpdateQuery">
<UpdateParameters>
<asp:Parameter Name="TimeCode" Type="Int32" />
<asp:Parameter Name="Date" Type="DateTime" />
<asp:Parameter Name="Hours" Type="Int32" />
<asp:Parameter Name="Username" Type="String" />
<asp:Parameter Name="Comments" Type="String" />
<asp:Parameter Name="Approved" Type="Boolean" />
<asp:Parameter Name="Original_ID" Type="Int32" />
</UpdateParameters>
</asp:ObjectDataSource>
</div>
</form>
</body>
</html>
 
Visage said:
Thats crazy talk. You want to get your application to search through an entire table in order to check something that the DB can do easily with a built in index/key?

You're talking about doing:

1. Accept user data
2. Do a select across the entire table
3. go through each row in the table and check it against the data entered
4. If there's no duplicate, do the insert
5. Handle any error

As opposed to

1. Accept user data
2. Do the insert
3. Handle any error (which may include primary key violations).
Click to expand...

If you read up a few posts I objected to it for that exact reason.

I've tried to create a solution that will still use the compound key, but then simply capture any exception (E.G, it violates primary key unique contraints) and display a message to the user...

Unfortunately i'm having a fair bit of trouble doing it :\
 
akakjs said:
Looking at your problem I'd create a new identity column and use that as your PK. Then you can create a new index including the TimeCode, Date and Username columns and specify the unique constraint on the index. (As suggested by others in this thread)

Your auto-generated queries should be ok now, and if you try and insert a duplicate set of values for TimeCode, Date and Username, you'll get an exception. Not pretty I admit, but it will work.

akakjs
Click to expand...

Thanks, this seems the best way and i've gone with it.

It allows me to keep the intengrity enforced at the database, rather than relying on code to prevent any duplicate entries.
 
You must log in or register to reply here.
Back
Top Bottom