Filter datatable c#

Question

Filter datatable from text box and datetime picker

private void izvjestajoprodanimartiklima()
{
using (SqlConnection openCon = new SqlConnection(cs))
{
String saveStaff = "SELECT roba as 'Naziv artikla', SUM (cast(kolicina as float)) as 'Kolicina' from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables group by roba";
if (!string.IsNullOrEmpty(textBox1.Text))
{
saveStaff += "roba LIKE '%" + textBox1.Text + "%' ";
}
if (checkBox1.Checked)
{
String strFilterDTP1 = dateTimePicker1.Value.ToString("yyyy-MM-dd");
String strFilterDTP2 = dateTimePicker2.Value.ToString("yyyy-MM-dd");
saveStaff += strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' ";
}

error line 14 Incorrect syntax near the keyword 'LIKE'.

Some help?

Rafnas T P · Answer

hi, you have to do group by after all the filters, change your code as below String saveStaff = "SELECT roba as 'Naziv artikla', SUM (cast(kolicina as float)) as 'Kolicina' from (select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables " ; if (! string .IsNullOrEmpty(textBox1.Text)) { saveStaff += " where roba LIKE '%" + textBox1.Text + "%' " ; } saveStaff+= " group by roba" ;

Faisal Pathan · Answer

Your sql query is wrong it should be SELECT roba as 'Naziv artikla' , SUM ( cast (kolicina as float )) as 'Kolicina' from ( select roba, kolicina from mp_racun_roba union all select roba, kolicina from mp_faktura_roba) as alltables where roba LIKE '%" + textBox1.Text + "%' " group by roba

Bhairab Dutt · Answer

Hi Goran, I think the updated sql query is correct and you can use its with string . Now after that you can add where condition for get the value with like and then use and for date filteration . Hope Its help you. Thanks.

Goran Bibic · Answer

Answer is corect...rest of code? Line 8 if (checkBox1.Checked) { String strFilterDTP1 = dateTimePicker1.Value.ToString( "yyyy-MM-dd" ); String strFilterDTP2 = dateTimePicker2.Value.ToString( "yyyy-MM-dd" ); saveStaff += strFilterDTP1 + " 00:00:00' AND '" + strFilterDTP2 + " 23:59:59' " ; }