C# C0246 While Filtering DataGridView with Listbox whose items come from SQL Server

Question

I share with you a piece of code that works except the part where I'm trying to loop in the items of my listbox. That's why I'm here asking you for some help. Lately, I switched from VBA to C# so I'm still new on this and don't undertsand everything yet. So, the below code connect to my SQL server DB and fetch data both within my listbox and a DataGridView. I can filter with two textboxes also. So now I have items within my listbox and my db's view within the DataGridview. I'd like to filter my DataGridview (which is filled by a datatable ) with my Listbox's item. I miss only a silly part I guess. Why Do I get this CS0246 "ListItem could not be found"

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsAppTest
{

public partial class Form1 : Form

{
   //Initialize the component and display the items within my listbox CS_Bonds_listBox
    public Form1()
    {
        InitializeComponent();
        string connetionString = @"Data Source=my_server;Initial Catalog=my_db;Integrated Security=SSPI";
        SqlConnection conn = new SqlConnection(connetionString);
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT DISTINCT RatingProvider FROM Bonds", conn);
        adapter.Fill(ds);
        this.CS_Bonds_listBox.DataSource = ds.Tables[0];
        this.CS_Bonds_listBox.DisplayMember = "RatingProvider";
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    DataTable dtTEST = new DataTable();

// Next, when clicking on my button Connect, I retrieve my db into a Datatable that is displayed within //the Datagridview1

    private void buttonConnect_Click(object sender, EventArgs e)
    {
        string connetionString = @"Data Source=my_server;Initial Catalog=my_db;Integrated Security=SSPI";
        SqlConnection cnn= new SqlConnection(connetionString);
        cnn.Open();
        MessageBox.Show("Connection Open  !");
        String sql = "Select * from Bonds";
        SqlCommand command = new SqlCommand(sql, cnn);
        SqlDataAdapter sqlDA = new SqlDataAdapter();
        sqlDA.SelectCommand = command;
        sqlDA.Fill(dtTEST);
        dataGridView1.DataSource = dtTEST;

        cnn.Close();
    }

    private void ISIN_Bonds_textBox_TextChanged(object sender, EventArgs e)
    {
        DataView dv = dtTEST.DefaultView;
        dv.RowFilter = "ISIN LIKE '" + ISIN_Bonds_textBox.Text + "%'";
        dataGridView1.DataSource = dv;
    }

    private void Ticker_Bonds_textBox_TextChanged(object sender, EventArgs e)
    {
        DataView dv1 = dtTEST.DefaultView;
        dv1.RowFilter = "Ticker LIKE '" + Ticker_Bonds_textBox.Text + "%'";
        dataGridView1.DataSource = dv1;
    }


    private void CS_Bonds_listBox_SelectedIndexChanged(object sender, EventArgs e)
    {
        string conString = @"Data Source=my_server;Initial Catalog=my_db;Integrated Security=SSPI";           
        string query = "SELECT ISIN, Ticker, CrediSight, FROM Bonds";
        string condition = string.Empty;

        foreach (ListItem item in CS_Bonds_listBox.Items)
        {
            condition += item.Selected ? string.Format("'{0}',", item.Value) : "";
        }

        if (!string.IsNullOrEmpty(condition))
        {
            condition = string.Format(" WHERE Country IN ({0})", condition.Substring(0, condition.Length - 1));
        }

        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(query + condition))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    cmd.Connection = con;
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        dataGridView1.DataSource = dt;
                        //dataGridView1.DataBind();
                    }
                }
            }
        }
    }
 
  }
}

Answer 1

This line has a problem:

foreach (ListItem item in CS_Bonds_listBox.Items)

A ListItem is a WebForms thing, and your application is a WinForms thing; your listbox doesn't contain a list of ListItem objects so this line of code wouldn't work out anyway, even if the relevant web namespace was imported.

Because you've bound your listbox to a datatable the list it is showing is full of DataRowView objects, so that's what you need to process. A DataRowView has a Row property that gives you the underlying row, which in turn can be accessed by a column name.

Additionally, to make your life easier a listbox has a SelectedItems property so you don't need to check every item for being selected:

foreach (DataRowView drv in CS_Bonds_listBox.SelectedItems)
{
  var dr = drv.Row as DataRow;
  var rp = dr["RatingProvider"]; 
  condition += $"'{rp}'," 
}

Your condition will end up with a trailing comma as a result of this, so trim it off before you build an IN clause with it:

condition = condition.TrimEnd(',');

---

This technique could be susceptible to SQL Injection hacking if the user manages to change the text showing in the list items.

A better way to handle the problem is via parameterization. You'd do it like this:

var cmd  = new SqlCommand("SELECT * FROM table WHERE Country IN(", connStr);

int i = 0;
foreach (DataRowView drv in CS_Bonds_listBox.SelectedItems)
{
  var dr = drv.Row as DataRow;
  var rp = dr["RatingProvider"]; 
  cmd.CommandText += $"@p{i},";
  cmd.Parameters.Add($"@p{i}", SqlDbType.VarChar).Value = rp;
  i++;
}

cmd.CommandText = cmd.CommandText.TrimEnd(',') + ")";
using(var da = new SqlDataAdapter(cmd))
{
  var dt = new DataTable();
  da.Fill(dt);
  someGridView.DataSource = dt;
}

This builds an sql that looks like SELECT * FROM table WHERE Country IN(@p0,@p1,@p2.... i.e. we have concatenated parameter placeholders in rather than concatenating values in. At the same time we have filled the parameters collection with the parameter values

It also means that our database can't be hacked via our program, and our app doesn't die in a heap when the user selects a country with a name like Cote d'Ivoire

---

Some other things to note to tidy your code up:

SqlDataAdapter can take a string SQL and a string connection-string. You don't need to make a SqlCommand for it. You don't need to open and close conenctions for it; it knows how to do all this itself. I only used a SqlCommand because I was building the parameters collection as I went. Ordinarily I'd do using(var da = SqlDataAdapter("SELECT...", "Server=..") because it makes things nice and tidy.

This means e.g. your constructor can be simply:

//put this here once
private string _connStr = @"Data Source=my_server;Initial Catalog=my_db;Integrated Security=SSPI";

public Form1()
{
    InitializeComponent();
    
    var dt = new DataTable();
    using(var da = new SqlDataAdapter("SELECT DISTINCT RatingProvider FROM Bonds", _connStr))
      adapter.Fill(dt);
    this.CS_Bonds_listBox.DataSource = dt;
    this.CS_Bonds_listBox.DisplayMember = "RatingProvider";
}