VB and Crystal report problem with stored procedure

[ndp]

Hi,
In my VB application, I create crystal report through CRPEAuto.Application object. I have Crystal report version 8.5 and VB6.
My problem is that it gets blank report. I am using the stored procedure. The crystal report passes two parameters. The crystal report works fine individually. But, seems like it has problem with the stored procedure. I have bunch of If statements in my stored procedure. If I remove those if statements, then it works fine in my application.
I was wondering why the same stored procedure works with crystal reports and not through VB? Is there any syntex problem? If somebody knows about it please let me know correct syntex.
My store procedure is like this...

CREATE Procedure dbo.Cycle_Packing_ListApp

@IsDistrict as bit,
@County_Shipment_Type as varchar(50)

AS

IF @IsDistrict = 1

SELECT * FROM orPackingList

ELSE
begin
IF @County_Shipment_Type = 'Cycle I'
SELECT statement....
ELSE IF @County_Shipment_Type = 'Cycle II'
SELECT statement....
ELSE IF @County_Shipment_Type = 'Cycle III'
SELECT statement....
end

Any suggestion is welcome,

Thanks,
Nita

 

[vidru]

Do all of your potential SELECT statements return the EXACT same columns? The procedure itself is fine, but if the columns returned are different in name/type/number, the report wouldn't know what to do.

Once you get that straightened out, post the code you're using to call the report, and we'll go from there.

-dave

 

[ndp]

Thanks Dave,
This is excellent suggestion. I am still checking the columns as you suggested. I found one field name different and changed it. But, the report still doesn't show up in my application.
I am still checking each field data type. I am sure it must be something as you mentioned above. There are lot of fields, so please hang in there.

Thanks again for your help.

-ndp

 

[synapsevampire]

A good way to avoid this is to NOT use select * in an SP, not only does it complicate maintenance and reusability, but you may suffer this sort of problem.

-k

 

[ndp]

Ok, I checked everything. Everything matches and still it doesn't work. I don't see any reason.
It still doesn't like else part of my stored procedure.
The code works fine if I remove else part from stored procedure. I am posting the whole stored procedure here if somebody can figure out the problem.

CREATE Procedure dbo.Cycle_Packing_ListApp

@IsDistrict as bit,
@County_Shipment_Type as varchar(50)

AS

IF @IsDistrict = 1

SELECT * FROM orPackingList --This is a view

ELSE


IF @County_Shipment_Type = 'Cycle I'
SELECT DISTINCT '' AS FormSortOrder,
Counties.County_Name, Counties.County_Key, 0 AS Dis_Key, '' AS Dis_Name, 0 AS Dis_Num,
'' AS SchoolName, 0 as School_Key, 0 as School_Num, Counties.CDS,
County_Key AS Order_Key, 'Score Reports' AS OrderType, Products.[Description], 1 AS Quantity,
Products.Product_Key, Products.Display_Order, 1 as address_location,
Counties.CSuperTitle AS Title, Counties.CSuperFName AS FName, Counties.CSuperLName AS LName,
Counties.CSuperBulkBuild AS Build,
Counties.CSuperBulkStreet AS Street, Counties.CSuperBulkCity AS City, Counties.CSuperBulkZip AS Zip,
Counties.CSuperPhone AS Phone, Counties.CSuperExt AS Ext
FROM Products
CROSS JOIN Counties
WHERE Product_Key IN (1104, 1105, 1110, 1114)
AND County_Key NOT IN (90)


ELSE IF @County_Shipment_Type = 'Cycle II'

SELECT DISTINCT '' AS FormSortOrder,
Counties.County_Name, Counties.County_Key, 0 AS Dis_Key, '' AS Dis_Name, 0 AS Dis_Num,
'' AS SchoolName, 0 as School_Key, 0 as School_Num, Counties.CDS,
County_Key AS Order_Key, 'Score Reports' AS OrderType, Products.[Description], 1 AS Quantity,
Products.Product_Key, Products.Display_Order, 1 as address_location,
Counties.CSuperTitle AS Title, Counties.CSuperFName AS FName, Counties.CSuperLName AS LName,
Counties.CSuperBulkBuild AS Build,
Counties.CSuperBulkStreet AS Street, Counties.CSuperBulkCity AS City, Counties.CSuperBulkZip AS Zip,
Counties.CSuperPhone AS Phone, Counties.CSuperExt AS Ext
FROM Products
CROSS JOIN Counties
WHERE Product_Key IN (1210,1211,1212,1213,1214,1215,1216,1240)

AND County_Key NOT IN (43,45,47,90)

ELSE IF @County_Shipment_Type = 'Prep Materials'

SELECT DISTINCT '' AS FormSortOrder,
Counties.County_Name, Counties.County_Key, 0 AS Dis_Key, '' AS Dis_Name, 0 AS Dis_Num,
'' AS SchoolName, 0 as School_Key, 0 as School_Num, Counties.CDS,
County_Key AS Order_Key, 'Prep Materials' AS OrderType, Products.[Description], 2 AS Quantity,
Products.Product_Key, Products.Display_Order, 1 as address_location,
Counties.CTCTitle AS Title, Counties.CTCFName AS FName, Counties.CTCLName AS LName,
Counties.CTCBulkBuild AS Build,
Counties.CTCBulkStreet AS Street, Counties.CTCBulkCity AS City, Counties.CTCBulkZip AS Zip,
Counties.CTCPhone AS Phone, Counties.CTCExt AS Ext
FROM Products
CROSS JOIN Counties
WHERE Product_Key IN (1401, 1403)

ELSE IF @County_Shipment_Type = 'SRA'

