CODEWITHSUNDEEP
c#sql.netsql-servercrystal-reports
Win Coder
Win Coder

Reputation: 6756

SQL query acessing multiple tables

It is working fine if just display the results from a single table.

SqlCeCommand command = new SqlCeCommand("SELECT Student.EnrolNo,Student.St_Name FROM Student", ceConnection);

SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
DataSet set = new DataSet();
adapter.Fill(set, "Student");

ReportDocument document = new ReportDocument();
document.Load(@"C:\Users\user\documents\visual studio 2012\Projects\Student_Scholarship_management2\Student_Scholarship_management2\MainReport.rpt");
document.SetDataSource(set.Tables[0]);

crystalReportViewer1.ReportSource = document;

Now the above code is working fine as it just references a single table however if i use the query below which accesses record from multiple tables

SqlCeCommand command = new SqlCeCommand("SELECT Student.EnrolNo,Student.St_Name,Campus.C_ID FROM Student INNER JOIN Campus ON Student.Campus_ID=Campus.C_ID", ceConnection);

Only those columns get displayed which are from the Student Table. I have tried importing and linking more tables in through the Database Expert but it is of no use. I have verified the query and the resutls are fine and coming through just not getting displayed in crystal reports.

I have added the table in database expert and have added the relevant column in the report designer. Unfortunately its only displaying the data from one table. What could be causing this?

Upvotes: 1

Views: 535

Answers (2)

Fooksie
Fooksie

Reputation: 480

I would have a look at your set command:

document.SetDataSource(set.Tables[0]);

To me that seems to be setting only the first table in your DataSet.

I have a similar situation in the app I'm working on and this is my code (which is passing in about 10 tables):

var data = new DataSet();

//Do stuff to populate the dataset

report.SetDataSource(data);

The final line sets the datasource of your report to be all the tables in the DataSet.

Upvotes: 1

Marian Siminescu
Marian Siminescu

Reputation: 27

When you call:

adapter.Fill(set, "Student");

In that moment, the table schema in dataset is built. So, if you fill below with other columns, only the columns from the first fill will be considered. A solution for you would be to replace:

SqlCeCommand command = new SqlCeCommand("SELECT Student.EnrolNo,Student.St_Name FROM Student", ceConnection);

with

SqlCeCommand command = new SqlCeCommand("SELECT Student.EnrolNo,Student.St_Name, null as C_ID FROM Student", ceConnection);

Upvotes: 0

Related Questions