OleDbCommand cmd = new OleDbCommand("select * from Salary where (GPFCPF=? AND NameOfUnit=? AND GPFcategory=? AND PoNo1 <> ? OR PoNo2 <> ? OR PoNo3 <> ? OR PoNo4 <> ? OR PoNo5 <> ? OR PoNo6 <> ? OR PoNo7 <> ? OR PoNo8 <> ? ) AND (SDATE BETWEEN @date1 and @date2) ORDER BY SlipNo ASC", con);
using (OleDbConnection con = new OleDbConnection(ConfigurationManager.AppSettings["connection"])) { con.Open(); crystalReportViewer1.Refresh(); CrystalReportLIC billbyday = new CrystalReportLIC(); OleDbCommand cmd = new OleDbCommand("select * from Salary where GPFCPF=? AND NameOfUnit=? AND GPFcategory=? AND SDATE BETWEEN @date1 and @date2 AND PoNo1 <> ? OR PoNo2 <> ? OR PoNo3 <> ? OR PoNo4 <> ? OR PoNo5 <> ? OR PoNo6 <> ? OR PoNo7 <> ? OR PoNo8 <> ? ORDER BY SlipNo ASC", con); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = cmd; cmd.Parameters.Add(new OleDbParameter("@GPFCPF", comboBox1.Text)); cmd.Parameters.Add(new OleDbParameter("@NameOfUnit", comboBox3.Text)); cmd.Parameters.Add(new OleDbParameter("@GPFcategory", comboBox4.Text)); cmd.Parameters.Add(new OleDbParameter("@p1", "")); cmd.Parameters.Add(new OleDbParameter("@p2", "")); cmd.Parameters.Add(new OleDbParameter("@p3", "")); cmd.Parameters.Add(new OleDbParameter("@p4", "")); cmd.Parameters.Add(new OleDbParameter("@p5", "")); cmd.Parameters.Add(new OleDbParameter("@p6", "")); cmd.Parameters.Add(new OleDbParameter("@p7", "")); cmd.Parameters.Add(new OleDbParameter("@p8", "")); cmd.Parameters.Add(new OleDbParameter("@date1", Convert.ToDateTime(dateTimePicker1.Text))); cmd.Parameters.Add(new OleDbParameter("@date2", Convert.ToDateTime(dateTimePicker2.Text))); DataSetSalary ds = new DataSetSalary(); da.Fill(ds, "Salary"); billbyday.SetDataSource(ds); crystalReportViewer1.ReportSource = billbyday; con.Close(); }
using (OleDbConnection con = new OleDbConnection(ConfigurationManager.AppSettings["connection"])) { con.Open(); crystalReportViewer1.Refresh(); CrystalReportLIC billbyday = new CrystalReportLIC(); OleDbCommand cmd = new OleDbCommand("Select *, (select * from Salary where GPFCPF=? AND NameOfUnit=? AND GPFcategory=? AND PoNo1 <> ? OR PoNo2 <> ? OR PoNo3 <> ? OR PoNo4 <> ? OR PoNo5 <> ? OR PoNo6 <> ? OR PoNo7 <> ? OR PoNo8 <> ? ) as sub_salary form Salary where SDATE BETWEEN @date1 and @date2 ORDER BY SlipNo ASC", con); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = cmd; cmd.Parameters.Add(new OleDbParameter("@GPFCPF", comboBox1.Text)); cmd.Parameters.Add(new OleDbParameter("@NameOfUnit", comboBox3.Text)); cmd.Parameters.Add(new OleDbParameter("@GPFcategory", comboBox4.Text)); cmd.Parameters.Add(new OleDbParameter("@p1", "")); cmd.Parameters.Add(new OleDbParameter("@p2", "")); cmd.Parameters.Add(new OleDbParameter("@p3", "")); cmd.Parameters.Add(new OleDbParameter("@p4", "")); cmd.Parameters.Add(new OleDbParameter("@p5", "")); cmd.Parameters.Add(new OleDbParameter("@p6", "")); cmd.Parameters.Add(new OleDbParameter("@p7", "")); cmd.Parameters.Add(new OleDbParameter("@p8", "")); cmd.Parameters.Add(new OleDbParameter("@date1", Convert.ToDateTime(dateTimePicker1.Text))); cmd.Parameters.Add(new OleDbParameter("@date2", Convert.ToDateTime(dateTimePicker2.Text))); DataSetSalary ds = new DataSetSalary(); da.Fill(ds, "Salary"); billbyday.SetDataSource(ds); crystalReportViewer1.ReportSource = billbyday; con.Close(); }