Crystal Reports help

[edmund]

I have a report designed in crystal reports (CR for .NET) using a MySQL database. The report is based on a query, which is set in the report. My headache is that I can't figure out how to change the query at runtime using vb.net. Any help would be truly appreciated.

 

Thanks

[Phylum]

What do you need to change about the query, what are you trying to do?

[edmund]

ok here is the scenario. The report/form is already designed to the specs (it has to look close to exact as a CA state form. There are 4 rows to a page that could be multiple pages. These rows have contain 14 data items (fields) to populate psuedoo checkboxes(an "X" inside a box), text and currency types. To get the data required to populate the form it requires multiple joins and nested recordsets.

 

Now, its a client server app, my solution was to grab the data and store it in a table emulating a row in the form based on the system users id and indexed by an identity so that I can create new instances of the report populate it, preview it, print it then dump the data. In the crystal report itself I get the data by storing a sql command:

 

"SELECT * FROM tablename WHERE useid = 1 order by id"

 

The report populates beautifully.

 

I just want to change the 1 (ie user id) in the query, depending on the user. How does one change the query at runtime? There's got to be a solution to this.

 

Scenario number two basing a report on a date range. Another just change the query situation. hmm

 

Any help would certainly be appreciated.

[Phylum]

Parameters. I know t-sql supports it. "SELECT * FROM tablename WHERE useid = @useid order by id". Look at the dataadapter parameters method to learn how to fill the parameter at run time. The syntax is something like this:

 

daMyAdapter.Parameters("@useid").value = "1"

[edmund]

Thanks, I'll look into it and let you know what happens. Would you know how to bypass the prompt for a parameter field?

[Phylum]

bypass the prompt?

[Madz]

How Crystal Reprot Works with ADO.NET

 

How Crystal Reprot Works with ADO.NET

 

ADO.NET data access model i entirely diffierent from other data access methedologies.

 

if you are going to use ADO.NET to fill crystal Reports you need to do things in theis sequence.

 

1- Design an XML Schma Defination file (xsd) which will be used while designing Crystal Report. this contains nothing just tables which would be contained on Report.

 

2- Design a Crystal Report.

 

3- add a Crystal Report Viewer to some form and bind it to the Report which we designed and fill the report with ADO.NET data

 

here 's a little example. in C# it would be easier for VB Developers to understand

 

//Here i have made a function which Loads an Already Designed Report 

//in a Crystal Report Viewer and fills it with data

//this is same like you fill some DataGrid

// mysql is a Connection which has been already intialized so dont need to mention the Query String



using CrystalDecisions.CrystalReports; <OR in VB> imports CrystalDecisions.CrystalReports

//This is a simple SUB which takes an input parameter Query

// and use that Query string to get data.

public void LoadCarData(string strQuery)

{

    try

     {

           //rptCars is a Crystal Report which was designed to View Cars Sales

           rptCars rptc = new rptCars();

           //Here Defining a SQL Data Adapter and giving some Qurey (select * from cars)

           SqlDataAdapter myadp = new SqlDataAdapter(strQuery,mysql);

           //Here Defining a new DataSet myds

           DataSet myds = new DataSet();



           //Now Filling the DataTable Cars in the Dataset myds 

           // Dont be confused with Cars table we give this name to the result

           // Table for our Reference if you dont specify the name of Table

           // by Default DataAdapter gives it name (Table1)

           myadp.Fill(myds,"Cars");

           //Next I am doing seting the CarReport Object to get data from this tables

           rptc.SetDataSource(myds.Tables["Cars"]);

           //once my report is filled with records i am setting Crystal Report Viewer's 

           //Report Source Property to the Report Object which we instentiated from 

           //and already designed report.

           reportviewer.ReportSource = rptc;

           reportviewer.Zoom(2);

     }

     catch (Exception ex)

     {

           //If some error occures it will show it in a messageBox

           MessageBox.Show(ex.Message,"Error");

     }

     finally

     {

           if (mysql.State==ConnectionState.Open)

           {

                 mysql.Close();

           }

     } 

}

[edmund]

Interesting, I'm not sure I can use ADO with MySQL, learning curve I guess. Thanks