我想使用listbox中的selectedvalue来过滤datagridview [英] I want to use selectedvalue from listbox to filter datagridview

问题描述

我正在尝试从我的listBox传递SelectedValue作为我的DataGridView的过滤器。两个对象都使用我的Azure SQL数据库来获取其数据。

Hi,

I'm trying to pass the SelectedValue from my listBox to be used as a filter for my DataGridView. Both objects use my Azure SQL database to get their data.

namespace myNameSpace
{
    public partial class CompanyForm : Form
    {
        public CompanyForm()
        {
            InitializeComponent();
            Load += new EventHandler(dataEmployees_Load);
            Load += new EventHandler(companyList_Load);
        }
        //Connection String
        string cs = ConfigurationManager.ConnectionStrings["MyCS"].ConnectionString;
        
        // Load all companies
        private void companyList_Load(object sender, EventArgs e)
        {
            SqlConnection con;
            SqlCommand cmd;
            SqlDataAdapter adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            string sql = "Select companyName from dbo.Company";
            con = new SqlConnection(cs);
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);
                adapter.Dispose();
                cmd.Dispose();
                con.Close();
                companyList.DataSource = ds.Tables[0];
                companyList.ValueMember = "companyName";

            }
            catch (Exception ex)
            {
                MessageBox.Show("There are no companies to display");
            }
        }

上面的代码是从我的SQL数据库生成列表框的代码

The code above is the code to generate the listbox from my SQL Database

// Load all employees
private void dataEmployees_Load(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(cs))
    {
        con.Open();
        SqlCommand cmd = new SqlCommand
        (
            "Select fname,ename
             FROM dbo.Users u
             inner join dbo.Company c on c.companyName = u.company", con
        ); //I would like to add to the select clause the variable "selectedValue" that can work in my conditional logic to filter the data in the datagrid
        SqlDataReader dr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(dr);
        dataEmployees.DataSource = dt;
    }
}

因此，用户创建了一个有员工的公司，然后想要选择一家公司，只在DataGridView中获得该公司雇用的公司



我尝试过：



我尝试了一些不同的方法，尝试将其放入变量，但数据不会被加载到列表框或DataGridView中。

So a user creates a company with employees, then wants to select a company and only get the ones employed in that particular company in the DataGridView

What I have tried:

I have tried some different approaches, with trying to put it into a variable but then the data does not get loaded into the listbox or the DataGridView.

推荐答案

一个简单的方法可能如下：



- 无条件地构建SQL语句

- 如果公司被选中修改SQL以包含WHERE子句并添加参数

- 完成命令并执行

A simple way could be the following:

- Build the SQL statement without conditions
- If company is selected modify the SQL to contain the WHERE clause and add the parameter
- complete the command and execute

SqlCommand cmd = new SqlCommand(); 
cmd.Connection = con;
string sql  = @"Select fname,ename 
                FROM dbo.Users u 
                inner join dbo.Company c on c.companyName = u.company"
if (companyList.SelectedIndex > -1) {
   sql = sql + " WHERE c.companyName = @companyname";
   cmd.Parameters.AddWithvalue("@companyname", companyList.SelectedItem);
}
cmd.CommandText = sql;
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
...

这篇关于我想使用listbox中的selectedvalue来过滤datagridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！