reflect changes in data bound control when underlying data tablechanges

[Ashutosh]

Hi,
I have a table (TableA) which is being used as a data source in for few
combo box in a data grid (for another table - TableB).

TableA & TableB, both are in the same data set. TableB is a usual table
(typed). However, Table A is somewhat different, the select query for
it's adapter is like

"select distinct dock from dock order by dock"

When the application initializes, the adapter fills the table A and the
values are displayed in the data grid of the combo box for a column of
Table B.

But after that if any row is added to TableA (dock) then the new value
is not reflected in the combo box in the datagrid view of Table B.

I tried to clear the table and re-fill the table/data-set using the
adapter, but it doesn't help. - At this point I can't commit the changes
to the database for the new row in table A using the Update command.

How can I make the changes reflect int the data bound combo box of the
data grid view.

Thanks & Regards,
Ashutosh

 

[Ashutosh]

[Little correction]

Hi,
I have a form on which I have 2 datagrid view.
1) for table "route" - Structure : route char(3), dock int
2) for table "lanes" - Structure: laneid char(3), laneName char(6),
lanetype char(2), dock1 int, dock2 int, dock3 int, dock4 int, dock5 int

The various docks in table lanes are actually values in the table
route's dock. But it's not a foreign key. Nor is the dock unique in the
route table. There can be multiple entry for a single value of dock.
Also database can be change under any circumstances.

For DataGridView of table lanes, I needed to display the available docks
in a combo box. So, I created another dummy table (dummy_docks) whose
select query is

"select distinct dock from route order by dock"

This dummy_docks table is used as data source for dock1, dock2....,
dock5 columns (combo box columns) of the data grid view of table lanes.

When the application initializes, the adapter fills the table
dummy_docks and the values are displayed in the combo box of the data
grid view of table lanes.

But after that if any row is added to table route then the new value of
dock is not reflected in the combo box in the data grid view of lanes.

I tried to update the changes in route table by calling update on the
data adapter and clear & re-fill the table dummy_docks using the
adapter, but it doesn't help.

How can I make the changes reflect in the data bound combo box of the
data grid view.

Thanks & Regards,
Ashutosh

 

[\Ji Zhou [MSFT]\]

Hello Ashutosh,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

Actually the comboBox's value list will update automatically if its
DataSource gets changed. In our scenario, we set the dump_docks as the
DataSource. Thus, in order to make the comboBox's value list update, we
firstly need to update the dump_docks table. As you described, this table's
data is filled from the route table by using the select statement "select
distinct dock from route order by dock". So, after we add a new row to the
table route, we need to the following things,

1.Call routeTableAdapter.Update() to commit the added row to the data base.

2.Call dummy_docks.Clear() function to clear all data in the dummy table

3.Use the select command "select distinct dock from route order by dock" to
fill the dummy_docks table again.

The following are codes work fine on my side, please let me know if this
works on your side.

private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'testDataSet.lanes'
table. You can move, or remove it, as needed.
this.lanesTableAdapter.Fill(this.testDataSet.lanes);
// TODO: This line of code loads data into the 'testDataSet.route'
table. You can move, or remove it, as needed.
this.routeTableAdapter.Fill(this.testDataSet.route);

this.routeTableAdapter.Adapter.SelectCommand = new
System.Data.SqlClient.SqlCommand("select distinct dock from route order by
dock",new
System.Data.SqlClient.SqlConnection(Properties.Settings.Default.TestConnecti
onString));
this.routeTableAdapter.Adapter.Fill(this.testDataSet.dummy_docks);

this.dataGridViewComboBox1.DataSource = this.testDataSet.dummy_docks;

this.dataGridViewComboBox1.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox1.DisplayMember = "dock";

this.dataGridViewComboBox2.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox2.DisplayMember = "dock";

this.dataGridViewComboBox3.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox3.DisplayMember = "dock";

this.dataGridViewComboBox4.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox4.DisplayMember = "dock";

this.dataGridViewComboBox5.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox5.DisplayMember = "dock";
}

private void button1_Click(object sender, EventArgs e)
{
this.testDataSet.route.Rows.Add(new object[] {"r7", 4 });
this.routeTableAdapter.Update(this.testDataSet.route);
this.testDataSet.dummy_docks.Clear();
this.routeTableAdapter.Adapter.SelectCommand = new
System.Data.SqlClient.SqlCommand("select distinct dock from route order by
dock", new
System.Data.SqlClient.SqlConnection(Properties.Settings.Default.TestConnecti
onString));
this.routeTableAdapter.Adapter.Fill(this.testDataSet.dummy_docks);
}

Have a nice day, Ashutosh!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Ashutosh]

Hi Ji,
Thanks for the solution. Actually I was also doing the same 3 things,
but I was doing it at a wrong place and at wrong time. Thanks a lot.

Secondly, is it possible to achieve this result without calling Update
on the route table? I want to give the user an option to save/reject the
changes.

Lastly, I want to find out a way in which if a dock number say 5 doesn't
exist anywhere in the route table, update the lane table and set the
dock to 0 for which the value was 5. There are two ways to do this.
1) By writing direct SQL command. But this will require to first update
the lane table, then update the dock and re-fill the lane table.
2) Traverse all the rows & columns in the lane table in the data set and
update the value.

But these are not efficient enough. Is there any better way to do this?

Thanks & Regards,
Ashutosh

Hello Ashutosh,

Thanks for using Microsoft Newsgroup Support Service, my name is Ji Zhou
[MSFT] and I will be working on this issue with you.

