data binding

[Frazer]

hi
i have a combo box which i populate with all tables from northwind database
and when the user selects an item from the combo, i want to populate the
datagrid with data from that table.

private void FillComboBox()

{

DataSet allTablesDataSet = new DataSet("AllTables");

string sqlString;

this.tableComboBox.Items.Clear();

sqlString = "Select * from sysobjects where type ='u'";

this.sqlDataAdapter = new SqlDataAdapter();

this.sqlDataAdapter.SelectCommand = new SqlCommand(sqlString,
this.sqlConnection);

this.sqlDataAdapter.Fill(allTablesDataSet,"AllTabl es");

this.tableComboBox.DataSource = allTablesDataSet.Tables[0];

this.tableComboBox.DisplayMember = "name";

this.tableComboBox.ValueMember = "name";

}

private void OnTableComboBoxSelectedIndexChanged(object sender,
System.EventArgs e)

{

if(this.tableComboBox.SelectedIndex == -1)

return;

this.tableDataSet = new DataSet();

this.sqlDataAdapter.SelectCommand = new SqlCommand("Select * from [" +
this.tableComboBox.SelectedValue + "]" ,this.sqlConnection);

this.sqlDataAdapter.Fill(this.tableDataSet, "Table");

this.dataGrid1.DataSource = this.tableDataSet;

this.dataGrid1.DataMember = "Table";

}

however the first time when the program is run i get the foll error. how do
i resolve that?

thnx

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.

on this line

this.sqlDataAdapter.Fill(this.tableDataSet, "Table");

[Cor Ligthert]

Hi Frazer,

The standard problem with the binded combobox is that when it is loading it
fires unpredictable the selected index change event.

Solutions for this are, set a switch in that event function or add the
handler on the moment that it is completly loaded (when you are reloading it
than you have to set the switch again or remove the handler at the right
moment).

(Why you do that clear of the combobox, if you are reseting it in your
program, than you should set in my opinion the datasource to null)

I hope this helps?

Cor

[William Ryan eMVP]

Hi Frazier:

Are you firing another query each time the index changes? you definitely
don't want to do this. You can just load the data into a datatable and bind
it to the combobox. Also, you're getting a SQLException which means
specifically it's a problem w/ the db.

Cor is exaclty right about the SelectedIndexChange and that's ostensibly
causing this even though it's a db error. Also, don't use Dynamic SQL, use
Parameters. Nonetheless, you'll want to grab two tables, one for the
parent, one for the child, and just set the bindings of the grid and the
CombBox. You'll need to use a DataRelation
http://www.knowdotnet.com/articles/datarelation.html to make this easy. If
you do this, you won't have to trap anything on SelectedIndexChanged to get
the child records to show up in a grid. This is pretty common and if hit a
search engine for Master Detail ADO.NET there will be tons of examples.

Let me know if you need any elaboration.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"Frazer" <Ic***@hotmail.com> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...

hi
i have a combo box which i populate with all tables from northwind database and when the user selects an item from the combo, i want to populate the
datagrid with data from that table.

private void FillComboBox()

{

DataSet allTablesDataSet = new DataSet("AllTables");

string sqlString;

this.tableComboBox.Items.Clear();

sqlString = "Select * from sysobjects where type ='u'";

this.sqlDataAdapter = new SqlDataAdapter();

this.sqlDataAdapter.SelectCommand = new SqlCommand(sqlString,
this.sqlConnection);

this.sqlDataAdapter.Fill(allTablesDataSet,"AllTabl es");

this.tableComboBox.DataSource = allTablesDataSet.Tables[0];

this.tableComboBox.DisplayMember = "name";

this.tableComboBox.ValueMember = "name";

}

private void OnTableComboBoxSelectedIndexChanged(object sender,
System.EventArgs e)

{

if(this.tableComboBox.SelectedIndex == -1)

return;

this.tableDataSet = new DataSet();

this.sqlDataAdapter.SelectCommand = new SqlCommand("Select * from [" +
this.tableComboBox.SelectedValue + "]" ,this.sqlConnection);

this.sqlDataAdapter.Fill(this.tableDataSet, "Table");

this.dataGrid1.DataSource = this.tableDataSet;

this.dataGrid1.DataMember = "Table";

}

