Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating a Crystal Report using multiple sql tables

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

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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