Filter datatable c#

Goran Bibic

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
Accepted 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";
Bhairab Dutt8y ago
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 Bibic8y ago
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' ";

              }
Faisal Pathan8y ago
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