I'm having a little problem with data corrupting when updating tables from a GridView. I must admit this is my first attempt at asp.net but I am reasonably familiar with C#.
Firstly, some background. We have some 3rd party inventory/order processing software that stores its data in MSSQL. I am using a GridView and an sql query to extract current work orders and display them on a table. The sql query also combines another table in a database I created (tblProdStatus) that adds additional status information about the work order.
The web page allows editing so that the status of the work order can be updated and these changes need to be written back to the table (tblProdStatus). The asp.GridView has the UpdateCommand defined with appropriate parameters. I've also defined the DataKeyNames to identify the row being updated.
Everything works as expected, I can Edit a row, update fields, select Update and have the information correctly written to the table (tblProdStatus).
My problem is when the underlying data is changed in the 3rd party software while the edit is in progress. The data ends up getting associated with the wrong record, either shifted up or down a row depending if the 3rd software added or deleted a row.
The 3rd party software has an AllocID key that is associated with one work order, and does not change for the duration. I link this to the same number stored in my table (tblProdStatus) to tie the two tables together. When the corruption occurs the sql update is passed the wrong AllocID for the row being edited. It's almost as if the table is refreshed when the Update link is clicked and then data is submitted afterwards using the wrong row.
There is no real code behind for the logic, it's all in the aspx file. Which I have posted, and will hopefully not be too embarrassing for myself.
I use stackoverflow a lot and always seem to be able to find guidance from others in my problems, so this is my first post of an actual question. Many thanks in advance for any advice.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="prodStatus.aspx.cs" Inherits="ProdStatus.prodStatus" EnableViewState="false" %>
<!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></title>
<link href="style.css" rel="stylesheet" type="text/css" />
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvProdStatus" runat="server" AutoGenerateColumns="False" DataKeyNames="AllocID"
DataSourceID="SqlDS" onrowdatabound="gvProdStatus_RowDataBound"
CssClass="table" EnableViewState="False" >
<Columns>
<asp:BoundField DataField="AllocID" HeaderText="AllocID" InsertVisible="False"
ReadOnly="True" SortExpression="AllocID" Visible="false" />
<asp:BoundField DataField="AllocWONo" HeaderText="Works Order"
ReadOnly="True" SortExpression="Works Order" />
<asp:BoundField DataField="ShortNm" HeaderText="Cust"
ReadOnly="True" SortExpression="Cust" />
<asp:BoundField DataField="CustPONo" HeaderText="Cust PO"
ReadOnly="True" SortExpression="Cust PO" />
<asp:BoundField DataField="MasterPNo" HeaderText="Part Number"
ReadOnly="True" SortExpression="Part Number" />
<asp:BoundField DataField="ItemDescription" HeaderText="Description"
ReadOnly="True" SortExpression="Description" />
<asp:BoundField DataField="AllocQty" HeaderText="Qty"
ReadOnly="True" SortExpression="Qty" DataFormatString="{0:G29}" />
<asp:BoundField DataField="ReqdDate" HeaderText="Due"
ReadOnly="True" SortExpression="Due" DataFormatString="{0:d/MMM/yy}" />
<asp:TemplateField HeaderText="Done" SortExpression="state0">
<ItemTemplate>
<asp:CheckBox ID="state0" runat="server" Enabled="false"
Checked='<%#Eval("state0") %>'
Visible='<%#Eval("mask0") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state0" runat="server"
Checked='<%#Bind("state0") %>'
Visible='<%#Eval("mask0") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/lfp.jpg' alt='Laser Faceplate' />" SortExpression="state1">
<ItemTemplate>
<asp:CheckBox ID="state1" runat="server" Enabled="false"
Checked='<%#Eval("state1") %>'
Visible='<%#Eval("mask1") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state1" runat="server"
Checked='<%#Bind("state1") %>'
Visible='<%#Eval("mask1") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/lcv.jpg' alt='Laser Cover' />" SortExpression="state2">
<ItemTemplate>
<asp:CheckBox ID="state2" runat="server" Enabled="false"
Checked='<%#Eval("state2") %>'
Visible='<%#Eval("mask2") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state2" runat="server"
Checked='<%#Bind("state2") %>'
Visible='<%#Eval("mask2") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/hdw.jpg' alt='Hardware Pack' />" SortExpression="state3">
<ItemTemplate>
<asp:CheckBox ID="state3" runat="server" Enabled="false"
Checked='<%#Eval("state3") %>'
Visible='<%#Eval("mask3") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state3" runat="server"
Checked='<%#Bind("state3") %>'
Visible='<%#Eval("mask3") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/str.jpg' alt='Strike Plate' />" SortExpression="state4">
<ItemTemplate>
<asp:CheckBox ID="state4" runat="server" Enabled="false"
Checked='<%#Eval("state4") %>'
Visible='<%#Eval("mask4") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state4" runat="server"
Checked='<%#Bind("state4") %>'
Visible='<%#Eval("mask4") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/box.jpg' alt='Box' />" SortExpression="state5">
<ItemTemplate>
<asp:CheckBox ID="state5" runat="server" Enabled="false"
Checked='<%#Eval("state5") %>'
Visible='<%#Eval("mask5") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state5" runat="server"
Checked='<%#Bind("state5") %>'
Visible='<%#Eval("mask5") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/ins.jpg' alt='Instructions' />" SortExpression="state6">
<ItemTemplate>
<asp:CheckBox ID="state6" runat="server" Enabled="false"
Checked='<%#Eval("state6") %>'
Visible='<%#Eval("mask6") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state6" runat="server"
Checked='<%#Bind("state6") %>'
Visible='<%#Eval("mask6") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="<img src='./images/pcb.jpg' alt='PCBs' />" SortExpression="state7">
<ItemTemplate>
<asp:CheckBox ID="state7" runat="server" Enabled="false"
Checked='<%#Eval("state7") %>'
Visible='<%#Eval("mask7") %>' />
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox ID="state7" runat="server"
Checked='<%#Bind("state7") %>'
Visible='<%#Eval("mask7") %>' />
</EditItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Note" SortExpression="Note">
<ItemTemplate>
<asp:Label ID="notes" runat="server" Text='<%# Eval("notes") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="notes" runat="server" Text='<%# Bind("notes") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
<asp:CommandField ShowEditButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDS" runat="server"
ConnectionString="<%$ ConnectionStrings:worksOrdersConnectionString %>"
SelectCommand = "SELECT [AllocID], [tblCustOrders].[CustORID], [tblStockItems].[ItemID], [ReqdDate], [AllocWONo], [ShortNm], [tblAlloc].[AllocQty], [tblCustOrders].[CustPONo], [MasterPNo], [ItemDescription],
ISNULL([mask0], 0) as [mask0],
ISNULL([mask1], 0) as [mask1],
ISNULL([mask2], 0) as [mask2],
ISNULL([mask3], 0) as [mask3],
ISNULL([mask4], 0) as [mask4],
ISNULL([mask5], 0) as [mask5],
ISNULL([mask6], 0) as [mask6],
ISNULL([mask7], 0) as [mask7],
ISNULL([state0], 0) as [state0],
ISNULL([state1], 0) as [state1],
ISNULL([state2], 0) as [state2],
ISNULL([state3], 0) as [state3],
ISNULL([state4], 0) as [state4],
ISNULL([state5], 0) as [state5],
ISNULL([state6], 0) as [state6],
ISNULL([state7], 0) as [state7],
ISNULL([notes], '') as [notes]
FROM (((((([miniMrpDB].[dbo].[tblAlloc]
INNER JOIN [miniMrpDB].[dbo].[tblSalesOrderDetail] ON [tblSalesOrderDetail].[RowID] = [tblAlloc].[SalesRowID])
INNER JOIN [miniMrpDB].[dbo].[tblStockItems] ON [tblStockItems].[ItemID] = [tblSalesOrderDetail].[StockID])
INNER JOIN [miniMrpDB].[dbo].[tblCustOrders] ON [tblCustOrders].[CustORID] = [tblAlloc].[CustORID])
INNER JOIN [miniMrpDB].[dbo].[tblCusAddresses] ON [tblCustOrders].[CustID] = [tblCusAddresses].[AddID])
LEFT JOIN [worksOrders].[dbo].[tblItemMaskBits] ON [tblStockItems].[ItemID] = [tblItemMaskBits].[ItemID])
LEFT JOIN [worksOrders].[dbo].[tblProdStatus] ON [AllocID] = [tblAllocID])
WHERE (Status IS NULL OR Status < 3) AND ([tblAlloc].[CustORID] > -1) AND ([AllocID] < 999999999) ORDER BY [ReqdDate], [ShortNm], [CustPONo], [AllocWONo]"
UpdateCommand = "BEGIN TRAN
IF EXISTS (SELECT * from [worksOrders].[dbo].[tblProdStatus] WITH (updlock,serializable) WHERE [tblAllocID] = (@AllocID))
BEGIN
UPDATE [worksOrders].[dbo].[tblProdStatus] SET
[state0] = (@State0),
[state1] = (@State1),
[state2] = (@State2),
[state3] = (@State3),
[state4] = (@State4),
[state5] = (@State5),
[state6] = (@State6),
[state7] = (@State7),
[notes] = (@Notes)
WHERE [tblAllocID] = (@AllocID)
END
ELSE
BEGIN
INSERT [worksOrders].[dbo].[tblProdStatus] ([tblAllocID], [state0], [state1], [state2], [state3], [state4], [state5], [state6], [state7], [notes])
VALUES ((@AllocID), (@State0), (@State1), (@State2), (@State3), (@State4), (@State5), (@State6), (@State7), (@Notes))
END
COMMIT TRAN" >
<UpdateParameters>
<asp:Parameter Type="Int32" Name="AllocID"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State0"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State1"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State2"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State3"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State4"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State5"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State6"></asp:Parameter>
<asp:Parameter Type="Boolean" Name="State7"></asp:Parameter>
<asp:Parameter Type="String" Name="Notes"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
</div>
<br />
<div>
<asp:Button ID="btnReload" runat="server" Text="Reload"
onclick="btnReload_Click" />
<a id="countdown"></p>
</div>
<script>
<!--
(function countdown(remaining) {
if (remaining === 0)
location.reload(true);
document.getElementById('countdown').innerHTML = remaining;
setTimeout(function () { countdown(remaining - 1); }, 1000);
})(600);
//-->
</script>
</form>
</body>
</html>