[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Crystal Report Problem

Posted on 2009-12-16
18
Medium Priority
?
344 Views
Last Modified: 2012-05-08
I have the following code below and I have all my datasets and reports there.

When I click on a button on my main form to show frmViewer it shows me the report but none of the data.

I have attached a pic to show
Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data

Public Class frmViewer

    Dim objRpt As New CrystalReport2
    Public strSQL As String

    Private Sub frmViewer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim con As SqlConnection

        con = New SqlConnection(My.Settings.Constr)
        con.Open()

        Dim dscmd As New SqlDataAdapter(strSQL, con)
        Dim ds As New DataSet2

        dscmd.Fill(ds, "tblSettings")
        objRpt.SetDataSource(ds.Tables(1))
        CrystalReportViewer1.ReportSource = objRpt
        CrystalReportViewer1.Refresh()

    End Sub

End Class

Open in new window

Untitled.gif
0
Comment
Question by:richard_gar
  • 8
  • 7
  • 3
18 Comments
 
LVL 27

Expert Comment

by:VBRocks
ID: 26067039
Why don't you try this:

        dscmd.Fill(ds, "tblSettings")

        'Display the number of rows in the table before setting it as the datasource
        MsgBox ds.Tables("tblSettings").Rows.Count

        objRpt.SetDataSource(ds.Tables("tblSettings"))  

        CrystalReportViewer1.ReportSource = objRpt


Also, did you build the report off DataSet2, using tblSettings?

0
 

Author Comment

by:richard_gar
ID: 26067185
Yes I did build the report off dataset2 using tblSettings

I tired the messagebox and that shows 0 records.
0
 
LVL 10

Expert Comment

by:anv
ID: 26070593
did you write this? objRpt.Load(Application.StartupPath & "/ReportName.rpt")
0
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.

 
LVL 10

Expert Comment

by:anv
ID: 26070609
Does it show the data when use pass the DataSet instead of DataTable?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26072563
If the messagebox shows 0 records, then you're not even getting the data loaded into your table correctly.

Can you show me your SQL select statement that the "strSQL" variable is set to?  For example:

    strSQL = "SELECT * FROM [TableName]"  

        'Replace [TableName] with the name of the table the data is coming from
0
 

Author Comment

by:richard_gar
ID: 26072983
select * from tblSettings
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26073132
Ok, so try this and see if it works:

strSQL = "SELECT * FROM tblSettings"

Dim ds As New DataSet2
Dim dscmd As New SqlDataAdapter(strSQL, My.Settings.Constr)
dscmd.Fill(ds, "tblSettings")

MsgBox ds.Tables("tblSettings").Rows.Count



0
 

Author Comment

by:richard_gar
ID: 26073181
k, that shows me that I have one record.

But I still cannot see the data on my crystal report.
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26073251
Ok, now that we have that working, let's verify the data:

strSQL = "SELECT * FROM tblSettings"

Dim ds As New DataSet2
Dim dscmd As New SqlDataAdapter(strSQL, My.Settings.Constr)
dscmd.Fill(ds, "tblSettings")

dim r As DataRow = ds.Tables("tblSettings").Rows(0)

'This messagebox should display the data for the row:
MsgBox r("StoreAddress") & vbcrlf & _
              r("StoreAddress1") & vbcrlf & _
              r("StoreTel") & vbcrlf & _
              r("StoreEmail") & vbcrlf & _
              r("StoreWWW")
0
 

Author Comment

by:richard_gar
ID: 26073265
yeap the data shows in the messagebox
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26073321
ok, good.  So we also need to make sure we're setting the report correctly:

strSQL = "SELECT * FROM tblSettings"

Dim ds As New DataSet2
Dim dscmd As New SqlDataAdapter(strSQL, My.Settings.Constr)
dscmd.Fill(ds, "tblSettings")

Dim rpt As New CrystalReport2
rpt.SetDataSource(ds.Tables("tblSettings"))
CrystalReportViewer1.ReportSource = rpt

0
 

Author Comment

by:richard_gar
ID: 26073335
k, the report loads but the data is not there.
0
 
LVL 27

Assisted Solution

by:VBRocks
VBRocks earned 2000 total points
ID: 26073575
Ok, try moving the fields from the Page Header to the Detail section, and see if anything displays.
0
 

Author Comment

by:richard_gar
ID: 26074237
nope made no difference.

I have posted the code I am now using
        Dim strSQL As String
        Dim objRpt As New CrystalReport2

        strSQL = "SELECT * FROM tblSettings"

        Dim ds As New DataSet2
        Dim dscmd As New SqlDataAdapter(strSQL, My.Settings.Constr)
        dscmd.Fill(ds, "tblSettings")

        Dim rpt As New CrystalReport2
        rpt.SetDataSource(ds.Tables("tblSettings"))
        CrystalReportViewer1.ReportSource = rpt

Open in new window

0
 
LVL 27

Expert Comment

by:VBRocks
ID: 26074632
How big is your app?  Anyway you can zip it up and upload it so I can take a look at it?
0
 
LVL 10

Expert Comment

by:anv
ID: 26077975
Hi Richard:

Try this once:

Does it show the data when use pass the DataSet instead of DataTable?
0
 

Author Comment

by:richard_gar
ID: 26079588
it won't let me keeps saying about file exentions not allowed.
0
 

Accepted Solution

by:
richard_gar earned 0 total points
ID: 26084283
I fixed it,

I had to have all the tables in view for the details to show.
Dim rpt As New CrystalReport2()
        Dim myConnection As SqlConnection
        Dim MyCommand, MyCommand1, MyCommand2, MyCommand3 As New SqlCommand()
        Dim myDA, myDA1, myDA2, myDA3 As New SqlDataAdapter()
        Dim myDS As New DataSet2()

        myConnection = New SqlConnection(My.Settings.Constr)

        MyCommand.Connection = myConnection
        MyCommand.CommandText = "SELECT * FROM tblSettings"
        MyCommand.CommandType = CommandType.Text
        myDA.SelectCommand = MyCommand
        myDA.Fill(myDS, "tblSettings")

        MyCommand1.Connection = myConnection
        MyCommand1.CommandText = "SELECT * FROM tblCustomers WHERE CustRef = 'SG1234'"
        MyCommand1.CommandType = CommandType.Text
        myDA1.SelectCommand = MyCommand1
        myDA1.Fill(myDS, "tblCustomers")

        MyCommand2.Connection = myConnection
        MyCommand2.CommandText = "SELECT * FROM tblOrders WHERE CustRef = 'SG1234' AND OrderRef = '1234'"
        MyCommand2.CommandType = CommandType.Text
        myDA2.SelectCommand = MyCommand2
        myDA2.Fill(myDS, "tblOrders")

        MyCommand3.Connection = myConnection
        MyCommand3.CommandText = "SELECT * FROM tblOrderLines WHERE CustRef = 'SG1234' AND OrderRef = '1234'"
        MyCommand3.CommandType = CommandType.Text
        myDA3.SelectCommand = MyCommand3
        myDA3.Fill(myDS, "tblOrderLines")

        rpt.SetDataSource(myDS)
        CrystalReportViewer1.ReportSource = rpt

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

831 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