Error: One or more fields could not be found in the result set.

[huskies77]

Hello,

I have a crystal report (version 9.0) that i am trying to modify. I changed the source of stored procedure to my local database and now i cant get the report to work. When I verify database with null values then i can see reporting fields in the Database Fields under Fields explorer and when i try to verify using values in paramters, all the fileds are gone. Also when i run the report after verifing with null values, i get above error. Please HELP. This is driving me nuts. Here is my stored procedure...

Thanks in advance.

CREATE PROCEDURE Rp_DAP_04_ItemStatusWithVendor
@DescriptionLike varchar(100)='ALL',
@PriceLike varchar(20)='ALL',
@StatusLike varchar(20)='ALL',
@SortOrder varchar(40)='Description',
@SortMethod varchar(40)= 'Ascending'
AS
BEGIN
Declare @MTDStart VARCHAR(30), @YTDStart VARCHAR(30), @Today Varchar(30)
Declare @Filter varchar(500), @sqlstring Nvarchar(4000), @Connector varchar(20)

IF isnull(@SortMethod,'') = ''
BEGIN
SELECT TOP 1
0 AS VendorID,
'' AS VendorDesc,
'' AS ReportDate,
$0.00 AS Price,
'' AS Status,
0 AS QtyOnHand,
0 AS QtyBO,
'' AS PublishDate,
'' as OpDate,
null AS MTD_Net,
0 AS YTD_Net,
0 AS ATD_Net
END

ELSE
begin

set @SortMethod = (Select case @SortMethod
when 'ASCENDING' THEN 'ASC'
when 'DESCENDING' THEN 'DESC'
else 'ASC'
END)

set @SortOrder = (Select case @SortOrder
when 'Description' then 'vs1_vendor.VendorDesc'
when 'Price' then 'vs1_product.Price'
when 'Status' then 'vs1_product.UD2'
when 'Qty on Hand' then 'inventory.On_Hand'
when 'Qty on Back Order' then 'QtyBO'
when 'Publish Date' then 'vs1_product.UD7'
when 'Op Date' then 'vs1_product.UD8'
when 'MTD NET' then 'MTD.MTDNET'
when 'YTD NET' then 'YTD.YTDNET'
when 'ATD NET' then 'ATD.ATDNET'
else 'vs1_vendor.VendorDesc'
end)

set @Filter = ''
set @Connector = ''
IF not( Upper(@DescriptionLike) = 'ALL' or len(@DescriptionLike) = 0 )
BEGIN
set @Filter = @Filter + @Connector + 'vs1_vendor.VendorDesc like ''%' + @DescriptionLike + '%'''
set @Connector = ' AND '
END

IF not( Upper(@PriceLike) = 'ALL' or len(@PriceLike) = 0 )
BEGIN
set @Filter = @Filter + @Connector + 'vs1_product.Price = ' + @PriceLike
set @Connector = ' AND '
END

IF not( Upper(@StatusLike) = 'ALL' or len(@StatusLike) = 0 )
BEGIN
set @Filter = @Filter + @Connector + ' vs1_product.UD2 like ''%' + @StatusLike + '%'''
set @Connector = ' AND '
END

IF LEN( @Filter ) > 0
set @Filter = 'WHERE ' + @Filter


set @YTDStart = convert( varchar(4),year(getdate()) ) + '-01-01 00:00:00'
set @MTDStart = convert( varchar(4),year(getdate()) ) + '-' + convert( varchar(2),month(getdate()) ) + '-01 00:00:00'
set @Today = convert( varchar(4),year(getdate()) ) + '-' + convert( varchar(2),month(getdate()) ) + '-' + convert( varchar(2),day(getdate()) )

select orderdetail.item, sum( orderdetail.shipped_qty )as ATDNET
into #DAP_REPORT4ATD
from orderdetail
Where OrderDetail.Item_Status <> 'Return'
group by orderdetail.item

select orderdetail.item, sum( orderdetail.shipped_qty )as YTDNET
into #DAP_REPORT4YTD
from orderdetail
where orderdetail.item_shipdate
between @YTDStart and @Today
And OrderDetail.Item_Status <> 'Return'
group by orderdetail.item

select orderdetail.item, sum( orderdetail.shipped_qty )as MTDNET
into #DAP_REPORT4MTD
from orderdetail
where orderdetail.item_shipdate
between @MTDStart and @Today
And OrderDetail.Item_Status <> 'Return'
group by orderdetail.item
---
set @sqlstring = 'select
vs1_vendor.VendorID,
max( vs1_vendor.VendorDesc ) as VendorDesc,
as ReportDate,
max( vs1_product.Price ) as Price,
max( vs1_product.UD2 ) as Status,
sum( inventory.On_Hand ) as QtyOnHand,
SUM( case
when inventory.OnOrder > inventory.On_Hand then inventory.OnOrder - inventory.On_Hand
else 0
end ) as QtyBO,
max( vs1_product.UD7 ) as PublishDate,
max( vs1_product.UD8 ) as OpDate,
max( #DAP_REPORT4MTD.MTDNET ) as MTD_Net,
max( #DAP_REPORT4YTD.YTDNET ) as YTD_Net,
max( #DAP_REPORT4ATD.ATDNET ) as ATD_Net
from inventory
Inner Join Vs1_Product
On Inventory.productID = Vs1_Product.ProductID
Inner Join vs1_Vendor On Vs1_Product.vendorID = vs1_Vendor.vendorID
left join #DAP_REPORT4MTD on ( inventory.item = #DAP_REPORT4MTD.ITEM )
left join #DAP_REPORT4YTD on ( inventory.item = #DAP_REPORT4YTD.ITEM )
left join #DAP_REPORT4ATD on ( inventory.item = #DAP_REPORT4ATD.ITEM )'
+ @FILTER +
' group by vs1_vendor.VendorID
order by ' + @SortOrder + ' ' + @SortMethod

exec( @sqlstring )
END
END
GO

 

[vidru]

There's a typo on line 105 of your procedure... you're not specifying a field for ReportDate.

-dave

 

[huskies77]

Sorry, Typo is correct in my stored procedure. I dont know why it didnt get copied here properly. Anyways, like 105 should be '''+@Today+''' as ReportDate,

Thank you.

 

[vidru]

I don't see anything else that's obvious. The only difference between setting NULL parameters and 'real' parameters, is that with the 'real' parameters, all of the SELECT into temp tables happens. Sometimes, Crystal interprets the 'N rows affected' messagers as results.

To see if that's what's causing your problem, try placing a 'SET NOCOUNT ON' statement at the beginning of the procedure definition.

-dave

 

[huskies77]

Great, that solved the problem to a new problem. Now with null parameter, i am able to successfully varify database but with values passing in, i get an error "Failed to open a rowset, ADO Error Code: 0x80040e14 Login failed for user ..." I am able to loging with the same user and password into database and in query analyzer. With this username and password, i had problems with .Net application also and the only way i was able to login through .Net was to add Trusted-Connection=yes to the connect string. What coudl be wrong with the settings on this user? Any ideas?

Thanks.

 

[vidru]

There are a few whitepapers on the BizObj site that might help:

http://support.businessobjects.com/library/kbase/articles/c2011618.asp

http://support.businessobjects.com/library/kbase/articles/c2016674.asp

You should also make sure you're up to date with Service Packs/Hotfixes:
Service Packs
Hot Fixes

-dave