Hello All,

Can anyone please help in resolving crystal reports.Net and SQL Server stored procedure problem I am facing these days.

See here what I am having.

- We are using SQL Server 2000 stored procedures for our MIS type reports. Procedures have too complicated structure. I have given only a simple of procedure in which I face problem.

- After that, I design crystal report linked with a stored procedure using Crystal Reports 10 editor. It runs very well and shows proper data in preview mode of crystal reports editor.

- Now I called this report in my ASP.Net crystal report viewer. Also, I have set logon info and other tables related functionalities required in .vb file.

Problem:

I m just giving sample of procedure, actually my original procedures are too complicated. But I m sure if this sample works than I will make everyone working.


If I write following procedure, it works fine on both the sides (crystal report editor and asp.Net view). So, there is no problem at all.


CREATE PROCEDURE [dbo].[temp]
(@case_type_id varchar(5))
AS
select * from ms_case_type where case_type_id > @case_type_id
GO


But now, if I write the same procedure using exec(‘ ‘ + ‘ ‘) method, it works on crystal report editor preview mode side but gives an error in asp.net side.

CREATE PROCEDURE [dbo].[temp1]
(@case_type_id varchar(5))
AS
exec('select * from ms_case_type where case_type_id > ' + @case_type_id )
GO

See, it is strongly required that I use second type of procedure. So, don’t tell me that you can’t use this way. Also, for you information, I have used 50 such procedures in our ASP project, it works well. Also it works very well if I attached it with data adaptor, it can fetch data and show using dataset or datareader in ASP.Net. So, there is nothing wrong on database side or procedure side.


The error I get is

-------------------

Exception of type System.Exception was thrown.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Exception of type System.Exception was thrown.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[Exception: Exception of type System.Exception was thrown.]

CrystalDecisions.Web.ReportAgent.u(Boolean

--------------------------------------------------------------------------------
N) +358

CrystalDecisions.Web.CrystalReportViewer.OnP reRender(EventArgs e) +108
System.Web.UI.Control.PreRenderRecursiveInte rnal() +62
System.Web.UI.Control.PreRenderRecursiveInte rnal() +125
System.Web.UI.Control.PreRenderRecursiveInte rnal() +125
System.Web.UI.Page.ProcessRequestMain() +1489

-------------------

Plz help me out if u have any solution or clue to the solution.

Thanx in advance

Jainesh

I have the same problem and a solution for you.

1) Uninstall Crystal Reports (completely remove it from .Net and your hard drive)
2) Install Open Office
3) Recreate all reports using Open Office
4) Use the export to PDF feature of Open Office to create the PDF file

Found another solution:
Reporting Services a new product from Microsoft which is also better than Crystal Reports and would at least return a decient error.

Let me explain,, the error you recieved isn't even really an error. All it is saying is that something happened somewhere.

Crystal Reports didn't use error checking.
If you want to send 2 tables of data you have to use 2 datasets.
A dataset is a group of tables. Therefor the crystal reports team has no farfing idea what the heck a dataset is.

So you are screwed my friend.

Maybe perhaps you can find a solution, perhaps you used a datatable that had a deleted row (.net doesn't really delete the row then CR tries to access the deleted row and then BOOM! CR doesn't know what happened but something did)

CR doesn't even know if the report was generated, their object don't return success values, they never though of such features.

Perhaps you data had a null value, you see CR accepts Objects as input. .Net defines everything as Objects. So you could pass an empty StringBuilder object as the datasource of your report.

CR requires a typed dataset to create an RPT file but when it comes to setting the datasource you could pass it anything you please.

That's how much care and error checking CR put into it's products.

So when I say you need to stop using CR I'm not joking.

You wouldn't take a VW Beetle on Germany's autobahn wait for the engine to explode and then wonder "What happened?". You would know I took a piece of sh!t on a major transit center and it broke down because it wasn't built to do that.

CR was built to sell. It worked people bought it. (It didn't work too well though because they give it out for free also). It wasn't built to generate reports,, not really it was built as a proof of concept they make a few reports and then people buy the software. That's it. Don't expect anything more.