Solved

Crystal Report with dynamic query inside VB.NET

Posted on 2006-07-17
5
658 Views
Last Modified: 2012-08-13
Hi Experts,

I am having trouble with creating a dataset with a dynamic query and using this dataset to populate a Crystal Report.  I have tried two ways.  The frist was to plainly create the dataset and set the datasource and show the form with the report inside.  Nothing happens.  Second try was creating an XML and have the report use the fields as parameters to no avail.  I am stuck and this project is very important to our IT Staff.  I will show some code.

1st Try:
        Dim newView As New rptViewProject
        Dim newfrmReport As New frmReport
        Dim myDataAdapter As New OleDb.OleDbDataAdapter

        myDataAdapter.Fill(myDataSet)

        Dim myDataSet As New DataSet
        myDataAdapter.SelectCommand = New OleDb.OleDbCommand("Select * from items", cnOleDb)
   
        newView.SetDataSource(MyDataSet)
        newfrmReport.CrystalReportViewer1.ReportSource = newView
        newfrmReport.ShowDialog()

returns a blank report.  Is there anything I need to have set in Crystal tht I might be missing?  I know its a vague question but the use of database fields or parameters means different report builds.  I have done the necessary steps to build the report properly ( I think)  I do have other reports for other programs but the all have static information and are not queried.

2nd Try (XML):
        Dim newView As New rptViewProject
        Dim newfrmReport As New frmReport
        Dim myDataAdapter As New OleDb.OleDbDataAdapter

        Dim myDataSet As New DataSet
        myDataAdapter.SelectCommand = New OleDb.OleDbCommand("Select * from items", cnOleDb)

        myDataAdapter.Fill(myDataSet)
   
        Dim result As String = myDataSet.GetXml()
        Dim fileName As String = "c:\temp\Listings.xml"
        Dim sw As IO.StreamWriter = New IO.StreamWriter(fileName)
        sw.Write(result)
        sw.Flush()
        sw.Close()

        newView.SetDataSource("c:\temp\Listings.xml")
        newfrmReport.CrystalReportViewer1.ReportSource = newView
        newfrmReport.ShowDialog()

Nothing here either.  Thanks for your time in advance.  I have looked through EE very thoroughly and got here but I can not seem to locate the information I think I am missing.  

Thanks again!

Lucas
0
Comment
Question by:Kudzullc
  • 3
  • 2
5 Comments
 
LVL 3

Author Comment

by:Kudzullc
ID: 17122697
cont.  

I created seperate .rpt files to except parameters in one and database fields in the other.

Lucas
0
 
LVL 1

Accepted Solution

by:
mordizer earned 500 total points
ID: 17122883
Hi Kudzullc, I'm not really an expert around here but I've just solved a similar problem and it might get you going while you wait for someone cleverer to chip in :)

Basically I did this to solve my problem :

1)  I made a typed dataset to hold the data I wanted to report on and used that to build the visual report in the normal designer.

2)  I made a new class and inherited from the report I designed in step one and made a private instance of a typed dataset of the same type as I built the report off.

3)  In the constructer of this new class I passed in the dynamic parts of the query I wanted to run and then filled the dataset within the class.

4)  I called the me.setdatasource(dsExample) to bind my new report class to the dataset I'd built.

For example if you create a dataset called dsCatalogue and drag the tables onto it to show a product catalogue you could use that to develop a report called ShowCatalogueReport.  Once you're happy with that report save it and create a new class - call it BindDataToReport or something and it would look like this.

Public class BindDataToReport
    Inherits ShowCatalogueReport

    Private mdsCatalogue as new CatalogueDataset

    Public Sub New(byval strProductHeader as string)
         'Some code here to fill mdsCatalogue with data filtered by strProductHeader
         me.setdatasource(mdsCatalogue)
    end sub

End class

You could then use this new class as a report document to show in a viewer.

This has given me a little more control over how I fill up my dataset before it gets bound to the report and if you can figure out my instructions it might keep you going until someone posts the proper way of doing it :)
0
 
LVL 3

Author Comment

by:Kudzullc
ID: 17123928
Thanks for the advice!  I will try it out!

Lucas
0
 
LVL 3

Author Comment

by:Kudzullc
ID: 17125043
I used the idea and I have it working.  Instead of Crystal Reports I used PrinterClass and used a template.  Maybe a little more work then I anticipated but it does exactly what I need it to!  Thanks for the help!

Lucas
0
 
LVL 1

Expert Comment

by:mordizer
ID: 17128623
Glad it got you moving again :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Suggested Solutions

Title # Comments Views Activity
rebind a grid after user clicks on node in treeview 1 41
Import Data from Multiple Text Files in Excel 12 61
write xml in vb.net 2 33
MailAddress in vb 4 29
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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 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