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

I GOT THIS ERROR WHILE DEVELOPING REPORTS IN SSRS 2008R2
sqlcuriousAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sqlcuriousConnect With a Mentor Author Commented:
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
 
planoczCommented:
Sorry, You have to gives us more info than that.
What key are you talking about?
0
 
sqlcuriousAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
planoczCommented:
Can you post your query so we can see if there is a problem in the way it is written.
0
 
sqlcuriousAuthor Commented:

 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
 
planoczCommented:
I mat not have this just right, but go ahead and run it
0
 
planoczCommented:


-- 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
 
sqlcuriousAuthor Commented:
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
 
planoczCommented:
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
 
sqlcuriousAuthor Commented:
i figured it out myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.