however the first time when the program is run i get the foll error. how do i resolve that?

thnx

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll

Additional information: System error.

on this line

this.sqlDataAdapter.Fill(this.tableDataSet, "Table");

[Frazer]

What i am doing is populating the combobox with all tables from the
northwind database and
when the user selects a particular table i fill the datagrid with rows from
that table.

Is my approach too lengthy to achieve this?
Is master - detail approach the right one to use for this purpose.

Thanks for your valuable input

[Cor Ligthert]

Hi Frazer,

I find your approach very nice. Exactly how it should be done in my opinion.

Cor

[Frazer]

Hi,
Thanks for your reply,
However I didnt understand where this would fit in my approach.

" Nonetheless, you'll want to grab two tables, one for the

parent, one for the child, and just set the bindings of the grid and the
CombBox. You'll need to use a DataRelation
http://www.knowdotnet.com/articles/datarelation.html to make this easy. If you do this, you won't have to trap anything on SelectedIndexChanged to get the child records to show up in a grid. "
Can i bind a datagrid and a combobox ?, so that when i click on certain
items in the combo the data grid is reflected accordingly???

Thnx.
"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:uX**************@TK2MSFTNGP10.phx.gbl... Hi Frazier:

Are you firing another query each time the index changes? you definitely
don't want to do this. You can just load the data into a datatable and bind it to the combobox. Also, you're getting a SQLException which means
specifically it's a problem w/ the db.

Cor is exaclty right about the SelectedIndexChange and that's ostensibly
causing this even though it's a db error. Also, don't use Dynamic SQL, use Parameters. Nonetheless, you'll want to grab two tables, one for the
parent, one for the child, and just set the bindings of the grid and the
CombBox. You'll need to use a DataRelation
http://www.knowdotnet.com/articles/datarelation.html to make this easy. If you do this, you won't have to trap anything on SelectedIndexChanged to get the child records to show up in a grid. This is pretty common and if hit a search engine for Master Detail ADO.NET there will be tons of examples.

Let me know if you need any elaboration.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
"Frazer" <Ic***@hotmail.com> wrote in message
news:Ox**************@tk2msftngp13.phx.gbl...

hi
i have a combo box which i populate with all tables from northwind

database

and when the user selects an item from the combo, i want to populate the
datagrid with data from that table.

private void FillComboBox()

{

DataSet allTablesDataSet = new DataSet("AllTables");

string sqlString;

this.tableComboBox.Items.Clear();

sqlString = "Select * from sysobjects where type ='u'";

this.sqlDataAdapter = new SqlDataAdapter();

this.sqlDataAdapter.SelectCommand = new SqlCommand(sqlString,
this.sqlConnection);

this.sqlDataAdapter.Fill(allTablesDataSet,"AllTabl es");

this.tableComboBox.DataSource = allTablesDataSet.Tables[0];

this.tableComboBox.DisplayMember = "name";

this.tableComboBox.ValueMember = "name";

}

private void OnTableComboBoxSelectedIndexChanged(object sender,
System.EventArgs e)

{

if(this.tableComboBox.SelectedIndex == -1)

return;

this.tableDataSet = new DataSet();

this.sqlDataAdapter.SelectCommand = new SqlCommand("Select * from [" +
this.tableComboBox.SelectedValue + "]" ,this.sqlConnection);

this.sqlDataAdapter.Fill(this.tableDataSet, "Table");

this.dataGrid1.DataSource = this.tableDataSet;

this.dataGrid1.DataMember = "Table";

}

however the first time when the program is run i get the foll error. how

do

i resolve that?

thnx

An unhandled exception of type 'System.Data.SqlClient.SqlException'

occurred

in system.data.dll

Additional information: System error.

on this line

this.sqlDataAdapter.Fill(this.tableDataSet, "Table");