How can I search a listbox items depending on a variable?-CodePudding

I am new to c# and trying to search a listbox as following : First i have this :

 public partial class FrmCodes : Form
 {
        ...
        SqlConnection Cn = new SqlConnection(@"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True");
        SqlCommand cmd;
        SqlDataReader DataRead;
        ...

    public FrmCodes()
    {
        InitializeComponent();
        cmd = new SqlCommand("Select Item from Items", Cn);
        Cn.Open();
        DataRead = cmd.ExecuteReader();

        while (DataRead.Read())
        {
            ListItems.Items.Add(DataRead["Item"].ToString());
        }
        DataRead.Close();
        Cn.Close();
    }

And tried to do this :

private void txtSrch_TextChanged(object sender, EventArgs e)
{
    ListItems.Items.Clear();
    while (DataRead.Read())
    {
        string str = DataRead["Item"].ToString();
        string srch = txtSrch.Text;
        if (str.Contains(srch))
        {
            ListItems.Items.Add(str);
        }
    }
}

It did not work , I tried to make a new sql select query that get data depending on txtSrch.Text but got nothing either . Thanks in advance.

Edit#1 This is the query i mentioned before :

private void txtSrch_TextChanged(object sender, EventArgs e)
{
    ListItems.Items.Clear();

    SqlConnection Cn2 = new SqlConnection(@"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True");
    Cn2.Open();

    string srch = txtSrch.Text;
    using (SqlDataAdapter a2 = new SqlDataAdapter("Select Item from Items WHERE Item LIKE '%"   srch   "%'", Cn2))
    {
        var t2 = new DataTable();
        a2.Fill(t2);

        ListItems.DisplayMember = "Item";
        ListItems.ValueMember = "Code";
        ListItems.DataSource = t2;
    }
}

This did not affect the items in the listbox nothing happens on txtSrch Change .

CodePudding user response：

Another solution using Dataview thanks to @Trevor

public partial class FrmCodes : Form
         
    {
        SqlConnection Cn = new SqlConnection(@"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True");
        SqlDataAdapter da;
        DataTable dt = new DataTable();
        
        
         

        public FrmCodes()
        {
            InitializeComponent();
            

            da = new SqlDataAdapter("Select Item from Items", Cn);
            da.Fill(dt);
            DataView dv = new DataView(dt);
            ListItems.DataSource = dv;
            ListItems.DisplayMember = "Item";
                      
          
        }

Textbox change :

private void txtSrch_TextChanged(object sender, EventArgs e)
        {

            ListItems.DataSource = null;
            DataView dv = new DataView(dt);
            string srch = txtSrch.Text;
            dv.RowFilter = string.Format("Item Like '%{0}%'", srch);
            ListItems.DataSource = dv;
            ListItems.DisplayMember = "Item";
                
        }

Thanks.

CodePudding user response：

Basedon @Olivier Jacot-Descombes’ comments, this worked for me:

private void txtSrch_TextChanged(object sender, EventArgs e)
{
    ListItems.DataSource = null;

    SqlConnection Cn2 = new SqlConnection(@"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True");
    Cn2.Open();

    string srch = txtSrch.Text;
    using (SqlDataAdapter a2 = new SqlDataAdapter("Select Item from Items WHERE Item LIKE '%"   srch   "%'", Cn2))
    {
        var t2 = new DataTable();
        a2.Fill(t2);

        ListItems.DisplayMember = "Item";
        ListItems.ValueMember = "Code";
        ListItems.DataSource = t2;
    }
}