I cannot report with my stored procedure 1

[AaronA]

Hello,
I am new to Crystal Reports and so I've been learning as much as I can about all the methods and procedures that come along with Crystal Reports. However, I am trying to move from embeded SQL to Stored Procedures and this is how I go about doing that. I have a recordset that I set Equal to my function that returns a recordset of data. I then try to set a Reports.getDataSource method to that recordset. It gives me an error that says "Unbale to access first Record."
and then says "SQL Server Error". I know this stored procedure works because I can step through the recordset and print the data in the debug window, but how do i go about reporting this data? Thank you for your time.

Aaron A. [sig][/sig]

 

[kenhamady]

Are you sure that the recordset is populated before you send it to the report?

Are you using the Active X, the RDC or the Automation Server? [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[AaronA]

Yes it is populated, I would run my function that returns the recordset, and I have used SQL Server Analyzer to check the results, they are the same. I am using the CRViewer Active-X Control. Also now that you mention if it was populated... I would check to see if the recordset was BOF or EOF and it would give an error saying that it couldn't tell me that or something. [sig][/sig]

 

[kenhamady]

Two things I will suggest.

First, I use the following line of code to assign a recordset to a report:

rptMyReport.SetTablePrivateData 0, 3, Adodc1.Recordset

Second, the report has to be designed so that it runs from Active Data. This means using the Active Data Driver to create it. If you have an existing report you can convert it to the active data driver, but only if it uses only one table. Otherwise you have to rebuild it from scratch using the active data driver. [sig]<p>Ken Hamady<br><a href=mailto:ken@kenhamady.com>ken@kenhamady.com</a><br><a href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[AaronA]

Ken,

Thanks for helping me on this problem. I dont think I have that method available to me. I did a search in the objectviewer for &quot;SetTablePrivateData&quot; and couldn't find it. I am using v8 active-x control, that might be why. Also I am not familiar with Active Data Driver Is this for Stored Procedures? Thanks for your time.

Aaron A [sig][/sig]

 

[kenhamady]

I am also using the V8 active-X, but I think I missed your question. I will try again.

I thought you were trying to pass a recordset to the report. That would require both an Active Data Driver report, and Active Data in the application(Like ADO, RDO, etc). I suppose you could connect to the stored procedure using ADO and then pass the recordset, but I don't think that is what you are looking to do.

I think what you want to do is have the report use the stored procedure as the source of its data. This can be done but it will bypass any recordsets in memory in the application.

This has to be changed in the report. You can design a report using a stored procedure just like it was a table. You will have to go to &quot;File-Options&quot; in the report designer and on the &quot;Database&quot; tab check &quot;allow reporting on Stored Procedures&quot;.

If I missed the question again, let me know. [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[AaronA]

Ken,

Acctually that was what I was trying to do, using ADO I give a recordset to the Report.Database.SetDataSource method and it does a report. However, When I try to do it with a recordset derived from a stored procedure, it gives me that error. Thanks for your time.

Aaron [sig][/sig]

 

[kenhamady]

Are you sure that you are using the Active-X and not the RDC? I found the SetDataSource method in the RDC, but not in the OCX.

Below is the line of code that I use for RDC:

Report.Database.SetDataSource data, 3, 1

'data' is a recordset in memory.

Now if you are using RDC:

Are you saying that this whole thing works fine if the recordset comes from a table, and then fails when you switch the recordset to the stored procedure? Specifically you aren't changing the line that uses the SetDataSource at all, just the code behind it that generates the recordset and stores it into a memory variable?

If that is the case I would check the stored procedure and make sure that it is providing the exact same fields, in the same order, with the same names and data types.

My suspicion however is that you might be getting the REPORT to read a table, and then trying to get it to read a recordset. This won't work. The report has to be designed to read a recordset by being created from the Active Data Driver in the first place. Only then will it accept a recordset from memory.

Hope we are getting closer. [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[AaronA]

They are always recordsets, the first is an embedded SQL statement and the new one will be a stored procedure. So I think I am using the active data driver. But I when I create the report, I am using a *.ttx file (DDL file) to create the columns in the report.

Also in the function call: Report.Database.SetDataSource data, 3, 1 , what do the 3 and 1 stand for? are they constants or something? I am going to add those to the function call and see if that works. As for RDC and OCX, I thought I was using OCX, all I am doing is adding a CR Designer to the Project, Setting it up and using another form I have with the CRViewer active-x control. By the way, what does RDC stand for? Thanks for the help. [sig][/sig]

 

[kenhamady]

Aaron,

Yes, you are using the &quot;Report Design Component&quot; which is an Active-X Designer coupled with an Active-X viewer page. This is a recent introduction by Seagate.

There is an older simper technique which just uses one invisible Active-X control on the report. It uses completely different syntax and is referred to as the Active-X or OCX technique. Actually there are 4 different ways of launching CR from VB:
API
Active-X
Automation Server
RDC

Since you created the report using a ttx file , your report should be ready for an Active Data recordset.

the 3 tells the report to expect active data (the only value), the 1 is the table number, which has to be 1 with a recordset. The 1 might be optional, but I was pretty sure that the 3 was required. This however doesn't make sense if you don't have the 3 and your SQL recordset passes fine. [sig]<p>Ken Hamady-

http://www.kenhamady.com<br><a

href=

http://www.kenhamady.com/>Crystal

Reports Training by Ken Hamady</a><br>[/sig]

 

[AaronA]

Ken,

Yeah Acctually the '3' seems to be a optioanl variable, I've seen it used in a book before, but since I didn't know what it meant, I never Used it. So I am using RDC, that's good to know becuase Seagate Always asks me what I am using when I use their tech support. I am guessing that the &quot;SetTablePrivateData&quot; function you are talking about is in the Invisable active-x control? One more thing I'd like to add. the stored procedure generates a recordset from multiple tables, would that cause a problem?

Thanks for your time Ken. [sig][/sig]