Binding source not updating when linked table changes in code

[nortech]

Hello All,

I am currently in the process of adapting an application that previously worked only with Microsoft Access to work with SQL server or Access. Previously, all database interaction was designed based upon using the XSD file to create queries (etc).

I wasn't sure how to handle the new situation with two types of database using the visual methods (e.g. did not want two separate datasets (etc) so I created a static class that contains a function as follows:

public static DataTable SelectRows(string connectionString, string queryString)

The function will connect to either type of DB based on the connection string and will run the query held in queryString returning a table of the selected records.

I create a binding source and assign it to a table called masterTable as follows:

cards_photo_accesslevelBindingSource.DataSource = masterTable;

I then have a DataGridView that points to the binding source as follows:
dgNavigatorTable.DataSource = cards_photo_accesslevelBindingSource;

At various times in my code, I call the SelectRows method and store the returned table over the top of the masterTable as follows:

masterTable = MyQuery.SelectRows(MyConnectionStrings.getConnectionString(),
"SELECT * FROM (Cards LEFT JOIN Photo ON Cards.Card = Photo.Id)LEFT JOIN AccLevel ON Cards.AccessLevel = AccLevel.AccessLevel WHERE Cards.FirstName LIKE '" + tbFirstName.Text + "%'");

My first concern is that the binding source does not update in relation to the update of the table.

I have found that the only way to get the binding source and associated DataGridView to update after my masterTable has changed is to re-assign it as follows:

// This seems necessary to update the navigator linked to the binding source
cards_photo_accesslevelBindingSource.DataSource = masterTable;

Even using this naive technique, I also need at this point the PositionChanged event to fire on the binding source as it used to when I would call a Fill method on the datasource before I replaced this method with my SelectRows method.

I haven't done much C# for a while and previously have always used an XSD file for my database connection and queries and therefore I assume that I am using the binding source and DataTable in an illegal and naive way? I will keep searching the web for an answer and apologise in advance for my lack of understanding.

Regards,

Chris

[nick.crane]

Your problem comes from your re-assigning masterTable to a new DataTable instance.
Your binding source is assigned to the previous instance of masterTable and so does not update.

Try this:
Before doing the select rows, use SuspendBinding on the binding source.
Then clear masterTable and Merge the new SelectRows DataTable with masterTable.
Then use ResumeBinding on the binding source.
You may still need to Refresh the DataGridView (not sure, see what happens).

[nortech]

Your problem comes from your re-assigning masterTable to a new DataTable instance.
Your binding source is assigned to the previous instance of masterTable and so does not update.

Try this:
Before doing the select rows, use SuspendBinding on the binding source.
Then clear masterTable and Merge the new SelectRows DataTable with masterTable.
Then use ResumeBinding on the binding source.
You may still need to Refresh the DataGridView (not sure, see what happens).

Thanks for your input Nick!

I haven't tried your suggestion yet but I did try passing my masterTable by reference to the SelectRows function. After I call the SelectRows, my masterTable variable does get changed correctly but again the binding source does not update my DataGridView. I understood your explanation above and as a result was sure passing by reference would solve this problem.

I'll keep trying!

Thanks again!

[nortech]

Thanks for your input Nick!

I haven't tried your suggestion yet but I did try passing my masterTable by reference to the SelectRows function. After I call the SelectRows, my masterTable variable does get changed correctly but again the binding source does not update my DataGridView. I understood your explanation above and as a result was sure passing by reference would solve this problem.

I'll keep trying!

Thanks again!

Update on what I wrote above:

I've just realised that they may be another problem that causes a separate instance of the table to be created within my SelectRows method essentially rubbishing my last post. I will try to merge tables next.

Sorry for the incorrect information!