Solved

Creating a Crystal Report using multiple sql tables

Posted on 2008-09-29
14
695 Views
Last Modified: 2012-06-27
Hi

I am trying to create a crystal report using data from multiple tables,  using the crystal report designer I have dragged the fields onto the form, all the data from table t1 is displayed, but the data from table t2 is not. I have trapped the sql string and copied that into mssql to check that the data is pulled from the database, and it is.
My code is as follows

Try

Me.Cursor = Cursors.WaitCursor
sql = "SELECT t1.sop_record,t1.client,t1.store_number,t1.store_name,t1.store_address,t1.sop_detail,t1.equipment_detail,t1.date_raised,t1.date_required,t1.sop_by,t1.sop_value,t1.sop_status,t1.plans_reqd, t2.sop_record,t2.sop_audit_txt,t2.sop_changed_by,t2.sop_changed_date FROM sop_records AS t1, SOP_audit AS t2 WHERE t1.sop_record = 26 AND t1.sop_record = t2.sop_record ORDER BY t2.id DESC"
If myconn.State <> ConnectionState.Open Then
myconn.Open()
End If
Dim SOPHistoryReport As New SOPHistoryReport
da = New SqlDataAdapter(sql, myconn)
da.Fill(ds, "SOP_records")
SOPHistoryReport.SetDataSource(ds.Tables(0))
Me.CrystalReportViewer1.ReportSource = SOPHistoryReport
myconn.Close()
Me.Cursor = Cursors.Default
Catch ex As Exception
MsgBox("An error occured getting report data.")
Me.Cursor = Cursors.Default
myconn.Close()
Exit Sub
End Try

Any thoughts please
0
Comment
Question by:nsfranklin
  • 6
  • 6
14 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 22596699
How did you build the report?

Did you build it against that query?

mlmcc
0
 

Author Comment

by:nsfranklin
ID: 22596784
I built the report by creating an ADO.NET Dataset. The Dataset contained the two tables, from which I dragged the fields onto the report.

As mentioned, the t1 data displays fine, so I cannot understand why the t2 data does not.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22597160
Did you use that query to build the dataset?

You cannot change the SELECT part of the query after you build the report.

mlmcc
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:nsfranklin
ID: 22597243
OK maybe I have missed something here.

How do I create a Crystal Report from a dataset using the select statement format show in my question.

I assumed that once the fields were on the report, I could display the relevant data using any sql statement that gave me the fields I needed.

Is ADO.NET the best way.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22598353
To a certain degree you are correct.  The fields must be the same type as the pnes you built the report and in the same order.

Check this article
http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx

mlmcc
0
 

Author Comment

by:nsfranklin
ID: 22603553
OK I've got around the problem by using a different Data Connection. Instead of using and ADO.NET connection which caused the problem, I am now using an OLE DB(ADO) connection and all my data displays without a problem, using my original un-edited code as in my original question.

However I do have an issue whereby the password has not been store in the connection and to view the report I am asked to enter the MSSQL passsword.

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22605833
Are you running from an application?
If so you can pass a username and password to the report in the application

mlmcc
0
 

Author Comment

by:nsfranklin
ID: 22606219
yes I have tried that, but I keep getting a dialog box appear with the username, I must enter the password and click finish.

I have tried setting the logoninfo for the crystal report, but all it does is fill in the password, the dialog box still appears.

I apply the logoninfo after the datasource for the report, I have tried setting it before, but this throws an error.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 22606541
How do you call the report?

mlmcc
0
 

Author Comment

by:nsfranklin
ID: 22606630
Dim SOPHistoryReport As New SOPHistoryReport
da = New SqlDataAdapter(sql, myconn)
da.Fill(ds, "SOP_records")
SOPHistoryReport.SetDataSource(ds.Tables(0))
Me.CrystalReportViewer1.ReportSource = SOPHistoryReport
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 22607301
0
 

Author Comment

by:nsfranklin
ID: 22702652
Hi Sorry for the delay in posting. I have been working on another project.

I have not resolved the issue as yet, I have managed to show both sets of data on the crystal report, but am still getting asked for the passsword, so I still need to investigate further.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question