Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Creating a Crystal Report using multiple sql tables

Posted on 2008-09-29
14
Medium Priority
?
705 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 101

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 101

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 101

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 101

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 101

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 101

Accepted Solution

by:
mlmcc earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

885 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