• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1023
  • Last Modified:

Subreports have no data in Crystal from ASP

Opening a crystal report 8.5 in asp page using ADO.
Report opens fine. sub reports do not show any data. The subreport data does show when I open the report in the crystal designer. I'm using the same exact SQL in the ASP that's in the report.

Please let me know if i'm doing something wrong when setting up and opening the subreports.
thanks.
Here's the code:
'This line creates an ADO Connection object
Set MyConnection = Server.CreateObject("ADODB.Connection")
Set MyRS = Server.CreateObject("ADODB.Recordset")
Set MyRS2 = Server.CreateObject("ADODB.Recordset")
Set MyRS3 = Server.CreateObject("ADODB.Recordset")
'MyConnection.ConnectionString = "Provider=SQLOLEDB;server=MYSERVER
;uid=USER;pwd=PWD;database=tempdb"
MyConnection.Open

sqlstr = "select cr_crs_id,cr_session,cr_fin_grd,cr_p_id,cr_credits,tr_degree,tr_major_1,tr_level,tr_stu_type,p_std_id,p_fname,p_mname,p_lname,crs_std_id,crs_dept,crs_number,crs_section,crs_pop,crs_max_pop,crs_long_title,crs_start,crs_end,term_start,term_end,term_desc from asl..class_listing,student..course,student..term" + _
" where cr_crs_id = crs_std_id" + _
" and crs_term = term_code" + _
" and cr_term = '07F'" + _
" and crs_dept = 'ACC'"
MyRS.Open sqlstr,MyConnection,3,1

Set oRptTable = session("oRpt").Database.Tables.Item(1)
oRptTable.SetPrivateData 3, MyRS

Set subReportOne = session("oRpt").OpenSubReport("classlist_meetings")
set subReportOneDB = subReportOne.Database
set subReportOneTab = subReportOneDB.Tables
set subReportOneTable = subReportOneTab.Item(1)
sqlstr = "select * from ((student..meetings left outer join student..bld_room on cmt_brm_id = brm_std_id) " + _
" left outer join student..day_times on cmt_dtm_id = dtm_std_id) where  cmt_crs_id in ( select crs_std_id from student..course where crs_term = '07F' and crs_dept = 'ACC')"
MyRS2.Open sqlstr,MyConnection,3,1
subReportOneTable.SetPrivateData 3, MyRS2
'Session("oRpt").SubReportController.setDataSource "classlist_meetings", MyRS2, Session("oRpt").Database.Tables.Item(1).Alias, "ADORecordset"

Set subReportTwo = session("oRpt").OpenSubReport("classlist_instructors")
Set subReportTwoTable = subReportTwo.Database.Tables.Item(1)
sqlstr = "select * from student..crs_instructor, common..people where cin_ins_id = p_std_id" + _
" and cin_crs_id in ( select crs_std_id from student..course where crs_term = '07F' and crs_dept = 'ACC')"
MyRS3.Open sqlstr,MyConnection,3,1
subReportTwoTable.SetPrivateData 3, MyRS3
%>
<!-- #include file="MoreRequiredSteps.asp" -->

<!-- #include file="SmartVieweractivex.asp" -->                    

0
MariaHalt
Asked:
MariaHalt
  • 9
  • 7
1 Solution
 
mlmccCommented:
How is the subreport linked to the main report?

What happens if you move to page 2 of the report?
I have a report that doesn't show data on the first page subreport until i move to page 2 then it shows data on all subreports.  I fixed the issue but I don't remember how right now.

mlmcc
0
 
MariaHaltAuthor Commented:
I have the subreport linked to the main report on course ID numbers. All pages have no data for either subreport. just to reiterate, the report runs fine from the Crystal software. Am i missing code in ASP somewhere to help the main report link with the subreports?
0
 
mlmccCommented:
Are you running against the same data in the designer?

mlmcc
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
MariaHaltAuthor Commented:
Yes.  The subreport data is displayed there.
0
 
mlmccCommented:
Is the above cut and pasted or did you retype it.  It appears there is a double .. in the SQL for the subreports

mlmcc
0
 
MariaHaltAuthor Commented:
Copy and past with some touch ups.  I just copied and pasted both subreports queries into SQL Server query window, they run fine.  I don't see any double...where do you?
0
 
mlmccCommented:
You are right.  It is my monitor or eyesight. Some of the _ look like ..

Can you look at the datasets in the application?  Are they getting the right data?

mlmcc
0
 
MariaHaltAuthor Commented:
Yes.  Did a Response.Write for both subreport queries and it spit back data.
0
 
mlmccCommented:
Is there data that is related to the main report?

It seems that maybe you are getting data but it doesn't include any data that is related.

mlmcc
0
 
MariaHaltAuthor Commented:
The data is visible in the main report when generated with the submitted code.  However, the embedded subreports display blank data.  (Sorry if I didn't make that clear in the original post).
0
 
mlmccCommented:
Don't know unless you somehow have the tables joined wrong or differently between the SQL.  Check the where clauses to ensure they are correct.

mlmcc
0
 
MariaHaltAuthor Commented:
I found the answer to my problem. It seems the subreport ado recordset must have a different name than the main report recordset. I got it to work in Crystal XI. I could not get it to work in 8.5.
Here's what I did:

went into design of the subreport, selected 'Database Expert', clicked on the 'ado' recordset in the right-side box, clicked again to edit the name, changed it to something unique (adosr1).
I had two subreports so I did it to each, naming the second one adosr2.

That's it.

Thanks for your help.
0
 
MariaHaltAuthor Commented:
mlmcc,

I'm giving you the points because you've helped me out in the past and i hope to keep receiving solutions from you in the future.

maria
0
 
MariaHaltAuthor Commented:
mlmcc,
I'm giving you the points because you've helped me out alot in the past and hope to keep receiving solutions from you in the future.  Thanks, Maria
0
 
MariaHaltAuthor Commented:
OK.
0
 
mlmccCommented:
I appreciate the attempt but as the Zone Advisor I have to be certain the rules are follwed especially when I am benefiting.

mlmcc
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now