Found a bit more information. It's still doing an update. However, it seems that the SELECT that is executed to check if the ID already exists returns multiple rows. It returns more than one row as in my case I have discount lines with an OrderLineParentLineID set. The code than simply takes the first item and tries to update it using code like this:
adapter.Fill(set);
if (set.Tables.get_Item(0).Rows.get_Count() > 0)
{
row = set.Tables.get_Item(0).Rows.get_Item(0);
}
else
{
row = set.Tables.get_Item(0).NewRow();
set.Tables.get_Item(0).Rows.Add(row);
this.ID = NumberGenerator.GetNumber("OL");
}
However, depending on how SQL Server returns the data (it's unsorted in the SQL), this may, or may not return the actual product order line. In my case, apparently, it returns the discount line associated with the product order line, not my product order line itself. The UPDATE statement then tries to set the ID of the OrderLine (see SQL below) which will fail as my product order line already owns that ID.
Here's a trace of the SQL that is sent to SQL Server:
exec sp_executesql N'SELECT * FROM EcomOrderLines WHERE OrderLineID = @OrderLineID OR OrderLineParentLineID = @OrderLineID',N'@OrderLineID nvarchar(5)',@OrderLineID=N'OL567'
exec sp_executesql N'UPDATE [EcomOrderLines] SET [OrderLineID] = @p1, [OrderLineParentLineID] = @p2, [OrderLineDate] = @p3, [OrderLineModified] = @p4, [OrderLineProductNumber] = @p5, [OrderLineProductID] = @p6, [OrderLineProductName] = @p7, [OrderLineType] = @p8, [OrderLineReference] = @p9, [OrderLinePriceWithVAT] = @p10, [OrderLinePriceWithoutVAT] = @p11, [OrderLineUnitPriceWithVAT] = @p12, [OrderLineUnitPriceWithoutVAT] = @p13, [OrderLinePageId] = @p14, [OrderLineDiscountID] = @p15, [OrderLineFieldValues] = @p16, [OrderLineAttachment] = @p17 WHERE (([OrderLineID] = @p18) AND ((@p19 = 1 AND [OrderLineOrderID] IS NULL) OR ([OrderLineOrderID] = @p20)) AND ((@p21 = 1 AND [OrderLineParentLineID] IS NULL) OR ([OrderLineParentLineID] = @p22)) AND ((@p23 = 1 AND [OrderLineBOM] IS NULL) OR ([OrderLineBOM] = @p24)) AND ((@p25 = 1 AND [OrderLineDate] IS NULL) OR ([OrderLineDate] = @p26)) AND ((@p27 = 1 AND [OrderLineModified] IS NULL) OR ([OrderLineModified] = @p28)) AND ((@p29 = 1 AND [OrderLineProductNumber] IS NULL) OR ([OrderLineProductNumber] = @p30)) AND ((@p31 = 1 AND [OrderLineProductID] IS NULL) OR ([OrderLineProductID] = @p32)) AND ((@p33 = 1 AND [OrderLineProductVariantID] IS NULL) OR ([OrderLineProductVariantID] = @p34)) AND ((@p35 = 1 AND [OrderLineProductName] IS NULL) OR ([OrderLineProductName] = @p36)) AND ((@p37 = 1 AND [OrderLineProductVariantText] IS NULL) OR ([OrderLineProductVariantText] = @p38)) AND ((@p39 = 1 AND [OrderLineUnitPrice] IS NULL) OR ([OrderLineUnitPrice] = @p40)) AND ((@p41 = 1 AND [OrderLineVatPercent] IS NULL) OR ([OrderLineVatPercent] = @p42)) AND ((@p43 = 1 AND [OrderLineVatPrice] IS NULL) OR ([OrderLineVatPrice] = @p44)) AND ((@p45 = 1 AND [OrderLineQuantity] IS NULL) OR ([OrderLineQuantity] = @p46)) AND ((@p47 = 1 AND [OrderLineType] IS NULL) OR ([OrderLineType] = @p48)) AND ((@p49 = 1 AND [OrderLineReference] IS NULL) OR ([OrderLineReference] = @p50)) AND ((@p51 = 1 AND [OrderLineBOMItemID] IS NULL) OR ([OrderLineBOMItemID] = @p52)) AND ((@p53 = 1 AND [OrderLineUnitID] IS NULL) OR ([OrderLineUnitID] = @p54)) AND ((@p55 = 1 AND [OrderLineWeight] IS NULL) OR ([OrderLineWeight] = @p56)) AND ((@p57 = 1 AND [OrderLineVolume] IS NULL) OR ([OrderLineVolume] = @p58)) AND ((@p59 = 1 AND [OrderLinePriceWithVAT] IS NULL) OR ([OrderLinePriceWithVAT] = @p60)) AND ((@p61 = 1 AND
[OrderLinePriceWithoutVAT] IS NULL) OR ([OrderLinePriceWithoutVAT] = @p62)) AND ((@p63 = 1 AND [OrderLinePriceVAT] IS NULL) OR ([OrderLinePriceVAT] = @p64)) AND ((@p65 = 1 AND [OrderLinePriceVATPercent] IS NULL) OR ([OrderLinePriceVATPercent] = @p66)) AND ((@p67 = 1
AND [OrderLineUnitPriceWithVAT] IS NULL) OR ([OrderLineUnitPriceWithVAT] = @p68)) AND ((@p69 = 1 AND [OrderLineUnitPriceWithoutVAT] IS NULL) OR ([OrderLineUnitPriceWithoutVAT] = @p70)) AND ((@p71 = 1 AND [OrderLineUnitPriceVAT] IS NULL) OR ([OrderLineUnitPriceVAT] =
@p72)) AND ((@p73 = 1 AND [OrderLineUnitPriceVATPercent] IS NULL) OR ([OrderLineUnitPriceVATPercent] = @p74)) AND ((@p75 = 1 AND [OrderLinePageId] IS NULL) OR ([OrderLinePageId] = @p76)) AND ((@p77 = 1 AND [OrderLineDiscountID] IS NULL) OR ([OrderLineDiscountID] =
@p78)) AND ([OrderLineWishListId] = @p79) AND ((@p80 = 1 AND [OrderLinePriceCalculationReference] IS NULL) OR ([OrderLinePriceCalculationReference] = @p81)) AND ((@p82 = 1 AND [OrderLineUnitPriceCalculationReference] IS NULL) OR
([OrderLineUnitPriceCalculationReference] = @p83)) AND ((@p84 = 1 AND [OrderLineReverseChargeForVat] IS NULL) OR ([OrderLineReverseChargeForVat] = @p85)))',N'@p1 nvarchar(5),@p2 nvarchar(4000),@p3 datetime,@p4 datetime,@p5 nvarchar(8),@p6 nvarchar(6),@p7
nvarchar(22),@p8 nvarchar(1),@p9 nvarchar(153),@p10 float,@p11 float,@p12 float,@p13 float,@p14 int,@p15 nvarchar(4000),@p16 nvarchar(352),@p17 nvarchar(10),@p18 nvarchar(5),@p19 int,@p20 nvarchar(7),@p21 int,@p22 nvarchar(5),@p23 int,@p24 bit,@p25 int,@p26
datetime,@p27 int,@p28 datetime,@p29 int,@p30 nvarchar(4000),@p31 int,@p32 nvarchar(4000),@p33 int,@p34 nvarchar(4000),@p35 int,@p36 nvarchar(4),@p37 int,@p38 nvarchar(4000),@p39 int,@p40 float,@p41 int,@p42 float,@p43 int,@p44 float,@p45 int,@p46 float,@p47 int,@p48
nvarchar(1),@p49 int,@p50 nvarchar(4000),@p51 int,@p52 nvarchar(4000),@p53 int,@p54 nvarchar(4000),@p55 int,@p56 float,@p57 int,@p58 float,@p59 int,@p60 float,@p61 int,@p62 float,@p63 int,@p64 float,@p65 int,@p66 float,@p67 int,@p68 float,@p69 int,@p70 float,@p71 int,@p72 float,@p73 int,@p74 float,@p75 int,@p76 int,@p77 int,@p78 nvarchar(12),@p79 int,@p80 int,@p81 nvarchar(34),@p82 int,@p83 nvarchar(38),@p84 int,@p85 bit',@p1=N'OL567',@p2=N'',@p3='2014-08-27 14:07:09.410',@p4='2014-08-27 14:07:09.530',@p5=N'W4920XTG',@p6=N'PROD31',@p7=N'3DBOXX 4920 XTREME GPU',@p8=N'2',@p9=N'http://scooter.localtest.me/products/workstations/single-socket/3dboxx-4920-xtreme-gpu?ConfigurationName=2&SessionId=434f892d-b484-487e-879f-646e522e2932',@p10=2433,@p11=2433,@p12=2433,@p13=2433,@p14=2,@p15=N'',@p16=N'<OrderLineFieldValueCollection><OrderLineFieldValue><OrderLineFieldSystemName>ConfigurationName</OrderLineFieldSystemName><Value>2</Value></OrderLineFieldValue><OrderLineFieldValue><OrderLineFieldSystemName>GuruSessionId</OrderLineFieldSystemName><Value>434f892d-b484-487e-879f-646e522e2932</Value></OrderLineFieldValue></OrderLineFieldValueCollection>',@p17=N'Some value',@p18=N'OL585',@p19=0,@p20=N'ORDER55',@p21=0,@p22=N'OL567',@p23=0,@p24=0,@p25=0,@p26='2014-08-27 14:08:08.543',@p27=0,@p28='2014-08-27 14:08:08.543',@p29=0,@p30=N'',@p31=0,@p32=N'',@p33=0,@p34=N'',@p35=0,@p36=N'Test',@p37=0,@p38=N'',@p39=1,@p40=NULL,@p41=1,@p42=NULL,@p43=1,@p44=NULL,@p45=0,@p46=1,@p47=0,@p48=N'3',@p49=0,@p50=N'',@p51=0,@p52=N'',@p53=0,@p54=N'',@p55=0,@p56=0,@p57=0,@p58=0,@p59=0,@p60=-20,@p61=0,@p62=-20,@p63=0,@p64=0,@p65=0,@p66=0,@p67=0,@p68=-20,@p69=0,@p70=-20,@p71=0,@p72=0,@p73=0,@p74=0,@p75=0,@p76=0,@p77=0,@p78=N'SALESDISCNT2',@p79=0,@p80=0,@p81=N'OrderLinePriceCalculationReference',@p82=0,@p83=N'OrderLineUnitPriceCalculationReference',@p84=0,@p85=0
Can this be looked at please? It's blocking me from doing a whole bunch of other things!
Thanks,
Imar
