Changing DataBoundGrid Query

[Steven]

I have a GridDataBoundGrid that I have bound to a database using the following code:

System.Data.OleDb.OleDbDataAdapter da1 = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Products", oleDbConnection1.Connection);

this.dataSet11.Clear();
da1.Fill(this.dataSet11, "New");
this.gridDataBoundGrid1.DataMember = "New";

I now want to change the DataAdapter query so that it returns a different set of columns. When I do this the columns stay the same as before but only the columns specified in my query are populated with data.

If I specify a new srcTable (New1 instead of New) I get the proper behaviour (only the specified columns are displayed).

How do I get the grid to redraw showing only the specified columns without generating a new srcTable?

Thanks
Steven

[Administrator]

Try this.

Before getting the new contents, set

grid.BeginUpdate();
grid.DataMember = "";
grid.DataSource = null;
//get your new contents
grid.DataMember = "New";
grid.DataSource = ds;
grid.Binder.InitializeColumns();//may not need
grid.EndUpdate();
grid.Refresh();

[Steven]

This did not work.

I made a test case that has one column in the dataset. It is displayed correctly. I press a button and I change the query so that the dataset has two columns. I can look at my dataset and see that it has changed to two columns. The GridDataBoundGrid still only shows one column. InitializeColumns made no difference.

>Try this.
>

[Administrator]

Can you upload the sample project you did so we can see exactly what you are doing?

If your dataset is generated at design time, then there are public properties added for each table and each column in the table. If you then just do a DataSet.Clear, this will not affect these properties added at design time. So, if you want to dynamically change the content of the displayed table, then you may not want to use the designer to set up the DataSet.

[Steven]

>Can you upload the sample project you did so we can see exactly what you are doing?
>
>If your dataset is generated at design time, then there are public properties added for each table and each column in the table. If you then just do a DataSet.Clear, this will not affect these properties added at design time. So, if you want to dynamically change the content of the displayed table, then you may not want to use the designer to set up the DataSet.

[Steven]

Here is the project. The db stuff was created at designtime. Just run it and press the third button from the top. You will get 1 column. Press the 4th button from the top and you should get 2 columns. Only one column shows. Thanks > > >>Can you upload the sample project you did so we can see exactly what you are doing? >> >>If your dataset is generated at design time, then there are public properties added for each table and each column in the table. If you then just do a DataSet.Clear, this will not affect these properties added at design time. So, if you want to dynamically change the content of the displayed table, then you may not want to use the designer to set up the DataSet. EMMManager_6079.zip

[Administrator]

You can get this to work by just working directly with the Datatables involved and not use the designer generated dataset direct. So, instead of

grid.DataSource = dataset1;
grid.DataMember = "New";

use

grid.DataSource = daatset1.Tables["New"];

Here are your button handler back with code that worked for me.

    private void button2_Click(object sender, System.EventArgs e)
    {
      Console.WriteLine("----");
      //System.Data.OleDb.OleDbDataAdapter da1 = new System.Data.OleDb.OleDbDataAdapter("SELECT ProductName, ProductID, QuantityPerUnit FROM Products WHERE UnitPrice < 10", oleDbConnection1.Connection);
      System.Data.OleDb.OleDbDataAdapter da1 = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM Products", oleDbConnection1.Connection);

      this.dataSet11.Clear();
      da1.Fill(this.dataSet11, "New");
		
	this.gridDataBoundGrid1.DataSource = null;
		this.gridDataBoundGrid1.DataMember = "";
      this.gridDataBoundGrid1.DataSource = this.dataSet11.Tables["New"];
    }

    private void menuItem2_Click(object sender, System.EventArgs e)
    {
      this.Close();
    }

    private void menuItem3_Click(object sender, System.EventArgs e)
    {
      // set up the open file dialog
      openFileDialog1.Filter = "Extreme Movie Manager Files|*.mdb";
      openFileDialog1.Title = "Select an Extreme Movie Manager Database File";

      if(openFileDialog1.ShowDialog() == DialogResult.OK)
      {
        Console.WriteLine("opening " + openFileDialog1.FileName);
      }

    }

    private void button3_Click(object sender, System.EventArgs e)
    {
      Console.WriteLine("----");
      //System.Data.OleDb.OleDbDataAdapter da1 = new System.Data.OleDb.OleDbDataAdapter("SELECT ProductName FROM Products", oleDbConnection1.Connection);
      this.oleDbDataAdapter1.SelectCommand.CommandText = "SELECT ProductName FROM Products";

      gridDataBoundGrid1.BeginUpdate();
      gridDataBoundGrid1.DataMember = "";
      gridDataBoundGrid1.DataSource = null;

      if (this.dataSet11.Tables.Contains("New"))
      {
        this.dataSet11.Tables.Remove("New");
        this.dataSet11.Tables.Add("New");
      }

      this.oleDbDataAdapter1.Adapter.Fill(this.dataSet11, "New");

      foreach(DataTable dataTable in dataSet11.Tables)
      {
        Console.WriteLine(dataTable.TableName + " " + dataTable.Rows.Count + " " + dataTable.Columns.Count);
      }

      gridDataBoundGrid1.DataSource = this.dataSet11.Tables["New"];
     // gridDataBoundGrid1.Binder.InitializeColumns();//may not need
      gridDataBoundGrid1.EndUpdate();
      gridDataBoundGrid1.Refresh();
    }

    private void button5_Click(object sender, System.EventArgs e)
    {
      Console.WriteLine("----");
      //System.Data.OleDb.OleDbDataAdapter da1 = new System.Data.OleDb.OleDbDataAdapter("SELECT ProductName FROM Products", oleDbConnection1.Connection);
      this.oleDbDataAdapter1.SelectCommand.CommandText = "SELECT ProductID, ProductName FROM Products";

      gridDataBoundGrid1.BeginUpdate();
      gridDataBoundGrid1.DataMember = "";
      gridDataBoundGrid1.DataSource = null;

      if (this.dataSet11.Tables.Contains("New"))
      {
        this.dataSet11.Tables.Remove("New");
        this.dataSet11.Tables.Add("New");
      }

      this.oleDbDataAdapter1.Adapter.Fill(this.dataSet11, "New");

      foreach(DataTable dataTable in dataSet11.Tables)
      {
        Console.WriteLine(dataTable.TableName + " " + dataTable.Rows.Count + " " + dataTable.Columns.Count);
      }

      gridDataBoundGrid1.DataSource = this.dataSet11.Tables["New"];
      gridDataBoundGrid1.EndUpdate();
      gridDataBoundGrid1.Refresh();
      gridDataBoundGrid1.Update();
    }

[Steven]

Can you give me a brief reason why working with the designer dataset and tables is bad. An answer like "designer generated stuff stinks" is a good (and actually expected) answer.

Thank you very much.

>You can get this to work by just working directly with the Datatables involved and not use the designer generated dataset direct.

[Administrator]

It is not that it is bad, but using the designer generates a strongly typed dataset does not lend itself to a dataset that you want to dynamcially change. Look at all the properties in DataSet.cs it added specifically to reflect what you requested at design time. None of this is available for the "New" datatable. But just pointing the grid directly to the DataTable avoids the dataset and allows the grid to work directly with the datatables.

[Steven]

Great answer. Thanks

>It is not that it is bad, but using the designer generates a strongly typed dataset does not lend itself to a dataset that you want to dynamcially change. Look at all the properties in DataSet.cs it added specifically to reflect what you requested at design time. None of this is available for the "New" datatable. But just pointing the grid directly to the DataTable avoids the dataset and allows the grid to work directly with the datatables.