Crystal Reports won't display related table data

Question

I'm new to Crystal Reports and trying to maintain someone else's code -- so bear with me. I have a crystal report that is tied to a DataSet with 2 related DataTables.

I'm grouping by JurorProfile.JurorPK. In my Group Header, I display information from the JurorProfile DataTable.

In the report detail, I have RowNumber, JurorQuestionAnswers.Question and JurorQuestionAnswers.Answer.

In my c# code, I'm populating the data like this:


public void SetDataSource(AdoProvider provider, int sessionPK, int jurorPK)
{
   string commandText;
   AdoQuery query;

   commandText = $@"select j.* from Juror j
   where j.JurorPK in (select JurorFK from SessionJurorLink where SessionFK = @sessionId)
   {(jurorPK > 0 ? " and jurorPK = @jurorId" ? "")}
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader jReader = query.ExecuteReader(sessionPK, jurorPK);
   report.Database.Tables["JurorProfile"]
     .SetDataSource((IDataReader)jReader.DataReader);

   commandText = $@"select ... from JurorAnswers ...
   where sessionFK = @sessionId
   {(jurorPK > 0 ? " and jurorFK = @jurorId " : "")}
   order by JurorFK, Rank, ParentFK, QuestionPK
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader qaReader = query.ExecuteReader(sessionPK, jurorPK);
   report.Database.Tables["JurorQuestionAnswers"]
     .SetDataSource((IDataReader)qaReader.DataReader);
}

However, when my report runs, the Group Heading displays and I get a record number printed for each question but the Question and Answer are not displayed.

It would appear, that Crystal Reports is not mapping the data results from my second query to the DataTable.

What's even weirder is that JurorQuestionAnswers DataTable currently has a redundant Lastname and Firstname field. If I include those fields in the detail area (JurorQuestionAnswers.Lastname, JurorQuestionAnswers.Firstname), they actually show up -- the data was properly mapped.

What am I doing wrong?

Update - Image of Links Tab

If you inspect the content of report.Database, can you see that the data is really there? I notice you use two different names: JurorAnswers and JurorQuestionAnswers, but I think it is ok - but you want to check if those names must be the same... Third and last, check if you are linking the tables in crystal, in the tab "Link" that is displayed in the second image (this is not THE problem, but it can be ONE problem later). — heringer
If I add code to loop through the reader values -- yes, the data is there in the reader. I tried inspecting the content of report.Database. It definitely shows both tables as well as the link between them but I wasn't able to find any kind of property that held data -- for either table. — RHarris
Just to anticipate some thoughts to follow: I'm thinking in these possibilities: (1) the data is not there; (2) the names of columns and tables or types in the dataset are different from those defined for the rpt (do you use XSD file?); (3) something is suppressing the "invisible" fields (did you use the suppress formula?). — heringer
I remembered one more thing: may you show the tab "Links" of your "Database Expert"? And what happens when you use the "preview" feature? — heringer

RHarris RHarris · Accepted Answer · 2020-09-14T15:07:44

I finally got this to work. Basically, it involved replacing the DataSet that existed on the report with a new DataSet.

I had to change my code as follows:

public void SetDataSource(AdoProvider provider, int sessionPK, int jurorPK)
{
   string commandText;
   AdoQuery query;
   
   // ** Added Direct Reference to DataSet **//
   var ds = new QuestionAnswersData(); 

   commandText = $@"select j.* from Juror j
   where j.JurorPK in (select JurorFK from SessionJurorLink where SessionFK = @sessionId)
   {(jurorPK > 0 ? " and jurorPK = @jurorId" ? "")}
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader jReader = query.ExecuteReader(sessionPK, jurorPK);

   // ** REPLACED THIS LINE **//
   // report.Database.Tables["JurorProfile"]
   //  .SetDataSource((IDataReader)jReader.DataReader); 

   //** WITH THIS LINE **//
   ds.Tables["JurorProfile"].Load((IDataReader)jReader.DataReader, LoadOption.OverwriteChanges, null);  

   commandText = $@"select ... from JurorAnswers ...
   where sessionFK = @sessionId
   {(jurorPK > 0 ? " and jurorFK = @jurorId " : "")}
   order by JurorFK, Rank, ParentFK, QuestionPK
   ";
   query = provider.CreateQuery(commandText, null
      , provider.CreateParameter("sessionId", DbType.Int32)
      , provider.CreateParameter("jurorId", DbType.Int32)
   );
   AdoReader qaReader = query.ExecuteReader(sessionPK, jurorPK);

   //** REPLACED THIS LINE **//
   //report.Database.Tables["JurorQuestionAnswers"]
   // .SetDataSource((IDataReader)qaReader.DataReader); 

   //** WITH THIS LINE **//
   ds.Tables["JurorQuestionAnswers"].Load((IDataReader}jReader.DataReader, LoadOption.OverwriteChanges, null);

   //** NOW TIE THE DATASOURCE DIRECTLY TO THE REPORT **//
   base.SetDataSource(ds); 
}

Crystal Reports won't display related table data

2 Answers