ASP support for Reports based on SQL Queries

[Sarada]

I am working in crystal reports.I am facing a problem in giving the Web Interface using the Crystal Reports.

I am writing my query to you:

I have a prototype of a Table

the Fields are

Message Id Auto Number(Primary Key)

Connection Id Number

Device Id Number

Time Stamp Date/Time

Milliseconds Number

Function Number

Non_SECS_Messages Char(10)

Tool_To_from_Product Byte


There are 20 such MEssage Tables.(say tab1...........tab20)

I have to Query for all the Messages from all the Message Tables based on the Connection Id.

Say for ex:

select all the Message from tab1 to tab20 where connection Id =10;

Using "Union" operator I can retrive the data from all the Message Tables.

I have given my SQL expression as

************************************************************************************

select * from tab1 where connection id = 10

union

.........

...............

union

select * from tab2o where connetion id =10

************************************************************************************

here I made the Connection Id field as Parameter field.

And I have created a query using this expression in "Crystal Reports SQL designer."

And using the Report Designer I have created a Report using this Query.

It is giving me the data properly.Now I have to make it Web enabled.

For this I am creating one ADODB connection to the database in which the Query is there and

trying to open a Recordset.

There is one ADORecordset example in samples\ASPSamples\ADORecordset.cpp

but there I have to give the whole query as i am going to retrive data from 20 tables.

(and it is becoming tedious)

Is there any other way in which i can make my report web enabled.



And in the browser it is giving an error ""An Error has occured on the server attempting to access the data source"

I need some suggestions from you regarding this problem.

If my query is not clear I will get back to you again.

Please go through this problem and provide your suggestions.

 

[Guest_imported]

You can avoid giving the whole SQL statement to the database if you use SP/RCP (stored procedure) in stead. It will probably give you faster code too.

If you go for the SP sollution, try to do as much work as possible on the server; only return the data you really need for your application.

You do not state what kind of database (DB) you use; some definitions may be different (i.e. some have Number for whole figures and some use Integer).

Another common problem which I have been forced to do a workaround several times is date (one of the negative things with not having US date/time).

 

[AEldridge]

If you are trying to publish an already working Crystal Report via the web, you should use Crystal Enterprise (bundled with CR8.5) or the Web Component Server (bundled in CR7 and CR8).

It will save you from all the ADO hassles you are having.

Cheers, Alan Eldridge
Melbourne, Australia

 

[imstac]

I have a question regarding Provocado's suggestion to use a stored procedure. We have created a stored procedure in Sql for our supervisors to run to determine who has not filled out their timecards. The Crystal report that is using the stored procedure is being published to the web using ASP pages. Within this stored procedure we have a supervisor parameter and date parameter. The supervisor parameter is populated automatically into the crystal report when the report is ran. The data parameter is being selected by the supervisor on the web. The stored procedure works fine in Crystal but when I publish it to the web it won't show the data. We think it has to do with the '@' sign that you have to use to depict a parameter in the stored procedure. Has anyone dealt with this before? Any suggestions would be great. Thanks.