Last post Oct 17, 2009 09:11 PM by samix
Oct 17, 2009 02:14 PM|samix|LINK
I have a weird problem, which is:
I have a gridview in my page running on an Access datasource, every thing running ok but when I try the update buttom, it updates all fields except one field (ItemCapacity), and no error messages or something. I don't know why that.
My aspx code for this part:
<asp:Panel ID="pnlAccItems" runat="server">
<asp:AccessDataSource ID="dsAccCapacity" DataFile="../App_Data/Database/Data.mdb"
UpdateCommand="UPDATE ItemCapacity SET ItemCapacity=@ItemCapacity,Price=@Price WHERE ItemMNumber=@ItemMNumber AND ItemCapacity=@ItemCapacity;"
DeleteCommand="DELETE FROM ItemCapacity WHERE ItemMNumber=@ItemMNumber AND ItemCapacity=@ItemCapacity;" runat="server">
<asp:GridView ID="gvAccCapacity" runat="server" AutoGenerateColumns="False"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" DataSourceID="dsAccCapacity"
DataKeyNames="ItemMNumber,ItemCapacity" Width="500px" Caption="Item Capacities">
<asp:BoundField HeaderText="Capacity" DataField="ItemCapacity" />
<asp:BoundField HeaderText="Price" DataField="Price" />
Capacity: <asp:TextBox ID="txtAccCapacity" runat="server" Width="100"></asp:TextBox>
Price: <asp:TextBox ID="txtAccPrice" runat="server" Width="100"></asp:TextBox>
<asp:Button ID="btAddCapacity" runat="server" Text="Add New Capacity" />
My behind code for this part:
Protected Sub GetAccItems(ByVal strAccID As String)
Protected Sub gvAccCapacity_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles gvAccCapacity.RowCancelingEdit
Protected Sub gvAccCapacity_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles gvAccCapacity.RowDeleting
Protected Sub gvAccCapacity_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles gvAccCapacity.RowEditing
Protected Sub RenderGV(ByVal strAccID As String)
dsAccCapacity.SelectCommand = "SELECT * FROM ItemCapacity WHERE ItemMNumber='" & strAccID & "';"
Protected Sub gvAccCapacity_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles gvAccCapacity.RowUpdating
Oct 17, 2009 07:05 PM|hans_v|LINK
If you change the value of itemcapacity, the original record will not be updated at all, since itemcapacity is part of the WHERE clause. Only if there's another record with the same itemNumber and with an itemcapacity which is the same value as the new value,
this record will be updated, otherwist no record will be updated at all!
Normally, you would have only 1 Primary Key field, in this case I guess ItemNumber will be unique, so this field should be the only field in the datakeynames property and in the WHERE clause of both the update and delete commands...
Oct 17, 2009 08:20 PM|samix|LINK
Yes you are right man, but in my case I have to make the two collumns both act as a primary key, because each ItemMNumber can have a multiple ItemCapacity, so I can duplicate the ItemMNumber, also I can duplicate the ItemCapacity, but I can't duplicate the
same ItemMNumber, ItemCapacity together in one record. For that reason I used the two fields act as a primary key.
There is another note I want to say which is when I click update the Price filed updates OK, and ItemCapacity didn't updates, which means the record accept the update OK, but it updates all fields except ItemCapacity ( weird right!).
Now I didn't know what I can do to resolve it without removing ItemCapacity from DataKeyNames, and my Where clauses.
Oct 17, 2009 08:48 PM|hans_v|LINK
Off course you can use multiple columns as primary key, but the values of primary keys shouldn't be changed! Why not add a Autonumber field which can be used as primary key? Then it would be no problem to change teh values of ItemCapacity...
Oct 17, 2009 09:11 PM|samix|LINK
Ok, I have done it by using Autonumber. Thank you Hans for your help.