SELECT DISTINCT '' AS FormSortOrder,
Counties.County_Name, Counties.County_Key, 0 AS Dis_Key, '' AS Dis_Name, 0 AS Dis_Num,
'' AS SchoolName, 0 as School_Key, 0 as School_Num, Counties.CDS,
Counties.County_Key AS Order_Key, 'SRA' AS OrderType, Products.[Description],
Case WHEN Product_Key = 1501 THEN Ceiling((Count(Distinct School_Key) + 1.0)/25.0)*25 ELSE Ceiling((Count(Distinct School_Key) + 1.0)/25.0) END AS Quantity,
Products.Product_Key, Products.Display_Order, 1 as address_location,
Counties.CSuperTitle AS Title, Counties.CSuperFName AS FName, Counties.CSuperLName AS LName,
Counties.CSuperBulkBuild AS Build,
Counties.CSuperBulkStreet AS Street, Counties.CSuperBulkCity AS City, Counties.CSuperBulkZip AS Zip,
Counties.CSuperPhone AS Phone, Counties.CSuperExt AS Ext
FROM Products
CROSS JOIN Counties
INNER JOIN Districts
ON Districts.County_Key = Counties.County_Key
INNER JOIN dbo.Schools
ON Schools.Dist_Key = Districts.Dis_Key
WHERE Product_Key IN (1501,1503)
AND Schools.inactive = 0 and districts.inactive = 0
AND Counties.County_Key NOT IN (43,45,47,90)
GROUP BY Counties.County_Name, Counties.County_Key, Counties.CDS, Counties.County_Key,

Products.Description, Products.Product_Key, Products.Display_Order,
Counties.CSUPERTitle, Counties.CSUPERFName, Counties.CSUPERLName,
Counties.CSUPERBulkBuild, Counties.CSUPERBulkStreet, Counties.CSUPERBulkCity,
Counties.CSUPERBulkZip, Counties.CSUPERPhone, Counties.CSUPERExt

ORDER BY Counties.County_Key

The wiew orPackingList is...
CREATE VIEW dbo.orPackingList AS

SELECT DISTINCT Case OrderType WHEN 'Standalone' THEN FormSortOrder_FT ELSE FormSortOrder END AS FormSortOrder,
Counties.County_Name, Counties.County_Key, Districts.Dis_Key, Districts.Dis_Name, Districts.Dis_Num,
CASE Schools.School_Name WHEN 'DistrictOverage' THEN 'A' ELSE Schools.School_Name END AS SchoolName, Schools.School_Key, Schools.School_Num, Schools.CDS,
Orders.Order_Key, Orders.OrderType, Products.[Description], Order_Details.Quantity, Products.Product_Key, Products.Display_Order, Orders.address_location,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSATitle WHEN 2 THEN TCTitle WHEN 3 THEN Extra1_Title END As Title,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSAFName WHEN 2 THEN TCFName WHEN 3 THEN Extra1_FName END As FName,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSALName WHEN 2 THEN TCLName WHEN 3 THEN Extra1_LName END As LName,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSABulkBuild WHEN 2 THEN TCBulkBuild WHEN 3 THEN Extra1_Build END As Build,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSABulkStreet WHEN 2 THEN TCBulkStreet WHEN 3 THEN Extra1_Street END As Street,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSABulkCity WHEN 2 THEN TCBulkCity WHEN 3 THEN Extra1_City END As City,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSABulkZip WHEN 2 THEN TCBulkZip WHEN 3 THEN Extra1_Zip END As Zip,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSAPhone WHEN 2 THEN TCPhone WHEN 3 THEN Extra1_Phone END As Phone,
CASE Orders.Address_Location WHEN 1 THEN Districts.CSAExt WHEN 2 THEN TCExt WHEN 3 THEN Extra1_Ext END As Ext
FROM Order_Details
INNER JOIN Orders ON Order_Details.Order_Key = Orders.Order_Key
INNER JOIN nCounts ON Orders.nCount_Key = nCounts.nCount_Key
INNER JOIN Products ON Order_Details.Product_Key = Products.Product_Key
INNER JOIN Schools ON nCounts.School_Key = Schools.School_Key
INNER JOIN Districts ON Schools.Dist_Key = Districts.Dis_Key
INNER JOIN Counties ON Counties.County_Key = Districts.County_Key
WHERE Order_Details.Quantity > 0 AND IsFilled = 0 AND Orders.IsCancelled = 0

I will really appreciate if somebody can figure out the problem before I give up on this.

Thanks,
ndp

 

[vidru]

I'll take your word for the columns matching up (I just don't have the time to check them all).

Try putting in an ELSE after your ELSE IF's that will return the same columns (as a default), just in case. Currently, if @IsDistrict = 0 and the @County_Shipment_Type parameter is anything other than Cycle I, Cycle II, Prep Materials, or SRA, the procedure won't return anything at all.

Not that I condone using SELECT *, but your ELSE could be something like:
SELECT *
FROM orPackingList
WHERE <a condition that will always evaluate to false>

That would at least return the column headers.

-dave

 

[ndp]

Thanks Dave,
I really appreciate your effort.
You are right about putting another else at the end for @County_Shipment_Type. I did that.

About the second part of your suggestion,
orPackingList is a view and it uses different tables than my ELSE statements. The view uses District table where as in my ELSE statements, I use County table instead. So, I can not use the orPackinglist for ELSE statements. I hope I understood your point properly.

-ndp

 

[ndp]

Hi,

Finally, I made it work. It is not perfect solution, but kind of workaround. So, I am not really excited. I removed my else statements and used union statements and put parameters in CR. Based on the parameter, CR would select only those records.
Still the my question is open. Why it wouldn't work with else statements?

Thanks very much for the suggestions.
-ndp

 

[aljodo]

Is it not the case that CR only reads the first SELECT statement in a SP and ignores the rest?!

Alan.