CODEWITHSUNDEEP
c#sql-serversql-server-2008crystal-reports
hKumudu
hKumudu

Reputation: 53

How can I create a Crystal Report between two dates

I tried to use the code below, but it doesn't work.

I stored report data in a ReportDataSet using an SQL query. Later I set this ReportDataSet as the DataSource for the CrystalReport object.

private void btnGenReport_Click(object sender, EventArgs e)
    {
        CrystalReport1 objRpt = new CrystalReport1();

        string sql; //creating sql query
        sql = "SELECT invoice.InvoiceNo, invoice.Date, invoiceitems.Name, invoice.InvoiceTo, invoice.CusName, invoiceitems.Qty, invoiceitems.Rate, invoiceitems.Amount ";
        sql += "FROM (invoice INNER JOIN invoiceitems ON invoice.Date = invoiceitems.Date) ";
        sql += "WHERE (invoice.Date BETWEEN '" + DateTimePickerFrom.Value + "' AND '" + DateTimePickerTo.Value + "');";

        ReportDataSet ds = new ReportDataSet();
            string path = Application.StartupPath + "\\";
        string conStr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=" + path + "SMS_DB.mdf;Integrated Security=True;User Instance=True";
        SqlConnection con = new SqlConnection(conStr); // create connection
        try
        {
            con.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds, "InvoiceItemData");
            objRpt.SetDataSource(ds.Tables["InvoiceItemData"]);
            crystalReportViewer1.ReportSource = objRpt;
            con.close();
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error ! \n" + ex.ToString());
        }
    }

Upvotes: 1

Views: 4570

Answers (1)

Ally
Ally

Reputation: 1922

I'm going to assume a few things:

  1. That your project is using Visual Studio 2010, .NET Framework 4, and Crystal Reports runtime for VS2010
  2. That your typed dataset ReportDataSet is setup using VS's Dataset Designer via a connection to your database file SMS_DB.mdf and that there is data already in this file
  3. That your typed Crystal Report rt1 is setup within the Report Designer with the datasource being the table InvoiceItemData from the typed dataset ReportDataSet in your project
  4. All of these controls are on the same form
  5. You're still debugging this on your machine where .NET Framework 4 and Crystal Reports runtime for VS2010 are installed

In other words, I'm assuming the conditions in which you've set everything up already, you've done everything up to the btnReportGen button click within the latest, non-beta environment, and that you're not trying to run this as a Release on another machine.

Now, I have some suggestions and clarifications to make to you.

  1. You're not using the typed dataset as the report datasource in this case: the dataset is your dataserve and the datasource is your typed datatable, which is why you're correctly sending the report the updated datatable in your code.
  2. Use separate winforms for your picker controls and report viewer, and call your report viewer form from your picker control form when you hit the generate button. Otherwise you're going to run into the issue of sending bad data to the report viewer, which is going to generate an incorrect report or none at all.
  3. Use parameters in your report document and send the picker control values to the report document so that you can display them on the report so that your user knows they are at least sending the right values when they hit the generate button.
  4. Check that your control values are valid before sending them to the viewer form and using them in a query. Some things to check: Are they both set? Are they both dates? Is the To value greater than or equal to the From value? Is the range valid for your database? (This also gives you the option of showing a more helpful error message to the user and being able to send them back to the right control so they can fix it.)
  5. Since you're already using the typed dataset and dataset designer, use the provided typed table adapter and create a custom query with parameters within the designer and then use that query to fill your table. This is going to save you so many headaches that are caused by trying to do everything programmatically. You have the tools available, so use them.

If you consider all of the above, I think you'll find yourself with a correctly working project. However, if you are still stuck, I can go through each suggestion and hand-hold you through fixing everything with screenshots and example code. It's okay if you need me to do that because I've created a huge variety of Crystal Report projects in VS2010 and I'm just telling you, from experience, what works best for you as the programmer and for the user. You might think you're saving time and resources by just typing everything up programmatically and throwing it at the report viewer, but that's also the reason why it's not cooperating.

Upvotes: 1

Related Questions