"Ein Element mit demselben Schlüssel wurde bereits hinzugefügt" Fehler bei SSRS beim Versuch, den Datensatz zu setzen
Wenn ich versuche, das Dataset in der SSRS-IDE einzustellen, erhalte ich die Fehlermeldung, die Sie im Snapshot sehen.
Die Abfrage funktioniert in SQL Server Management Studio einwandfrei. Ich frage mich, wo ich falsch liegen kann.
Die Verbindung zur DB ist gut aufgebaut.
WAHLWEISE:
Wenn Sie sich meine Suchanfrage (zu lang) ansehen möchten, habe ich sie sehr gut geprüft. Nichts falsch daran:
SELECT Customer.customerID, Customer.companyName, CustomerInvoice.dueDate, CustomerInvoice.cuInvoiceID, CustomerQuote.PONumber, Product.productName, CASE WHEN (SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 THEN CustomerQuoteProducts.unitPrice * 1.15
WHEN (SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 THEN CustomerQuoteProducts.unitPrice
ELSE CustomerQuoteProducts.unitPrice
END AS "unitPrice",
CustomerQuoteProducts.qty, CustomerQuoteProducts.isTaxPaid, PaymentMethod.paymMethDesc, CustomerInvoice.customerQuoteID, CustomerInvDetail.paidDate, CustomerInvDetail.clearedDate,
CustomerInvDetail.notes, CustomerInvDetail.sentDate, PaymentExpected.payExpectedTitle, PaymentStatus.paymentStatusTitle,
CASE WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * 1.15) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty) * 1.15) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15) * CustomerQuoteProducts.qty) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid > 0 THEN (CustomerQuoteProducts.unitPrice * 1.15) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN (((CustomerQuoteProducts.unitPrice * 1.15)) * CustomerQuoteProducts.qty) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 1 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * 1.15)) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT isTaxPaid
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty > 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN ((CustomerQuoteProducts.unitPrice * CustomerQuoteProducts.qty)) WHEN
(SELECT Count(isTaxPaid)
FROM SupplierQuoteProducts
WHERE productID = CustomerQuoteProducts.ProductID) = 0 AND CustomerQuoteProducts.qty <= 0 AND
CustomerQuoteProducts.isTaxPaid <= 0 THEN (CustomerQuoteProducts.unitPrice) END AS [TotalPrice], CASE WHEN row_number() OVER (partition BY
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaid > 0 THEN (CustomerShipping.shippingPrice * 1.15)
WHEN CustomerShipping.isTaxPaid <= 0 THEN (CustomerShipping.shippingPrice) END) END AS [ShippingCost],
CASE WHEN row_number() OVER (partition BY
CustomerInvoice.cuInvoiceId
ORDER BY newid()) = 1 THEN (CASE WHEN CustomerShipping.isTaxPaidForOrigPr > 0 THEN (CustomerShipping.origShipPrice * 1.15)
WHEN CustomerShipping.isTaxPaidForOrigPr <= 0 THEN (CustomerShipping.origShipPrice) END) END AS [ShippingOrigCost],
CustomerShipping.isTaxPaid,
CustomerShipping.isTaxPaidForOrigPr,
CustomerShipping.shippingDate, CustomerShipping.trackingNumber, ShippingMethod.shippingVia, CustomerShipping.desAddress,
CustomerShipping.desCity, CustomerShipping.desPOBox, CustomerShipping.desPostalCode, CustomerShipping.desProvince, CustomerShipping.descName,
CustomerShipping.packageContent, CustomerShipping.cuShippingID, Country.countryName, CustomerShipping.packageDepth,
CustomerShipping.packageHeight, CustomerShipping.packageWeight, CustomerShipping.packageWidth, CustomerShipping.pickUpLocation
FROM CustomerInvoice INNER JOIN
CustomerInvDetail ON CustomerInvoice.cuInvoiceID = CustomerInvDetail.cuInvoiceID INNER JOIN
CustomerQuote ON CustomerQuote.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
CustomerQuoteProducts ON CustomerQuoteProducts.CustomerQuoteID = CustomerQuote.CustomerQuoteID INNER JOIN
CustomerShipping ON CustomerShipping.CustomerQuoteID = CustomerInvoice.CustomerQuoteID INNER JOIN
PaymentStatus ON PaymentStatus.paymentStatusID = CustomerInvDetail.paymentStatusID INNER JOIN
Customer ON Customer.CustomerID = CustomerQuote.CustomerID INNER JOIN
Product ON CustomerQuoteProducts.productID = Product.productID INNER JOIN
Country ON Country.countryID = CustomerShipping.countryID INNER JOIN
ShippingMethod ON ShippingMethod.shippingMethodID = CustomerShipping.shippingMethodID INNER JOIN
PaymentExpected ON PaymentExpected.paymentExpectedID = CustomerInvDetail.paymentExpectedID INNER JOIN
PaymentMethod ON PaymentMethod.paymentMethodID = CustomerInvoice.paymentMethodID
WHERE CustomerInvoice.cuInvoiceID = @cuInvID
Nachdem ich das Skript ein wenig formatiert hatte, fiel mir auf, dass es zwei Spalten mit demselben Namen gibt, die Sie auswählen. Stellen Sie sicher, dass Sie den endgültigen Namen ändern und dass jede Spalte, wenn Sie Ihre Anweisung in Management Studio ausführen, einen unique name hat.
Allerdings haben die beiden Spalten, die mir aufgefallen sind, doppelte Namen: customerquoteproducts.istaxpaid und customershipping.istaxpaid
Ich hoffe das hilft!
Ich bin heute auf diesen Beitrag gestoßen, als ich dieselbe Ausgabe erhielt. Mein Problem war jedoch ein bisschen anders.
Ich hatte eine temporäre Tabelle und eine If-Anweisung in meinem SP. Also sah mein SP so aus:
DECLARE @myCheck as int
SET @myCheck = 0
SELECT @myCheck = CASE WHEN [checkfield] = 'xxx' then 1 else 0 end
FROM checktable
WHERE ...
SELECT myfields.*
into #temptable
FROM mytable
WHERE ...
IF @myCheck = 1 --if ssrs does'nt want ot refresh your fields, remove hte if as it does not see past this...
select *
FROM #temptable
LEFT JOIN tableA
ON ...
ELSE
select *
FROM #temptable
LEFT JOIN tableB
ON ...
Um meine Felder in SSRS "aufzufrischen", kommentierte ich die If-Anweisung aus und behielt eine meiner Abfragen mit den Feldnamen bei. Ich musste nur daran denken, alles danach wieder hinzuzufügen ...
Verwenden von Visual Studio 2012/SQL 2012