Filtering database data

Konstantin Pankarican

Hello,
I am working on a project where I have check box list and a datagridview. I populate datagridview from database. Now, what I need is to populate datagridview from database depending on checked items in check box list.
I am working in VS 2010, sqlCEserver, .net 3.5
I was wondering is there a way to do this:

SELECT * FROM articles WHERE name LIKE ?

I think this will solve my problem.
Please help.

13 Replies

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

Konstantin Pankarican13y ago
Hello sir, the search will be against all items which are shown in listview. Items and subitems. Regards.
Rosi Sreenivasa Reddy13y ago
Hai Iftikar Hussain,

can you explain clearly for me also.what to do and how the flow was going on,
In both front End and Back End.

by using asp.net web Application 3.5 and Sql Server 2008 r2
Iftikar Hussain13y ago
you want to search against main item or sub-item?

ListViewItem item = new ListViewItem(dr["sifra"].ToString());
item.SubItems.Add(dr["ime"].ToString());

Regards,
Iftikar
Konstantin Pankarican13y ago
Hello sir,
excuse me it's my mistake. It's not checkedlistbox. It's listview with checkboxes enabled in properties. here's the code:

try
            {
                cn.Open();

                SqlCeCommand cm = new SqlCeCommand("SELECT * FROM tbl_employees WHERE oblik='Pravno lice'", cn);

                SqlCeDataReader dr = cm.ExecuteReader();

                while (dr.Read())
                {
                    ListViewItem item = new ListViewItem(dr["sifra"].ToString());
                    item.SubItems.Add(dr["ime"].ToString());


                    listView1.Items.Add(item);
                }
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.Message, "Error");
                Application.ExitThread();
            }

Once again, I'm sorry for mistake.
Regards.
Konstantin Pankarican13y ago
Ok. I will populate in 2 hrs. I am at work now. Regards.
Iftikar Hussain13y ago
can you share me the code how you have populate the checkboxlist?

Regards,
Iftikar
Rosi Sreenivasa Reddy13y ago
Hai,

     if you r interested i think by using subquery

    select * from articles where name=(select name from articles where checked='y')

   i think it will execute can try it

if it is not possible we can go stored procedure?
Konstantin Pankarican13y ago
Hello, I hope that this is the right answer. I will try it today and let you know. If have any trouble I will sent you my entire code. Thanks. Best regards sir.
Iftikar Hussain13y ago
you need to create dynamic sql query and the execute the query

private void checkedListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string sql = "SELECT * FROM articles ";
if (checkedListBox1.CheckedItems.Count > 0)
{
sql = sql + "WHERE ( ";
}
StringBuilder query = new StringBuilder();
foreach (object itemChecked in checkedListBox1.CheckedItems)
{
DataRowView castedItem = itemChecked as DataRowView;
string name = castedItem["Name"].ToString();
query.AppendFormat("or name like '%{0}%' ", name);
}
if (checkedListBox1.CheckedItems.Count > 0)
{
query.Remove(0, 2);
query.Append(" )");
}
sql = sql + query.ToString();
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(@"Data Source=SQLEXPRESS;Initial Catalog=MyDb;Persist Security Info=True;User ID=MyUser;Password=MyPassword"))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter dap = new SqlDataAdapter(cmd);
dap.Fill(dt);
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
dataGridView1.DataSource = dt;
}
}

Regards,
Iftikar
Konstantin Pankarican13y ago
Hello, no. On selection change in checklistbox display data in datagridview depending on checklistbox checked items. Thanks.
Iftikar Hussain13y ago
have you used checkedlistbox?

Regards,
Iftikar
Konstantin Pankarican13y ago
Hello. In windows form. Thanks.
Iftikar Hussain13y ago
Hi,
Are you looking for in ASP.NET or Windows Form?

Regards,
Iftikar