code prior

[JKingdom]

According to what crystal report manual says :
///
'We do not support multiple recordsets in a single report. If this is required one should
'use subreports or combine the multiple recordsets in code prior to passing them to the report
'as a datasource.
///

i have a report of 2 tables . its working fine directly but not through the ASP page .. how u can do one of this step:

combine the multiple recordsets in code prior to passing them to the report

 

[JKingdom]

i want to use 2 tables .. dept and emps .. want to get records from these 2 tables where the dept.dept_id = emps.dept_ID

this is my code ...

\\\\\\

Set oADOConnection = Server.CreateObject("ADODB.Connection")

oADOConnection.Open ("directory")

Set oADORecordset = Server.CreateObject("ADODB.Recordset")
Set oADORecordset = oADOConnection.Execute("Select * From dept")

Set oRptTable = session("oRpt").Database.Tables.Item(1)

oRptTable.SetPrivateData 3, oADORecordset,oADORecordset2

 

[JKingdom]

sorry the last line in the code is this:
oRptTable.SetPrivateData 3, oADORecordset
not this:
oRptTable.SetPrivateData 3, oADORecordset,oADORecordset2

 

[DotNetGnat]

since you can do a join on the two tables using dept.dept_id = emps.dept_ID

i would suggest you to use a single query to get all the records...

can you post some sample data of both the tables and the kind of the results you are looking for?

-DNG

 

[Sheco]

Does the documentation include anything that leads you to believe that the SetPrivateData method will accept 2 recordset objects as input parameters?

As DNG says why not just make one recordset with data from both tables like:
[tt]
SELECT dept.*, emps.*
FROM dept
INNER JOIN emps
ON dept.dept_id = emps.dept_id
[/tt]



As a side note, generally speaking it is not good to store objects in session variables:
[tt]Set oRptTable = session("oRpt").Database.Tables.Item(1)[/tt]

 

[JKingdom]

but i keep having this usual error if teh report using 2 tables ..

Error Occurred Reading Records: The table '' could not be found..

maybe doing this helps(combine the multiple recordsets in code prior to passing them to the report
as a datasource) according to what mentioned in the documentation .. .

 

[DotNetGnat]

thats what we are doing when we are joining the tables and making it one recordset instead of two recordsets...

post some sample data from your tables and the kind of output you are looking for...

we will come up with a query...

-DNG

 

[JKingdom]

IT dept >>>>> john, Smith, Tom
HR dept >>>> kaz, Maria, Edward
Public relations dept >>> johnson, Mark , Sue

 

[DotNetGnat]

not enough information provided for your data and the kind of results you are looking for...but the basic structure of your query will be something like this:

SELECT ITDept.*, HRDept.*,PRDept.*
FROM ITDept INNER JOIN HRDept
ON ITDept.EmpId=HRDept.EmpId
INNER JOIN PRDept ON
ITDept.EmpId=HRDept.EmpId

or simply...

SELECT ITDept.*, HRDept.*,PRDept.*
FROM ITDept, HRDept, PRDept 
WHERE ITDept.EmpId=HRDept.EmpId
AND ITDept.EmpId=HRDept.EmpId

-DNG

 

[davidwendelken]

SELECT dept.*, emps.*
FROM dept
INNER JOIN emps
ON dept.dept_id = emps.dept_id

This type of query will bring back two dept_id columns, one from the dept table and one from the emps table.
Not sure that is what you want.

Second, some questions:

1) If a department has no employees assigned, do you want to see it in the result set?

2) If an employee is not assigned to a department, do you want to see them in the result set?

3) Do you want 1 record returned per employee/department combination, or one record per department with a long string of relevant employee names at the end.

I ask this last question because when asked to supply sample data and desired output, you replied (somewhat ambiguously) as follows:

IT dept >>>>> john, Smith, Tom
HR dept >>>> kaz, Maria, Edward
Public relations dept >>> johnson, Mark , Sue

Let us know!


David Wendelken

http://www.casetech.net