can anyone help..plz

i have a form called transaction view.in this form there is two datetime picker.dtpstart and dtpEnddate.i want to pass these two values to report such that the report will display transactions occured in the range specified by th e datetime picker


there are 3 tables in my database

Custinfo table - custid(primarykey),name, .....

Spainfo table - spaid(primary key)name,description,price...

spaindate tabele - custid(foreign key references custinfo.custid),spaid(foregn key references spainfo.spaid),Date ...


i want when the user specifies the range ie date range for the transcations and when he press vierptbtn : a report of the transactions to be dsiplayed

i have done this in a datagrid in the form but for printing purposes i want it to have a report also

this is how i did it in the datagrid

connec.ConnectionString = dbConnec.ConnString;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            try
            {


                string str = "SELECT CI.CUSTID,CI.NAME,SP.SPAID,SP.NAME,SP.DESCRIPTION,SP.DURATION,
SP.PRICE,T.DATE,T.TIMEIN,T.TIMEOUT FROM CUSTINFO CI,SPAINFO SP,SPAINDATE T WHERE CI.CUSTID = T.CUSTID AND SP.SPAID = T.SPAID AND T.DATE BETWEEN @dateIN AND @dateOUT";
                cmd1 = new SqlCommand(str, connec);
                connec.Open();
                cmd1.CommandType = CommandType.Text;
                cmd1.Parameters.AddWithValue("@dateIN", dtpStartDate.Text.Trim());
                cmd1.Parameters.AddWithValue("@dateOUT", dtpEndDate.Text.Trim());
                
                da.SelectCommand = cmd1;
                
                da.Fill(ds, "TRANSVIEW");
                dgSpaTransView.DataSource = ds.Tables["TRANSVIEW"];
                dgSpaTransView.Columns[0].HeaderText = "CustomerID";
                dgSpaTransView.Columns[1].HeaderText = "CustomerName";
                dgSpaTransView.Columns[2].HeaderText = "ServiceID";
                dgSpaTransView.Columns[3].HeaderText = "ServiceName";
                dgSpaTransView.Columns[4].HeaderText = "ServiceDescription";
                dgSpaTransView.Columns[5].HeaderText = "ServiceDuration";
                dgSpaTransView.Columns[6].HeaderText = "Price(Rs.)";
                dgSpaTransView.Columns[7].HeaderText = "Date";
                dgSpaTransView.Columns[8].HeaderText = "TimeIN";
                dgSpaTransView.Columns[9].HeaderText = "TimeOUT";
                  if (ds.Tables["TRANSVIEW"].Rows.Count == 0)
                {
                    MessageBox.Show("No Records Found", "No Records", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.label2.Text = "";
                    this.textBox1.Text = "";
                }
                else
                {
                    string str2 = "select Sum(SP.PRICE)as PRICE From  SPAINFO SP,SPAINDATE T WHERE SP.SPAID = T.SPAID AND T.DATE BETWEEN @dateIN AND @dateOUT";
                    cmd2 = new SqlCommand(str2, connec);

                    cmd2.CommandType = CommandType.Text;
                    cmd2.Parameters.AddWithValue("@dateIN", dtpStartDate.Text.Trim());
                    cmd2.Parameters.AddWithValue("@dateOUT", dtpEndDate.Text.Trim());

                    da.SelectCommand = cmd2;

                    da.Fill(ds, "INCOME");
                    


                    this.textBox1.DataBindings.Add("Text", ds, "INCOME.PRICE");
                    string dt1 = dtpStartDate.Text;
                    label2.ForeColor = Color.Maroon;
                    this.label2.Text = "Income for spa services in the period " + dtpStartDate.Text + " To " +dtpEndDate.Text + " is (Rs.)";
                }
            }
            catch (System.Exception)
            {
                MessageBox.Show("OOps i am having hiccups", "HICCUPs", MessageBoxButtons.OK, MessageBoxIcon.Information);
  
            }
            finally
            {
                
                connec.Close();
                this.textBox1.DataBindings.Clear();             
                

            }

it works fine for the datagrid ...i donno how to do with the crystal report.if sumone can lend a help ill be grtfull..i gusss we have to pass parameters for the report..if anyone can provide a sample demo project it wil be very helpful.....thank u

Recommended Answers

All 3 Replies

I think you would start by setting up a parameter field in crystal reports for your the date field in your table. Then you would have a date on your report where you would use the select expert (when you left click on the field this will be an option) to set up the date range selection.
Good luck

You can try the following link , it shows the basics of sending date paramter to Crystal Reports in C#.

http://csharp.net-informations.com/crystal-reports/csharp-crystal-reports-date-parameter.htm

Follow the link yang gave you above. I would:
1) create two date parameter fields for the begin and end dates, then
2) use the Record Selection Formula Editor to create a "Comparisons" selection criteria based on the DB date Field and the two params from step 1, then
3) set the date parameters from your c# code at runtime:

public static void SetParameterDescreteValue(ReportDocument crDoc, string paramFieldName, object discreteValue)
            {
                // Retrieve the parameber field we are setting...
                ParameterFieldDefinition crParamField = crDoc.DataDefinition.ParameterFields[paramFieldName];

                // Create an object type crystal understands to pass in the value...
                ParameterDiscreteValue crParamDiscreteValue = new ParameterDiscreteValue();
                crParamDiscreteValue.Value = discreteValue;

                // create a new collection of parameter values and add value passed in...
                ParameterValues crParamValues = new ParameterValues();
                crParamValues.Add(crParamDiscreteValue);

                // finally, set the parameter's new value collection...
                crParamField.ApplyCurrentValues(crParamValues);
            }

The code in the link yang gave will do the job, but I compacted what I needed into the method above, which you can call once each for begin and end dates. I have not tested it with anything but string objects passed in, but I think it should be fine with dates too.

Cheers!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.