Crystal Report multi table failure

Question

I'm still new to C# and reports, and in order to take baby steps, I started with a Crystal Report using one table.

Eventually I figured it out and it worked brilliantly.

Then I added another table to the report. I haven't changed anything in my code. Adding a field from the second table to the report, results in a blank report.

Removing that field again (so no columns form the second table is on the report), the report produces data again.

So I get the impression that the problem is on the report side. But I have included the code anyway:

private void Load_Ord_Rep()
{
    using (MySqlConnection conn = new MySqlConnection(OTW.Properties.Settings.Default.wcdbConnectionString))
    {
        conn.Open();

        String sql = "SELECT * FROM wcdb.order_table, wcdb.mat_table WHERE order_no = '13661' and order_table.mat_code = mat_table.mat_code";

        using (MySqlCommand cmdSel = new MySqlCommand(sql, conn))
        {

            DataSet ds = new DataSet();
            MySqlDataAdapter da = new MySqlDataAdapter(cmdSel);

            da.Fill(ds);

            ReportDocument rpt = new ReportDocument();
            rpt.Load("C:\\Visual Studio 2008\\Projects\\OTW\\OTW\\CrystalReport3.rpt");

            dataView1.Table = ds.Tables[0];
            rpt.SetDataSource(dataView1);


            crystalReportViewer1.ReportSource = rpt;
            crystalReportViewer1.Refresh();
        }
        conn.Close();
    } 
}

With further investigation I have come to the conclusion the problem is not the code or the link, but rather the loading of the second table. I did a outer join with the values being equal or greater. Only the first table's results are displayed on the report. So because the second table's values are not read, no join can be established between the two tables and thus no data on the report. Now the question: why is the second table not being read by Crystal Report!?

UPDATE

I removed the second table from the main report and added a sub-report with the data. Same result as before. The sup report shows blank. Running the sup report on its own (as the main report), it populated correctly. I'm using MySQL, could it then maybe be a database issue?

UPDATE

I created a new app, this time connected the report to the database using ODBC (instead of ADO.NET). And it worked perrrfect. Now to figure out why ADO.Net is not working....as my entire program is based on it.

Answer 1

Check the link between the two tables in crystal reports. Are they as what you expect ?

Check it by

Field Explorer> DataBaseFields > (RightClick) DatabaseExpert > (See Tab) Links

Answer 2

You don't need to do any of the code you are doing with the dataset/dataview. I recommend you allow the report to "pull" data (you are "pushing" data to the report now).

To pull data requires only the following:

ReportDocument rpt = new ReportDocument(); 
rpt.Load("C:\\Visual Studio 2008\\Projects\\OTW\\OTW\\CrystalReport3.rpt"); 

rpt.SetDataBaseLogon("userName", "password", "servername", "database"); 

crystalReportViewer1.ReportSource = rpt; 

This reduces the risk that you're confusing the report up by passing in a dataview that it has no idea how to map to the tables you added while designing the report. (Which I'm 99.999% sure is happening now.)

All the code above lacks is parameters if you had any that needed to be set. It looks like you may be trying to "push" data because you wanted to filter on that order number or something? If that is the case, add a 'record selection formula' inside your report, base it's 'where' aspect of that formula on a Crystal Report parameter and add a rpt.SetParameter(arguments) line to the code I provided.

No this is not a licensing constraint, I'm certain of that.

Successful troubleshooting path:

1) Used the simple code to load the report: * No errors, but no data in the report

2) Played with join options in the report: * Also resulted in a blank report, even with only one table * Concerned the second table is not reading data

3) Checked for record filtering by a 'record selection formula' which might limit/prevent rows from being returned * In this case, wasn't applicable

4) Analyzed how joins were being done: ["It's probably how you are joining. For example: if you do a right outer join to a second table and it has no data, then even the first table's rows won't come back."]

• A join was done on the primary id field (mat_code) in the 'orders' table to the same field (mat_code) in the second table, 'material'. This seemed fine.
• Attempted a Left Outer join with the link type being ">=". The report printed the first table (orders) with data. But still none of the second tables' (material) data.
• Made a new report, this time first adding the "second" table (material) and then the first (orders). This time with an outer join, and only the material table shows data. In other words, it only appears to pull data for the first, or primary table, then stops.

5) Attempted loading the second table's data by putting it into a subreport, and linking the subreport on the same 'mat_code' field:

(Helpful tutorial: http://vb.net-informations.com/crystal-report/vb.net_crystal_report_subreport.htm)

• Sub-report also shows blank. Only the main report works. Whatever table exists in the subreport is not being populated.
• Still sees no data using the simple suggested code, his original code still shows the first table's data

6) Check for a mismatch between the table's connection and definition, and the actual data structure/contents being submitted to, or loaded by, the report. A specific test was suggested, go to: i) 'Set Database Location' (where you manage tables for the report) ii) Look to see if an XML/DataSet was used versus OLEDB was done iii) Change the database table location to the same tables, but with an OLEDB connection type (repeat for all tables)

• When attempting above, when the 'Update' button was hit, the screen just flickered for a split second, but showed no message. After testing the report again, still no change in behavior. He was using ADO.NET to connect to the data.

7) Still highly suspicious of the data table definition and connection type. Suggested the following test: i) Make a brand new 1 page application with only simple test code. ii) Make a new report with only the 'orders' and 'materials' tables, using OLEDB from the beginning iii) Add only the mat_code field from the main table to the report iv) Add a subreport for 'materials' linked on mat_code v) Show only mat_code on the subreport vi) Run the app

If data shows, the problem is either:

Answer 1: A mismatch between the database definition (as was read when the report was FIRST made, and connected via ADO.NET to the tables) and the actual data table/column definitions being found when loading the report later (i.e. Someone edited the 'materials' table to change a column definition, or the number of fields, etc.)

Answer 2: Possible defect in the particular combination of Crystal Reports and Windows drivers required to push ADO.NET data to the report. Using the pull-data model over OLEDB may be working around something issues. Those issues may be addressed with the latest Crystal Reports and/or Windows drivers (i.e. hotfixes or Windows updates, driver packs, etc.)

• The test worked fine. Even without making a subreport. The data pulled in just like it was supposed to.