Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

Creating a Crystal Report using multiple sql tables

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
nsfranklin
Asked:
nsfranklin
  • 6
  • 6
1 Solution
 
mlmccCommented:
How did you build the report?

Did you build it against that query?

mlmcc
0
 
nsfranklinAuthor Commented:
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
 
mlmccCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
nsfranklinAuthor Commented:
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
 
mlmccCommented:
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
 
nsfranklinAuthor Commented:
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
 
mlmccCommented:
Are you running from an application?
If so you can pass a username and password to the report in the application

mlmcc
0
 
nsfranklinAuthor Commented:
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
 
mlmccCommented:
How do you call the report?

mlmcc
0
 
nsfranklinAuthor Commented:
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
 
nsfranklinAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now