Please help! Having trouble finding records when searchign two tables.

[Former Member]

Hi, I've been trying to solve this problem for several weeks now, and I have exhausted all of my knowledge and experience. I need help, and I hope someone here can give me some direction.

I am using VB 2008, and the CR that comes bundled with VS 2008 Pro. My database is a SQL Server 2005 CE v3.5 (a *.sdf file). I am connecting to the database through a dataset, and I am displaying the report in a CrystalReportViewer.

My dataset consistes of two tables:

1) tblCustomers which has a primary key "CustID", and contains only customer contact and personal information.

2) tblDateVisited which has a primary key of "VisitID", but it also has a column titled "CustID". Basically, every time a customer visits the business, details of that visit are recorded in tblDateVisited, and that record is associated with the customer by their CustID.

Here's what I'm trying to accomplish: I want to be able to display only Customer records when the customer has visited and that visit matches certain criteria. Right now, I am trying to match visits from the "tblVisitDate.PlayerType" column. If the customer has ever had a visit where they matched a particular player type, I want to see those customer records.

I don't know what I'm doing wrong, though. I can search a dataset if I am only querying one table and pulling records from that table. However, whenever I try to add a second table and perform queries on that table to get records from the first table, I can't return any records.

If it helps, I am trying to use one CrystalReportViewer to display multiple reports (user choice) and here's how I'm loading the report into the viewer:

Me.tblCustomersTableAdapter.Fill(Me.dsPlayerTypeReports.tblCustomers)

Me.tblDateVisitedTableAdapter.Fill(Me.dsPlayerTypeReports.tblDateVisited)

Me.ReportFile.SetDataSource(dsPlayerTypeReports.Tables(1))

I am suspicious that my problem is in the Tables(1) method. It confuses me that I can only assign one table as a datasource when I obviously need access to two tables to make this selection work.

Whatever the case, I'm at the end of my rope with this one. I'm not prone to giving up, but I'm at a dead end currently.

Any attempt to assist me with this will be greatly appreciated, successful or not.

Thanks in advance!

-Will

[former_member183750 · Accepted solution]

You can specify the exact "tables" to read as:

crReportDocument.Database.Tables(0).SetDataSource(dataSet.Tables("NAME_OF_TABLE"))

crReportDocument.Database.Tables(1).SetDataSource(dataSet.Tables("NAME_OF_TABLE2"))

Normally, this will make Crystal reports look at both tables, but the above will be more explicit:

crReportDocument.SetDataSource(dataSet) or crReportDocument.SetDataSource(dataSe.Tables(0))

Note that the report will not do any linking for you. The linking needs to be created the time you create the report. For more info re. datasets and Crystal Reports, please see this:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5a...

Ludek

[Former Member · Accepted solution]

Finally some fresh code for me to explore!

I'll play around with that for awhile and see what I can come up with. I'll update this post with the outcome.

Thanks!

...UPDATE...

WOO HOO! It worked, and I can now dynamically update my CrystalReportViewer with reports that use more than one table!

THANK-YOU!

Edited by: Will Smith on Sep 18, 2008 8:26 PM

[former_member183750 · Accepted solution]

Good news. Nothing like some info. All I can do is apologize that it's a bit hard to find, but we're working on it.

Ludek

[Former Member · Accepted solution]

Hi

I have been having the same problem and the above does indeed work.

However, is there no way of achieving the same result using a single SQL statement which joins the two tables rather than 2 DataAdapters? I need to add a complex, user-generated WHERE clause at runtime which may/may not filter records from both tables.

Thanks in advance.

Jonathan Attree

[former_member183750 · Accepted solution]

Not sure if I understand the query, but you should be able to build a single ADO .NET dataset from your SQL statement and then pass that dataset to the report. This White Paper discusses how CR works with ADO .NET datasets in more detail:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/401c4455-a31d-2b10-ae96-fa57af5a...

Ludek

[Former Member · Accepted solution]

Thanks for the reply.

What I am trying to do is create a report based on a Dataset from a query like this:

SELECT tblMedia., tblTitles. FROM tblMedia LEFT JOIN tblTitles " & _

"ON tblMedia.ODGMediaID = tblTitles.ODGMediaID WHERE ... ORDER BY.... etc

This Dataset will have one table which is the combined records from tblMedia and tblTitles.

I have set up the .xsd schema in my VS 2005 project with these two linked tables and designed the report accordingly.

If I only add fields to my report from tblMedia everything is fine but as soon as I add any field from tblTitles to the report design no data is displayed in the report.

The document you provided a link to has helped greatly as I can now at least make it work but only by using two separate SQL statements and DataAdapters which in turn means I will have to create two WHERE clauses which all seems rather long-winded.

Any more suggestions gratefully received.

Jonathan Attree

[former_member200290]

Hi Will,

I guess the question I have for you is if we take Crystal Reports right out of the situation can you create an ado record set using the query with the "where" clause you want? If you can then you are set. You can programattically generate the data you need and pass that to the report. The dataset has the .WriteXMLSchema method to create the schema of the result set, and you can use this file to build your report.

Trevor.

[Former Member]

Hi

I finally figured this out (or at least a way of doing it that seems to work!

In VS create a new, empty datasource with no database objects in it. You'll get a message saying asking if that's what you really want to do.

Add a TableAdapter to the datasource using the 2-table SQL statement with a join but no WHERE clause, in my case:

"SELECT tblMedia., tblTitles. FROM tblMedia LEFT JOIN tblTitles ON tblMedia.ODGMediaID = tblTitles.ODGMediaID "

Make sure Fill a DataTable and Return a DataTable are checked.

Rename the TableAdapter and DataTable to something sensible.

Design the report using the new Dataset.

To display the report in VB with user generated WHERE clause:

strWhere = "WHERE ID=..." etc

strSQL = "SELECT tblMedia., tblTitles. FROM tblMedia LEFT JOIN tblTitles " & _

"ON tblMedia.ODGMediaID = tblTitles.ODGMediaID " & strWhere

Dim conn As OleDbConnection = New OleDbConnection(strConn)

Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, conn)

Dim dsReport As New DataSet

da.Fill(dsReport, "DataTableName")

Dim rpt As New ReportDocument

rpt.Load(PathToReportFile)

rpt.SetDataSource(dsReport)

crReportViewer.ReportSource = rpt

Jonathan Attree