Actually the comboBox's value list will update automatically if its
DataSource gets changed. In our scenario, we set the dump_docks as the
DataSource. Thus, in order to make the comboBox's value list update, we
firstly need to update the dump_docks table. As you described, this table's
data is filled from the route table by using the select statement "select
distinct dock from route order by dock". So, after we add a new row to the
table route, we need to the following things,

1.Call routeTableAdapter.Update() to commit the added row to the data base.

2.Call dummy_docks.Clear() function to clear all data in the dummy table

3.Use the select command "select distinct dock from route order by dock" to
fill the dummy_docks table again.

The following are codes work fine on my side, please let me know if this
works on your side.

private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'testDataSet.lanes'
table. You can move, or remove it, as needed.
this.lanesTableAdapter.Fill(this.testDataSet.lanes);
// TODO: This line of code loads data into the 'testDataSet.route'
table. You can move, or remove it, as needed.
this.routeTableAdapter.Fill(this.testDataSet.route);

this.routeTableAdapter.Adapter.SelectCommand = new
System.Data.SqlClient.SqlCommand("select distinct dock from route order by
dock",new
System.Data.SqlClient.SqlConnection(Properties.Settings.Default.TestConnecti
onString));
this.routeTableAdapter.Adapter.Fill(this.testDataSet.dummy_docks);

this.dataGridViewComboBox1.DataSource = this.testDataSet.dummy_docks;

this.dataGridViewComboBox1.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox1.DisplayMember = "dock";

this.dataGridViewComboBox2.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox2.DisplayMember = "dock";

this.dataGridViewComboBox3.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox3.DisplayMember = "dock";

this.dataGridViewComboBox4.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox4.DisplayMember = "dock";

this.dataGridViewComboBox5.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox5.DisplayMember = "dock";
}

private void button1_Click(object sender, EventArgs e)
{
this.testDataSet.route.Rows.Add(new object[] {"r7", 4 });
this.routeTableAdapter.Update(this.testDataSet.route);
this.testDataSet.dummy_docks.Clear();
this.routeTableAdapter.Adapter.SelectCommand = new
System.Data.SqlClient.SqlCommand("select distinct dock from route order by
dock", new
System.Data.SqlClient.SqlConnection(Properties.Settings.Default.TestConnecti
onString));
this.routeTableAdapter.Adapter.Fill(this.testDataSet.dummy_docks);
}

Have a nice day, Ashutosh!

Best regards,
Ji Zhou ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[\Ji Zhou [MSFT]\]

Hello Ashutosh,

I see your points. As to your two new concerns, I am give the detailed
comments in the following.

1. Is it possible to achieve this result without calling Update on the
route table? I want to give the user an option to save/reject the changes.

Yes, we can achieve the objective without calling the Update function on
the route table. The necessary thing we need to do is updating the
dummy_docks. Of course we can fill the dummy_docks from the local
routeDataGrid, so that we do not need to save changes to data base
immediately. If we are using .NET 3.0 or above version Framework, we can
adopt the LINQ to achieve that easily as follows.

System.Collections.Generic.IEnumerable<Int32> ddE;

private void Form1_Load(object sender, EventArgs e)
{
this.lanesTableAdapter.Fill(this.testDataSet.lanes);
this.routeTableAdapter.Fill(this.testDataSet.route);

ddE = (from d in this.testDataSet.route
orderby d.dock
select d.dock).Distinct();

foreach (Int32 d in ddE)
{
this.testDataSet.dummy_docks.Rows.Add(new object[] { d });
}

this.routeDataGridView.RowsAdded += new
DataGridViewRowsAddedEventHandler(routeDataGridView_RowsAdded);

this.dataGridViewComboBox1.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox1.DisplayMember = "dock";

this.dataGridViewComboBox2.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox2.DisplayMember = "dock";

this.dataGridViewComboBox3.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox3.DisplayMember = "dock";

this.dataGridViewComboBox4.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox4.DisplayMember = "dock";

this.dataGridViewComboBox5.DataSource = this.testDataSet.dummy_docks;
this.dataGridViewComboBox5.DisplayMember = "dock";
}

void routeDataGridView_RowsAdded(object sender,
DataGridViewRowsAddedEventArgs e)
{
this.testDataSet.dummy_docks.Clear();
ddE = (from d in this.testDataSet.route
orderby d.dock
select d.dock).Distinct();

foreach (Int32 d in ddE)
{
this.testDataSet.dummy_docks.Rows.Add(new object[] { d });
}
}

private void button1_Click(object sender, EventArgs e)
{
this.testDataSet.route.Rows.Add(new object[] {"r7", 4 });
}

Whenever an Row is added into the routeDataGrid, the RowAdded event fires
and we fill the dummy_docks from there. If we cannot use the LINQ, we have
to iterate through the routeDataGrid and pick up all distinct dock value by
our own codes, and then fill the dummy_docks. That means some workload.

2.I agree with your comment. If we do not want to update the local data to
the data base, we have to manipulate it on local side. The LINQ feature
will release our efforts from traversing all items for it supports querying
the memory data objects, like Array, List and DataTable. Would you like to
considering adopt the LINQ in your project? We can find some getting start
articles in the following articles,

http://www.codeproject.com/KB/vista/LINQ_1.aspx
http://www.codeproject.com/KB/vista/LINQ2.aspx
http://www.codeproject.com/KB/vista/LINQ_3.aspx

Please let me know if you need any future assistance. I will be glad to be
of help. Have a nice day!


Best regards,
Ji Zhou
Microsoft Online Community Support

=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================