 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / ADO.NET / January 2006SqlDataSource and Optimistic Concurrency Problems
Hi, I'm using a detailsview control with an SqlDataSource control. My Update query isn't working, and I've narrowed it down to the optimistic concurrency parameters - i.e. when I comment them out of the query, it works. What seems to be happening is that if a field is NULL when it is read in through the select statement, it's causing the update statment not to find a match. I've got ConvertEmptyStringToNull not set (so it should default to true), but I can't get the update to work all the time and it's driving me crazy. Here's my code: Stored Procedure for Select as follows (the Update's going in a Stored Procedure when I can get it to work...): SELECT Customers.CustID, Customers.CustDisplayName, Customers.CustFirstName, Customers.CustSurname, Customers.CustType, Customers.CustNotes, Customers.CustIsBillingSame, Customers.CustNumEmployees, Customers.CustIsProspect, Customers.CustOnHold, Customers.CustPersonID, Customers.CustUserFieldB1, Customers.CustUserFieldB2, Customers.CustUserFieldB3, Customers.CustUserFieldD1, Customers.CustUserFieldD2, Customers.CustUserFieldD3, Customers.CustUserFieldI1, Customers.CustUserFieldI2, Customers.CustUserFieldI3, Customers.CustCategory, Customers.CustInactive, CustomerContactDetails_1.CustContactStreet1, CustomerContactDetails_1.CustContactStreet2, CustomerContactDetails_1.CustContactStreet3, CustomerContactDetails_1.CustContactTown, CustomerContactDetails_1.CustContactCounty, CustomerContactDetails_1.CustContactPostCode, CustomerContactDetails_1.CustContactCountry, CustomerContactDetails_1.CustContactTelephone, CustomerContactDetails_1.CustContactMobile, CustomerContactDetails_1.CustContactEMail, CustomerContactDetails_1.CustContactFax, CustomerContactDetails.CustContactStreet1 AS CustBContactStreet1, CustomerContactDetails.CustContactStreet2 AS CustBContactStreet2, CustomerContactDetails.CustContactStreet3 AS CustBContactStreet3, CustomerContactDetails.CustContactTown AS CustBContactTown, CustomerContactDetails.CustContactCounty AS CustBContactCounty, CustomerContactDetails.CustContactPostCode AS CustBContactPostCode, CustomerContactDetails.CustContactCountry AS CustBContactCountry, CustomerContactDetails.CustContactTelephone AS CustBContactTelephone, CustomerContactDetails.CustContactMobile AS CustBContactMobile, CustomerContactDetails.CustContactEMail AS CustBContactEMail, CustomerContactDetails.CustContactFax AS CustBContactFax FROM Customers LEFT OUTER JOIN CustomerContactDetails AS CustomerContactDetails_1 ON Customers.CustAddressID = CustomerContactDetails_1.CustContactID LEFT OUTER JOIN CustomerContactDetails ON Customers.CustBillingID = CustomerContactDetails.CustContactID WHERE (Customers.CustID = @CustID) <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:FocusSystemsConnectionString %>" OldValuesParameterFormatString="original_{0}" ProviderName="<%$ ConnectionStrings:FocusSystemsConnectionString.ProviderName %>" SelectCommand="SelectCustomersAndAddresses" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE [Customers] SET [CustDisplayName] = @CustDisplayName, [CustFirstName] = @CustFirstName, [CustSurname] = @CustSurname, [CustType] = @CustType, [CustNotes] = @CustNotes, [CustIsBillingSame] = @CustIsBillingSame, [CustNumEmployees] = @CustNumEmployees, [CustIsProspect] = @CustIsProspect, [CustOnHold] = @CustOnHold, [CustPersonID] = @CustPersonID, [CustUserFieldB1] = @CustUserFieldB1, [CustUserFieldB2] = @CustUserFieldB2, [CustUserFieldB3] = @CustUserFieldB3, [CustUserFieldD1] = @CustUserFieldD1, [CustUserFieldD2] = @CustUserFieldD2, [CustUserFieldD3] = @CustUserFieldD3, [CustUserFieldI1] = @CustUserFieldI1, [CustUserFieldI2] = @CustUserFieldI2, [CustUserFieldI3] = @CustUserFieldI3, [CustCategory] = @CustCategory, [CustInactive] = @CustInactive WHERE [CustID] = @original_CustID AND [CustDisplayName] = @original_CustDisplayName AND [CustFirstName] = @original_CustFirstName AND [CustSurname] = @original_CustSurname AND [CustType] = @original_CustType AND [CustIsBillingSame] = @original_CustIsBillingSame AND [CustNumEmployees] = @original_CustNumEmployees AND [CustIsProspect] = @original_CustIsProspect AND [CustOnHold] = @original_CustOnHold AND [CustPersonID] = @original_CustPersonID AND [CustCategory] = @original_CustCategory AND [CustInactive] = @original_CustInactive" EnableViewState="true"> <UpdateParameters> <asp:Parameter Name="CustDisplayName" Type="String" /> <asp:Parameter Name="CustFirstName" Type="String" /> <asp:Parameter Name="CustSurname" Type="String" /> <asp:Parameter Name="CustType" Type="String" /> <asp:Parameter Name="CustNotes" Type="String" /> <asp:Parameter Name="CustIsBillingSame" Type="Boolean" /> <asp:Parameter Name="CustNumEmployees" Type="Int32" /> <asp:Parameter Name="CustIsProspect" Type="Boolean" /> <asp:Parameter Name="CustOnHold" Type="Boolean" /> <asp:Parameter Name="CustPersonID" /> <asp:Parameter Name="CustUserFieldB1" Type="String" /> <asp:Parameter Name="CustUserFieldB2" Type="String" /> <asp:Parameter Name="CustUserFieldB3" Type="String" /> <asp:Parameter Name="CustUserFieldD1" Type="String" /> <asp:Parameter Name="CustUserFieldD2" Type="String" /> <asp:Parameter Name="CustUserFieldD3" Type="String" /> <asp:Parameter Name="CustUserFieldI1" Type="String" /> <asp:Parameter Name="CustUserFieldI2" Type="String" /> <asp:Parameter Name="CustUserFieldI3" Type="String" /> <asp:Parameter Name="CustCategory" Type="String" /> <asp:Parameter Name="CustInactive" Type="Boolean" /> <asp:Parameter Name="original_CustID" /> <asp:Parameter Name="original_CustDisplayName" /> <asp:Parameter Name="original_CustFirstName" /> <asp:Parameter Name="original_CustSurname" /> <asp:Parameter Name="original_CustType" /> <asp:Parameter Name="original_CustNotes" /> <asp:Parameter Name="original_CustIsBillingSame" /> <asp:Parameter Name="original_CustNumEmployees" /> <asp:Parameter Name="original_CustIsProspect" /> <asp:Parameter Name="original_CustOnHold" /> <asp:Parameter Name="original_CustPersonID" /> <asp:Parameter Name="original_CustUserFieldB1" /> <asp:Parameter Name="original_CustUserFieldB2" /> <asp:Parameter Name="original_CustUserFieldB3" /> <asp:Parameter Name="original_CustUserFieldD1" /> <asp:Parameter Name="original_CustUserFieldD2" /> <asp:Parameter Name="original_CustUserFieldD3" /> <asp:Parameter Name="original_CustUserFieldI1" /> <asp:Parameter Name="original_CustUserFieldI2" /> <asp:Parameter Name="original_CustUserFieldI3" /> <asp:Parameter Name="original_CustCategory" /> <asp:Parameter Name="original_CustInactive" /> <asp:Parameter Name="CustContactStreet1" Type="String" /> <asp:Parameter Name="CustContactStreet2" Type="String" /> <asp:Parameter Name="CustContactStreet3" Type="String" /> <asp:Parameter Name="CustContactTown" Type="String" /> <asp:Parameter Name="CustContactCounty" Type="String" /> <asp:Parameter Name="CustContactPostCode" Type="String" /> <asp:Parameter Name="CustContactCountry" Type="String" /> <asp:Parameter Name="CustContactTelephone" Type="String" /> <asp:Parameter Name="CustContactMobile" Type="String" /> <asp:Parameter Name="CustContactEMail" Type="String" /> <asp:Parameter Name="CustContactFax" Type="String" /> <asp:Parameter Name="original_CustContactStreet1" Type="String" /> <asp:Parameter Name="original_CustContactStreet2" Type="String" /> <asp:Parameter Name="original_CustContactStreet3" Type="String" /> <asp:Parameter Name="original_CustContactTown" Type="String" /> <asp:Parameter Name="original_CustContactCounty" Type="String" /> <asp:Parameter Name="original_CustContactPostCode" Type="String" /> <asp:Parameter Name="original_CustContactCountry" Type="String" /> <asp:Parameter Name="original_CustContactTelephone" Type="String" /> <asp:Parameter Name="original_CustContactMobile" Type="String" /> <asp:Parameter Name="original_CustContactEMail" Type="String" /> <asp:Parameter Name="original_CustContactFax" Type="String" /> <asp:Parameter Name="CustBContactStreet1" Type="String" /> <asp:Parameter Name="CustBContactStreet2" Type="String" /> <asp:Parameter Name="CustBContactStreet3" Type="String" /> <asp:Parameter Name="CustBContactTown" Type="String" /> <asp:Parameter Name="CustBContactCounty" Type="String" /> <asp:Parameter Name="CustBContactPostCode" Type="String" /> <asp:Parameter Name="CustBContactCountry" Type="String" /> <asp:Parameter Name="CustBContactTelephone" Type="String" /> <asp:Parameter Name="CustBContactMobile" Type="String" /> <asp:Parameter Name="CustBContactEMail" Type="String" /> <asp:Parameter Name="CustBContactFax" Type="String" /> <asp:Parameter Name="original_CustBContactStreet1" Type="String" /> <asp:Parameter Name="original_CustBContactStreet2" Type="String" /> <asp:Parameter Name="original_CustBContactStreet3" Type="String" /> <asp:Parameter Name="original_CustBContactTown" Type="String" /> <asp:Parameter Name="original_CustBContactCounty" Type="String" /> <asp:Parameter Name="original_CustBContactPostCode" Type="String" /> <asp:Parameter Name="original_CustBContactCountry" Type="String" /> <asp:Parameter Name="original_CustBContactTelephone" Type="String" /> <asp:Parameter Name="original_CustBContactMobile" Type="String" /> <asp:Parameter Name="original_CustBContactEMail" Type="String" /> <asp:Parameter Name="original_CustBContactFax" Type="String" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="CustNameSelect" Name="CustID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> </td> </tr> <tr><td> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource2" Height="50px" Width="125px" DataKeyNames="CustID"> <Fields> <asp:BoundField DataField="CustID" HeaderText="CustID" ReadOnly="True" SortExpression="CustID" /> <asp:BoundField DataField="CustDisplayName" HeaderText="CustDisplayName" SortExpression="CustDisplayName" /> <asp:BoundField DataField="CustFirstName" HeaderText="CustFirstName" SortExpression="CustFirstName" /> <asp:BoundField DataField="CustSurname" HeaderText="CustSurname" SortExpression="CustSurname" /> <asp:BoundField DataField="CustType" HeaderText="CustType" SortExpression="CustType" /> <asp:BoundField DataField="CustNotes" HeaderText="CustNotes" SortExpression="CustNotes" /> <asp:CheckBoxField DataField="CustIsBillingSame" HeaderText="CustIsBillingSame" SortExpression="CustIsBillingSame" /> <asp:BoundField DataField="CustNumEmployees" HeaderText="CustNumEmployees" SortExpression="CustNumEmployees" /> <asp:CheckBoxField DataField="CustIsProspect" HeaderText="CustIsProspect" SortExpression="CustIsProspect" /> <asp:CheckBoxField DataField="CustOnHold" HeaderText="CustOnHold" SortExpression="CustOnHold" /> <asp:BoundField DataField="CustPersonID" HeaderText="CustPersonID" SortExpression="CustPersonID" /> <asp:BoundField DataField="CustUserFieldB1" HeaderText="CustUserFieldB1" SortExpression="CustUserFieldB1" /> <asp:BoundField DataField="CustUserFieldB2" HeaderText="CustUserFieldB2" SortExpression="CustUserFieldB2" /> <asp:BoundField DataField="CustUserFieldB3" HeaderText="CustUserFieldB3" SortExpression="CustUserFieldB3" /> <asp:BoundField DataField="CustUserFieldD1" HeaderText="CustUserFieldD1" SortExpression="CustUserFieldD1" /> <asp:BoundField DataField="CustUserFieldD2" HeaderText="CustUserFieldD2" SortExpression="CustUserFieldD2" /> <asp:BoundField DataField="CustUserFieldD3" HeaderText="CustUserFieldD3" SortExpression="CustUserFieldD3" /> <asp:BoundField DataField="CustUserFieldI1" HeaderText="CustUserFieldI1" SortExpression="CustUserFieldI1" /> <asp:BoundField DataField="CustUserFieldI2" HeaderText="CustUserFieldI2" SortExpression="CustUserFieldI2" /> <asp:BoundField DataField="CustUserFieldI3" HeaderText="CustUserFieldI3" SortExpression="CustUserFieldI3" /> <asp:BoundField DataField="CustCategory" HeaderText="CustCategory" SortExpression="CustCategory" /> <asp:CheckBoxField DataField="CustInactive" HeaderText="CustInactive" SortExpression="CustInactive" /> <asp:BoundField DataField="CustContactStreet1" HeaderText="CustContactStreet1" SortExpression="CustContactStreet1" /> <asp:BoundField DataField="CustContactStreet2" HeaderText="CustContactStreet2" SortExpression="CustContactStreet2" /> <asp:BoundField DataField="CustContactStreet3" HeaderText="CustContactStreet3" SortExpression="CustContactStreet3" /> <asp:BoundField DataField="CustContactTown" HeaderText="CustContactTown" SortExpression="CustContactTown" /> <asp:BoundField DataField="CustContactCounty" HeaderText="CustContactCounty" SortExpression="CustContactCounty" /> <asp:BoundField DataField="CustContactPostCode" HeaderText="CustContactPostCode" SortExpression="CustContactPostCode" /> <asp:BoundField DataField="CustContactCountry" HeaderText="CustContactCountry" SortExpression="CustContactCountry" /> <asp:BoundField DataField="CustContactTelephone" HeaderText="CustContactTelephone" SortExpression="CustContactTelephone" /> <asp:BoundField DataField="CustContactMobile" HeaderText="CustContactMobile" SortExpression="CustContactMobile" /> <asp:BoundField DataField="CustContactEMail" HeaderText="CustContactEMail" SortExpression="CustContactEMail" /> <asp:BoundField DataField="CustContactFax" HeaderText="CustContactFax" SortExpression="CustContactFax" /> <asp:BoundField DataField="CustBContactStreet1" HeaderText="CustBContactStreet1" SortExpression="CustBContactStreet1" /> <asp:BoundField DataField="CustBContactStreet2" HeaderText="CustBContactStreet2" SortExpression="CustBContactStreet2" /> <asp:BoundField DataField="CustBContactStreet3" HeaderText="CustBContactStreet3" SortExpression="CustBContactStreet3" /> <asp:BoundField DataField="CustBContactTown" HeaderText="CustBContactTown" SortExpression="CustBContactTown" /> <asp:BoundField DataField="CustBContactCounty" HeaderText="CustBContactCounty" SortExpression="CustBContactCounty" /> <asp:BoundField DataField="CustBContactPostCode" HeaderText="CustBContactPostCode" SortExpression="CustBContactPostCode" /> <asp:BoundField DataField="CustBContactCountry" HeaderText="CustBContactCountry" SortExpression="CustBContactCountry" /> <asp:BoundField DataField="CustBContactTelephone" HeaderText="CustBContactTelephone" SortExpression="CustBContactTelephone" /> <asp:BoundField DataField="CustBContactMobile" HeaderText="CustBContactMobile" SortExpression="CustBContactMobile" /> <asp:BoundField DataField="CustBContactEMail" HeaderText="CustBContactEMail" SortExpression="CustBContactEMail" /> <asp:BoundField DataField="CustBContactFax" HeaderText="CustBContactFax" SortExpression="CustBContactFax" /> <asp:CommandField ShowEditButton="True" /> </Fields> </asp:DetailsView> When you say it isn't working, is it throwing an exception or is it just not updating. From the looks of the Update query if I'm reading it correctly, there's no where clause to specify which row you're going to update. Is that intentional? > Hi, > [quoted text clipped - 356 lines] > </Fields> > </asp:DetailsView> >When you say it isn't working, is it throwing an exception or is it just not >updating. From the looks of the Update query if I'm reading it correctly, >there's no where clause to specify which row you're going to update. Is that >intentional? It's there but almost hidden in the formatting ;o) See below. >> Hi, >> [quoted text clipped - 57 lines] >> SelectCommand="SelectCustomersAndAddresses" >> SelectCommandType="StoredProcedure" Here is the update command, and .... >> UpdateCommand="UPDATE [Customers] SET >> [CustDisplayName] = @CustDisplayName, [CustFirstName] = @CustFirstName, [quoted text clipped - 8 lines] >> [CustUserFieldI1] = @CustUserFieldI1, [CustUserFieldI2] = >> @CustUserFieldI2, [CustUserFieldI3] = @CustUserFieldI3, [CustCategory] Here is the where clause... >> = @CustCategory, [CustInactive] = @CustInactive WHERE [CustID] = >> @original_CustID AND [CustDisplayName] = @original_CustDisplayName AND [quoted text clipped - 283 lines] >> </Fields> >> </asp:DetailsView> Otis Mukinfus http://www.otismukinfus.com http://www.tomchilders.com It's just not updating. I'm going to try a timestamp concurrency approach instead (came to me at 4AM this morning), unless anyone can better advise! Thanks, Stuart Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.