1

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 join two tables and display their matched records on the report:

    Try
        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 a JOIN customer b ON b.accountNumber = a.customer_accountNumber;"
        Dim dbAdapter As New MySqlDataAdapter(dbQuery, dbConn)
        Dim dbTable As New DataTable
        dbAdapter.Fill(dbTable)
        Dim report As New rptCardListAll
        report.SetDataSource(dbTable)
        CrystalReportViewer1.ReportSource = report
        CrystalReportViewer1.Zoom(1)
    Catch ex As Exception
        'MsgBox(ex.Message)
    End Try

The problem I'm running into now is that when the report runs at run-time, all the db records are populated on the report except for the one field that I'm pulling from the CUSTOMER table. Below is a screen shot. Notice the blank CUSTOMER NAME - this shouldn't be blank because I know for a fact there's data in that field for every record.

The query works fine when I run it directly on the DB using MySQL Workbench, so I can't figure out why the report won't pull the requested information. Any help would be appreciated, thanks.

Report Snapshot

EDIT: Screenshot showing DataSet Visualizer during debug containing the missing field (nameCOMPANY)

Report Snapshot

Kismet Agbasi
  • 557
  • 2
  • 8
  • 28
  • What's the format of this report? The 'customer name' field looks like it's in a header block, and so I wouldn't expect to source this data from the primary query, which renders the records in the body of the report. I bet if you moved that field to the body of the report it would start working (albeit repeated for every record in a way that you don't want). I've not worked with CR for many years, but hopefully that might give you some ideas to start digging. – halfer Jan 22 '13 at 21:22
  • I wonder, could you attach `MIN(customer_name)` to that field? If the customer name is the same across all fields in the recordset, you just need an aggregate function to pull one of those copies out. – halfer Jan 22 '13 at 21:24
  • Is it necessary that you put the `*` sign to get all the fields? why don't you put the exact field names so you'll be sure you're getting what you expected? – luchosrock Jan 22 '13 at 21:30
  • @halfer - The Customer Name field (nameCUSTOMER) is an actual database field. It's placed right next to the header (which you can see). – Kismet Agbasi Jan 23 '13 at 02:37
  • @luchosrock - yes I know it can be overkill to use "*" but the recordset it relatively small and I don't expect to have a heavy load on this particular DB, so I preferred to just pull all the records. – Kismet Agbasi Jan 23 '13 at 02:39
  • Does the report work OK when run manually from inside CR designer? – halfer Jan 23 '13 at 08:43
  • In response to my earlier comment, you haven't really responded to the point I was trying to make. I wonder whether it is a feature of the header that cannot access records in your primary loop (unless, perhaps, you use an aggregate function). Though bear in mind this is a shot in the dark! To determine this, try adding a variety of other fields from your resultset in the header, to see if they have trouble appearing as well. – halfer Jan 23 '13 at 08:46
  • @halfer I'm guessing the header is not the issue; the customer account number shows up fine, and I assume that is in the same table as the name. Even still, adding the name to the detail section is a good test. Maybe the data could be grouped on customer ID and the customer data put into the group header instead of the report header. – EvilBob22 Jan 23 '13 at 23:00
  • @halfer - I don't think the header is the issue because as EvilBob22 points out, the customer ID is displaying properly in the group header. I don't know how to do aggregate functions yet, but I'll try adding other fields to see if the same problem occurs and report back. To answer your other question, the CR designer doesn't show me actual DB data - it only shows some random text. – Kismet Agbasi Jan 24 '13 at 13:23
  • @EvilBob22 - I added the nameCUSTOMER field to the data area and still it didn't show. To answer your question, no this field is coming from a different table (customer). The rest of the data on the report is coming from the (cc_master) table. – Kismet Agbasi Jan 24 '13 at 13:25
  • Fair enough, just trying to throw some ideas into the pile. CR designer definitely should show you real data when you run the report in the designer application. Have you tried connecting to your database this way? – halfer Jan 24 '13 at 13:25
  • @halfer - I found out that CR doesn't support connecting to a MySQL db, so the workaround is to create an dataset using the MySQL Connector. That's what I've done and that's where I get the db fields to create the report. So during design, it doesn't show actual db data - but a runtime it does. – Kismet Agbasi Jan 24 '13 at 13:56
  • In that case, can you use the MySQL Connector to connect to your real MySQL db? `:-)` http://stackoverflow.com/questions/7020083/connecting-mysql-with-crystal-reports-v10 – halfer Jan 24 '13 at 14:05
  • @halfer - Thanks for the reference material. Yes I can and did use the MySQL Connector to connect to the server. This created a server instance in the Server Explorer. I then created a DataSet in the Data Sources Explorer with that connection. Then I was able to use the CR Report Wizard to select the fields from the DataSet I created. At run-time, I'm querying the DB for the same records, creating a datatable (which should contain those fields) and it's working - except for the one field from another table. Not sure if that's the problem - that it's coming from another table. – Kismet Agbasi Jan 24 '13 at 16:35
  • @halfer - I've edited my question to show a screenshot of the DataSet Visualizer during debug - you can see clearly that the missing field (nameCUSTOMER) is being pulled from the DB. I just can't figure out why it won't show on the report. – Kismet Agbasi Jan 24 '13 at 16:37
  • I'm out of ideas - not an expert at all - but if you are still stuck in a day or two, let me know and I'll add a bounty for you. – halfer Jan 24 '13 at 16:52
  • Interesting, in theory creating an ODBC for your database (using the MySQL Connector) could allow you to connect Crystal that way without using VB at all. Then, Crystal would handle the join and tables for you. Although taking VB completely out of the picture may not be an option in your case. – EvilBob22 Jan 24 '13 at 17:32
  • Have you tried `"SELECT a.*, b.* "`, or manually naming all of the fields in the `SELECT`? – EvilBob22 Jan 24 '13 at 17:33
  • @halfer - I really appreciate the assistance thus far, and especially the lack of insults. Some people on here would've written this off, but I really appreciate your help. – Kismet Agbasi Jan 24 '13 at 18:29
  • @EvilBob22 - Yes I've tried selecting the records individualy. In fact in the updated screenshot showing the DataSet Visualizer, that's how I've queried the DB and it returns all the records I need but CR just won't show that one field.......it's really strange. I'll keep researching hoping to find a resolution. I haven't done the ODBC route because I don't know how to make it work on each workstation after the application is deployed. – Kismet Agbasi Jan 24 '13 at 18:31
  • No worries. Your question survived because it was well-written, illustrated and you'd made a solid attempt already. SO may sometimes seem like a bear-bit, but it's because we get a _lot_ of help vampires on here. Glad you fixed it! – halfer Jan 25 '13 at 10:51

1 Answers1

2

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 @halfer, @luchosrock, and @EvilBob22 for their 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?

Community
  • 1
  • 1
Kismet Agbasi
  • 557
  • 2
  • 8
  • 28