Solved

An item with the same key has already been added in ssrs 2008

Posted on 2011-02-18
10
1,221 Views
Last Modified: 2012-05-11
I GOT THIS ERROR WHILE DEVELOPING REPORTS IN SSRS 2008R2
0
Comment
Question by:sqlcurious
  • 5
  • 5
10 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 34930413
Sorry, You have to gives us more info than that.
What key are you talking about?
0
 

Author Comment

by:sqlcurious
ID: 34931516
it is related to ssrs 2008 r2 I  tried to create a dataset in the query window.

dbsp_report_projectdetailcost

@leetingdatefrom,
@lettingdateto,
@customername,
@divisionname

and after clicking ok,it prompts the message an item with the same key has already been added
0
 
LVL 27

Expert Comment

by:planocz
ID: 34933357
Can you post your query so we can see if there is a problem in the way it is written.
0
 

Author Comment

by:sqlcurious
ID: 34933385

 I wrote the stored proc and passed the variables mentioned above.whenever you write the stored proc and give the parameters name it should populate the fields that are used for reporting.It works fine for other proc.

-- exec DBSP_Report_LettingDate '01/01/2006','01/01/2011','1',''
-- Description:      <Procedure for Report by Letting Date >
-- =============================================
CREATE  PROCEDURE [dbo].[DBSP_Report_LettingDate]
      (
      @LettingDateFrom datetime,
      @LettingDateTo datetime,
      @ProjectTxDot int =2,
      @CustomerName varchar(50),
      @DivisionName varchar(250) = NULL
      
)
      
AS
BEGIN
DECLARE @SQL VARCHAR(8000)
 Set @SQL= 'SELECT
 CU.CustomerID,
 CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,
