Solved

Creating a Crystal Report using multiple sql tables

Posted on 2008-09-29
14
700 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Suggested Courses

623 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