Combo box load from a table

[Robert Schuldenfrei]

Dear NG,

I have successfully bound a combo box to a table when only one column was
involved. I am trying to present the user with the primary key to an
item-location table. The primary key involves two columns, loc_PartNo and
loc_location. My method: GetItemLocListing() works fine and returns the
DataTable that I instantiate to locTable. I can not get the combo box to
display and select both parts of the Primary Key. To test the combo box I
have established three examples of Primary Keys in the ArrayList: locations.
For ease of parsing, I have inserted the pipe character ("|") between the
two columns. This works fine. I can think of a number of ways to approach
this problem, but so far nothing has worked due to my limited knowledge of
C#, SQL, and ADO.NET. Here are my thoughts:

1/ Get the data directly out of the DataTable. I would need to "explain" to
the .ValueMember where each part of the Primary Key is such that it displays
both parts.

2/ Some how assign the ArrayList to the concatenated columns of the Primary
Key. In an ideal world this would be done indirectly by over-laying the
ArrayList on top of the DataTable.

3/ Although it would be inefficient, copy each row of the DataTable into the
ArrayList.

Any and all suggestions would be appreciated,

Bob

Robert Schuldenfrei
(e-mail address removed)



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

{

BindComboBox();

//Make sure Tag property is set even if no scrolling is done.

this.Tag = cboItemLoc.SelectedValue;

}

public static DataTable GetItemLocList()

{

SqlConnection mcs3Connection = MCS3_DB.GetConnection();

string selectStmt = "SELECT loc_PartNo, loc_location FROM ItemLoc ORDER BY "

+ "loc_PartNo, loc_location";

SqlCommand selectCommand = new SqlCommand(selectStmt, mcs3Connection);

mcs3Connection.Open();

SqlDataAdapter locDataAdapter = new SqlDataAdapter(selectCommand);

DataSet locDataSet = new DataSet();

locDataAdapter.Fill(locDataSet, "Loc");

mcs3Connection.Close();

return locDataSet.Tables["Loc"];

}

private void BindComboBox()

{

//disable event while processing

cboItemLoc.SelectedIndexChanged -= new
System.EventHandler(cboItemLoc_SelectedIndexChanged);

DataTable locTable = GetItemLocList();


//debug combo box - force a few loc_PartNo loc_location values

ArrayList locations = new ArrayList();

locations.Add("500-000 | Prime");

locations.Add("500-000 | new");

locations.Add("100-000 | Prime");


cboItemLoc.DataSource = locations; //this was the DataTable locTable

//cboItemLoc.DisplayMember = "loc_PartNo";

//cboItemLoc.ValueMember = "loc_PartNo";

//enable event

cboItemLoc.SelectedIndexChanged += new
System.EventHandler(cboItemLoc_SelectedIndexChanged);

}

 

[Robert Schuldenfrei]

Dear NG,

Forgive me for asking first and then looking at the NG later. Mr. William
Ryan had the answer in his very excellent web site. It turned out to be
DataColumn. Here is the code that did the trick:

//use DataColumn to concatinate the Primary Key.

DataColumn dcKey = new DataColumn();

dcKey.DataType = System.Type.GetType("System.String");

dcKey.ColumnName = "KeyString"; //this is passed to the combo box

dcKey.Expression = "loc_PartNo + ' | ' + loc_location";

locTable.Columns.Add(dcKey);


cboItemLoc.DataSource = locTable;

cboItemLoc.DisplayMember = "KeyString";

cboItemLoc.ValueMember = "KeyString";

Cheers,

Bob

Dear NG,

I have successfully bound a combo box to a table when only one column was
involved. I am trying to present the user with the primary key to an
item-location table. The primary key involves two columns, loc_PartNo and
loc_location. My method: GetItemLocListing() works fine and returns the
DataTable that I instantiate to locTable. I can not get the combo box to
display and select both parts of the Primary Key. To test the combo box I
have established three examples of Primary Keys in the ArrayList: locations.
For ease of parsing, I have inserted the pipe character ("|") between the
two columns. This works fine. I can think of a number of ways to approach
this problem, but so far nothing has worked due to my limited knowledge of
C#, SQL, and ADO.NET. Here are my thoughts:

1/ Get the data directly out of the DataTable. I would need to "explain" to
the .ValueMember where each part of the Primary Key is such that it displays
both parts.

2/ Some how assign the ArrayList to the concatenated columns of the Primary
Key. In an ideal world this would be done indirectly by over-laying the
ArrayList on top of the DataTable.

3/ Although it would be inefficient, copy each row of the DataTable into the
ArrayList.

Any and all suggestions would be appreciated,

Bob

Robert Schuldenfrei
(e-mail address removed)



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

{

BindComboBox();

//Make sure Tag property is set even if no scrolling is done.

this.Tag = cboItemLoc.SelectedValue;

}

public static DataTable GetItemLocList()

{

SqlConnection mcs3Connection = MCS3_DB.GetConnection();

string selectStmt = "SELECT loc_PartNo, loc_location FROM ItemLoc ORDER BY "

+ "loc_PartNo, loc_location";

SqlCommand selectCommand = new SqlCommand(selectStmt, mcs3Connection);

mcs3Connection.Open();

SqlDataAdapter locDataAdapter = new SqlDataAdapter(selectCommand);

DataSet locDataSet = new DataSet();

locDataAdapter.Fill(locDataSet, "Loc");

mcs3Connection.Close();

return locDataSet.Tables["Loc"];

}

private void BindComboBox()

{

//disable event while processing

cboItemLoc.SelectedIndexChanged -= new
System.EventHandler(cboItemLoc_SelectedIndexChanged);

DataTable locTable = GetItemLocList();


//debug combo box - force a few loc_PartNo loc_location values

ArrayList locations = new ArrayList();

locations.Add("500-000 | Prime");

locations.Add("500-000 | new");

locations.Add("100-000 | Prime");


cboItemLoc.DataSource = locations; //this was the DataTable locTable

//cboItemLoc.DisplayMember = "loc_PartNo";

//cboItemLoc.ValueMember = "loc_PartNo";

//enable event

cboItemLoc.SelectedIndexChanged += new
System.EventHandler(cboItemLoc_SelectedIndexChanged);

}