P.projectTxdot,
D.Divisionname As DivisionName,
P.projectCountyID As CountyID,
C.CountyName As CountyName,
CU.CustomerName As CustomerName,
CU.customerID,
QI.QuoteItemQuantity as Quantity,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
(QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,
QI.QuoteItemWeight AS ItemWeight,
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q.QuoteFreightRate as FreightRate,
Q.QuoteNumberOfTrucks,
Q.QuoteSurcharge1,
Q.QuoteSurcharge2,
Q.QuoteSurcharge3,
((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight

        FROM TBL_Ebid_Project P
            INNER JOIN  TBL_Quotes1 Q ON P.ProjectID = Q.ProjectID
            inner   JOIN  TBL_County C ON C.CountyID = P.ProjectCountyID
            inner JOIN  TBL_QuoteItems QI ON Q.QuoteID = QI.QuoteID
            inner  JOIN  TBL_Division D ON D.createdby = Q.QuoteCreatedby
            --INNER JOIN  TBL_CustomerQuotes CU ON CU.QuoteID = Q.QuoteID      
            inner join tbl_customer cu on cu.customercreatedby=Q.QuoteCreatedby'
            
      
            
If @LettingDateFrom Is Not Null
      Begin
            Set @SQL = @SQL + ' And p.projectstartdate >='''+Cast(@LettingDateFrom As Varchar(30))+''''
      End

If @LettingDateTo Is Not Null
      Begin
            Set @SQL = @SQL + ' And p.projectstartdate <='''+Cast(@LettingDateTo As Varchar(30))+''''
      End
      

if @DivisionName <> ''

 begin
 set @SQL= @Sql + ' And D.DivisionName LIKE ''%'+@DivisionName+'%'''  
end

if (@projectTxDot <> 2 )
 begin  
  SET @SQL =  @SQL + ' and p.ProjectTxDot =  '+ convert(varchar(5), @projectTxDot)
end

IF  @CustomerName <> ''
begin
 set @sql=@sql + ' And CU.CustomerName LIKE ''%'+@CustomerName+'%'''  
end

print @sql
exec (@sql)

END
0
 
LVL 27

Expert Comment

by:planocz
ID: 34935335
I mat not have this just right, but go ahead and run it
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 27

Expert Comment

by:planocz
ID: 34935340


-- exec DBSP_Report_LettingDate '01/01/2006','01/01/2011','1',''
-- Description:      <Procedure for Report by Letting Date >
-- =============================================
CREATE  PROCEDURE [dbo].[DBSP_Report_LettingDate]
      (
      @StartDate      datetime,
      @EndDate            datetime,
      @ProjectTxDot int =2,
      @CustomerName varchar(50),
      @DivisionName varchar(250) = NULL    
)
     
AS
Set NOCOUNT ON

If RTRIM(isnull(@StartDate,'')) = ''
Begin
      Select @StartDate = '%'
End
Else
Begin
      Select @StartDate = '%' + @StartDate + '%'
End
If RTRIM(isnull(@EndDate,'')) = ''
Begin
      Select @EndDate = '%'
End
Else
Begin
      Select @EndDate = '%' + @EndDate + '%'
End
If RTRIM(isnull(@DivisionName,'')) = ''
Begin
      Select @DivisionName = '%'
End
Else
Begin
      Select @DivisionName = '%' + @DivisionName + '%'
End
If RTRIM(isnull(@CustomerName,'')) = ''
Begin
      Select @CustomerName = '%'
End
Else
Begin
      Select @CustomerName = '%' + @CustomerName + '%'
End

BEGIN
SELECT
 CU.CustomerID,
 CU.CustomerName,
P.ProjectID ,
P.Projectname,
p.ProjectNumber,
P.ProjectCountyID,
convert(varchar(10),P.ProjectStartDate,110) AS ProjectStartDate,
P.projectTxdot,
D.Divisionname As DivisionName,
P.projectCountyID As CountyID,
C.CountyName As CountyName,
CU.CustomerName As CustomerName,
CU.customerID,
QI.QuoteItemQuantity as Quantity,
QI.QuoteItem_ItemNumber AS Item#,
QI.QuoteItemDescription As ItemDescription,
QI.QuoteItemQuantity As ItemQuantity,
QI.QuoteItemUnit As ItemUnit,
QI.QuoteItemUnitprice As UnitCost,
QI.QuoteItem_ItemTotalPrice As TotalPrice,
(QI.QuoteItemQuantity * QI.QuoteItemUnitPrice) AS TotalCost,      
(QI.QuoteItem_ItemTotalPrice * QI.QuoteItemQuantity) AS QtyItemTotalPrice,
(QI.QuoteItemWeight*QI.QuoteItemQuantity) AS TotalWeight,
QI.QuoteItemWeight AS ItemWeight,
QI.QuoteItemMarkupPercent AS MarkupPercent,
Q.QuoteFreightRate as FreightRate,
Q.QuoteNumberOfTrucks,
Q.QuoteSurcharge1,
Q.QuoteSurcharge2,
Q.QuoteSurcharge3,
((Q.QuoteFreightRate * Q.QuoteNumberOfTrucks) +(Q.QuoteSurcharge1+Q.QuoteSurcharge2+Q.QuoteSurcharge3)) AS TotalFreight

FROM TBL_Ebid_Project P
INNER JOIN  TBL_Quotes1 Q
ON P.ProjectID = Q.ProjectID
INNER JOIN  TBL_County C
ON C.CountyID = P.ProjectCountyID
INNER JOIN  TBL_QuoteItems QI
ON Q.QuoteID = QI.QuoteID
INNER JOIN  TBL_Division D
ON D.createdby = Q.QuoteCreatedby
INNER JOIN  tbl_customer cu
ON cu.customercreatedby = Q.QuoteCreatedby
           
WHERE p.projectstartdate >=  @StartDate  
      AND      p.projectstartdate <= @EndDate
      AND      D.DivisionName like   @DivisionName  
      AND  p.ProjectTxDot = + convert(varchar(5), @projectTxDot)
      AND CU.CustomerName like  @CustomerName  
      ORDER BY
            CU.CustomerName
 END  
0
 

Author Comment

by:sqlcurious
ID: 34938058
The above proc runs But When i execute the following statement it does not give me any results

exec DBSP_Report_LettingDate '01/01/2006','01/01/2011','1','','division description 2,division description 4
However it gives me the result when i pass only single value in @DIvisionName parameter

My problem is i want the result to be displayed both

Division description 2 and Division Description 4

When i pass two values in the DivisionName parameter

Can you modify the proc so that i can get the desired result
0
 
LVL 27

Expert Comment

by:planocz
ID: 34939648
are you talking about the division name or a different field of division description?
you do not have a division description listed in your SP.
0
 

Accepted Solution

by:
sqlcurious earned 0 total points
ID: 34939888
i am talking about 'division name' column
DivisionName is passed as a paramter.
You can see in the 'DivisionName' parameter several values has been passed.Instead of displaying only the passed values it is displaying all.

Another Problem in the above procedure.I got  following error

'Conversion failed when converting datetime from character string'
in the statement

0
 

Author Closing Comment

by:sqlcurious
ID: 34995563
i figured it out myself
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now