What could be causing a missing DB field on my Crystal Report at run-time?

[Former Member]

Ladies & Gentlemen,

I have been, thus far, successful in programmatically pulling records from a MySQL database to create a crystal report from a single table.  Using the code below, I'm trying to pull records from two tables - customer & cc_master.  Now, all the fields are coming from the cc_master table except for one - nameCOMPANY - which is coming from the customer table.

At during debug, the report is displaying all the records from the cc_master table and the one field from the customer table isn't being displayed.  Any help would be greatly appreciated - my knowledge is quite limited, so please go easy on me.  All that I've done thus far is by watching Youtube videos and reading online articles.  Below is my code and two screenshots (one showing the report and the other showing the DataSet Visualizer with the records from the query).

Here's my VS Code containing the query:

Try
   NotifyIcon1.ShowBalloonTip(3000, "Running Report, Please Wait.....", "The report you requested is being generated." & vbCrLf & _
                               "Please be patient, based on the data being requested it could take more than a few seconds.", ToolTipIcon.Info)
   Dim myConnectionString As String = "Server=" & FormLogin.ComboBoxServerIP.SelectedItem & ";Port=3306;Uid=parts;Password=parts;Database=accounting;"
   Dim dbQuery As String = "SELECT customer_accountNumber, nameCOMPANY, ccID, cardholderFirstname, cardholderLastname, cardholderSalutation, ccNumber, " & _
                           "ccExpireMonth, ccExpireYear, ccZipcode, ccLocation, ccType, ccAuthorizedUseStart, ccAuthorizedUseEnd " & _
                           "FROM customer a, cc_master b " & _
                           "WHERE a.accountNumber = b.customer_accountNumber"

   Dim dbAdapter As New MySqlDataAdapter(dbQuery, dbConn)
   NotifyIcon1.ShowBalloonTip(3000, "Querying the MySQL Database....", "The records necessary to compile your report " & vbCrLf & _
                               "are being retrieved from the datasource.", ToolTipIcon.Info)
   Dim dbTable As New DataTable
   dbAdapter.Fill(dbTable)
   Dim report As New rptCardListAll
   report.SetDataSource(dbTable)
   CrystalReportViewer1.ReportSource = report
   CrystalReportViewer1.Zoom(1)
   NotifyIcon1.ShowBalloonTip(3000, "Report Ready!", "The report you requested is now ready to view." & vbCrLf & _
                                           "Thanks for your patience.", ToolTipIcon.Info)
Catch ex As Exception
            'MsgBox(ex.Message)
            NotifyIcon1.ShowBalloonTip(3000, "AN ERROR HAS OCCURED...", ex.Message & vbCrLf & _
                               "Please report the problem to the IT/Systems Helpdesk @ Ext 131.", ToolTipIcon.Error)
End Try

[Former Member · Accepted solution]

Good evening all,

So after hours of reading and searching the web, I've managed to arrive at or better yet, discover, a solution to my problem.

It appears that even though I'd created a DataSet within VS and used that to create my CR Report, I wasn't actually using that DataSet in code.  Instead what I was doing was creating a new DataTable at run-time, filling that with my query result, and setting the report's datasource property to it.

What I should have been doing was to create an instance of my DataSet (the one I created earlier and used to design the report), fill it with my query result, and set the report's datasource property to it.  This allowed CR to recognize and respect the table links/relationships I established earlier in the DataSet designer.  I also learned that when using the DataAdapter with a query that returns multiple tables, the default naming convention is "Table" then "Table1" and so forth - that it was necessary to map these to the actual names of my tables in the DB.

So after applying all these lessons, I had to re-do my code as follows:

	Dim report As New rptCardListAll
	Dim myConnectionString As String = "Server=" & FormLogin.ComboBoxServerIP.SelectedItem & ";Port=3306;Uid=parts;Password=parts;Database=accounting;"
	Dim dbConn As New MySqlConnection(myConnectionString)
	Dim dbQuery As String = "SELECT * FROM cc_master; " & _
	"SELECT * FROM customer;"
	Dim dbAdapter As New MySqlDataAdapter(dbQuery, dbConn)
	With dbAdapter
	.TableMappings.Add("Table", "cc_master")
	.TableMappings.Add("Table1", "customer")
	End With
	Try
	Dim dbDataSet As New accountingDataSet
	dbAdapter.Fill(dbDataSet)
	report.SetDataSource(dbDataSet)
	CrystalReportViewer1.ReportSource = report
	CrystalReportViewer1.Zoom(1)
	Catch ex As Exception
	MsgBox(ex.Message, MsgBoxStyle.OkOnly, "An Error Has Occured....")
	End Try

My report now shows the missing field "nameCOMPANY" from the customer table.

CREDIT: I want to thank Ludek for the assistance.  Also, I give credit to the authors in the following documents:

http://developer-content.emc.com/developer/downloads/CrystalReport_ADO_Dataset.pdf

How to fill Dataset with multiple tables?

[former_member183750 · Accepted solution]

Hello Kismet-Gerald

Many thanks for sharing your solution with the community. I am sure it will be something very useful for those following in your footsteps. Just for completeness, now that we have a number of links in this thread, I'd like to add two more links (sorry Kismet-Gerald, I suspect the 1st one would have saved you time, but I never thought of it):

Crystal Reports Guide To ADO.NET

Crystal Reports for Visual Studio 2005 Walkthroughs (applies to VS 2008, 2010 and 2012)

- Ludek

[Former Member · Accepted solution]

Yeah, that would've saved me a lot of time.  But I'm still appreciative of your assistance.

[former_member183750]

Hello

There is a number of reasons and I'll list the most frequent ones below:

1) May be a bug, possibly one that is resolved, but you did not specify what version of CR you are using or version of .NET (see 2), so I can's say

2) May be that you are using a version of CR that is not supported in your version of .NET

3) If the two tables are not linked, this will also cause issues

4) If the report thinks there is a schema missmatch, it may simply remove the field from the report (enable the option "Verify on 1st refresh", see what that does). I see that you are using a dataset. To troubleshoot possible schema issues, see the following wiki:

http://wiki.sdn.sap.com/wiki/x/MofhEg

All of the above is assuming that this report actually does work somewhere, e.g.; in CR designer(?).

- Ludek

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Ludek,

Thanks for your response and for the reference article - will take a look at it shortly.  My apologies for not providing version information.  Here it is:

1. SAP Crystal Reports, version for Visual Studio 2010
2. Visual Studio 2010 Ultimate
3. Microsoft .Net 4.5 Runtime
4. Database:  MySQL Community Server v5.5.29
5. Connector:  MySQL Connector 6.6.4

To answer your question, yes - the report is working at run-time.  The only problem is, one field from the customer table is being suppressed. 

[former_member183750]

Make sure you are on SP5

Make sure the tables are linked

Make sure you consult the wiki I recommended in my previous post.

- Ludek