BindingSource.Filter using Inner Join

Question

I am using BindingSource and I want to use some SQL code to perform an Inner Join. My code for this doesn't work

ticketsBindingSource.Filter = "SELECT u.CallerName, t.* FROM users u INNER JOIN tickets t ON u.id = t.user WHERE u.CallerName = 'joe.smith' AND t.ProblemStatus = 'Open'";

But the following does work

ticketsBindingSource.Filter = "ProblemStatus = 'Open'";

How can I run my InnerJoin query and update my datagridview?

Taryn · Accepted Answer · 2012-04-12T17:48:58.417

The BindingSource.Filter applies a Filter to the data that is already been loaded into your DataGridView. So if you have accounts displayed, and you are looking for one specific account, then you would filter that data by account number.

The Filter, cannot run another SQL query for you.

If you want to perform an INNER JOIN on that data then you will need to perform another search and load the DataGridView. It sounds like you do not want to perform a filter, but you want to display two separate sets on data in the same grid.

Your basic process would be:

Load you DataGridView
Use selects the Filter or records they want
Performs a second search with your INNER JOIN
reload your DataGridView with the new data.

EDIT here is some code that might get your started:

How to: Bind Data to the Windows Forms DataGridView Control

private void GetData(string selectCommand)
{
    try
    {
        // Specify a connection string. Replace the given value with a 
        // valid connection string for a Northwind SQL Server sample
        // database accessible to your system.
        String connectionString =
            "Integrated Security=SSPI;Persist Security Info=False;" +
            "Initial Catalog=Northwind;Data Source=localhost";

        // Create a new data adapter based on the specified query.
        dataAdapter = new SqlDataAdapter(selectCommand, connectionString);

        // Create a command builder to generate SQL update, insert, and
        // delete commands based on selectCommand. These are used to
        // update the database.
        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

        // Populate a new data table and bind it to the BindingSource.
        DataTable table = new DataTable();
        table.Locale = System.Globalization.CultureInfo.InvariantCulture;
        dataAdapter.Fill(table);
        bindingSource1.DataSource = table;

        // Resize the DataGridView columns to fit the newly loaded content.
        dataGridView1.AutoResizeColumns( 
            DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
    }
    catch (SqlException)
    {
        MessageBox.Show("To run this example, replace the value of the " +
            "connectionString variable with a connection string that is " +
            "valid for your system.");
    }
}

private void Button1_Click(object sender, EventArgs e)
{
    // Bind the DataGridView to the BindingSource
    // and load the data from the database.
    dataGridView1.DataSource = bindingSource1;
    GetData("select * from Customers");
}

Once the data is bound to your grid, then you would provide the user some way to Filter, but you want them to search the data again. So you will want to use in the code some way to select which SQL you will run. So you could pass the SQL into GetData() as a string that you have set based on the users selection

private void Button1_Click(object sender, EventArgs e)
{
    // Bind the DataGridView to the BindingSource
    // and load the data from the database.
    dataGridView1.DataSource = bindingSource1;

    string sqlQuery = string.Empty;

    if(your check goes here)
    {
        sqlQuery = "select * from Customers";
    }
    else 
    {
        sqlQuery = "your new SQL";
    }

    GetData(sqlQuery);
}

then you would rebind your data based on the new query.

Yes but I am confused. Before I used the visual data binder in VS10 to create a dataset, bindingsource and used the visual tools to configure my columns, and it's headers. so with your method, how do I reload my new data and perform the Inner Join query? — Cocoa Dev, Apr 12 '12 at 17:14
do you have code to bind the data to your datagrid? If not, then I will find an example for you. — Taryn, Apr 12 '12 at 17:19
I have 0 lines of code so far. Everything was done Visually. But can both ways exist at the same time? — Cocoa Dev, Apr 12 '12 at 17:34
One last thing. How do I change the connection string to point to the accdb (Access 2007) file in the project folder? — Cocoa Dev, Apr 12 '12 at 18:01
check out this site: http://alperguc.blogspot.com/2008/12/c-ms-access-connection-string-ole-db.html or just search c# and ms access connection strings — Taryn, Apr 12 '12 at 18:10
yes I know the ConnectionString for access. How do I tell it to use the one in the Project File? — Cocoa Dev, Apr 12 '12 at 18:23
String connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\BC207\\test.accdb"; — Cocoa Dev, Apr 12 '12 at 18:28
I don't know offhand how to tell it to use the one in the project file. — Taryn, Apr 12 '12 at 18:55

score 1 · Answer 2 · answered Apr 12 '12 at 15:25

1

Change your data source.

create a view with the join.

Use that view as your data source for data binding.

and use the filter if required.

Remember the filter becomes part of the where clause.

answered Apr 12 '12 at 15:25

Deb

981
13
39

BindingSource.Filter using Inner Join

2 Answers2