Populating WPF Combobox with data from .sdf database

[Loony064]

Hey All!

I'm working on a project where there is a lot of data input from the user. There are numerous combo boxes to help minimise user input and prevent input errors. One of the combo boxes (ComboType) needs to be populated with all the records from the relevant database table EvidenceType which has two columns, namely ExhibitNum (primary key - int) and EvidenceDesc (nvarchar).

The combo boxes display member must be the EvidenceDesc field but the actual value of the selected item must be the EvidenceNum. My dataset is GoldStarDBDataset.

I've tried numerous different methods with no success. I'll list a couple below:

// create new dataset
GoldStarDBDataset ds = new GoldStarDBDataset();
ComboType.DataContext = ds.Tables["EvidenceType"].Defaultview;
ComboType.DisplayMemberPath = ds.Tables["EvidenceType"].Columns["EvidenceDesc"].ToString();
ComboType.SelectedValue = ds.Tables["EvidenceType"].Columns["ExhibitNum"].ToString();

The control is in a tab control. Each time the user navigates to a different tab, the items in the combo boxes need to be re-added as changes to other records may affect their values. The code below, which I call on selection_changed of the tab control, works, but each time I navigate back to the page, the old items are still in the control and this causes there to be duplicates items. I tried using

ComboType.items.Clear();

but that caused more hassle.

GoldStarDBDataset.EvidenceTypeDataTable dt = new GoldStarDBDataset.EvidenceTypeDataTable();
GoldStarDBDatasetTableAdapters.EvidenceTypeTableAdapter ta = new GoldStarDBDatasetTableAdapters.EvidenceTypeTableAdapter();

// array to store rows
DataRow[] rows;
// select all rows
rows = dt.Select();

for (int i = 0; i < rows.Count(); i++)
{
   // add item to control - 1 is the index of the field we want to use as display member
   ComboType.Items.Add(rows[i].ItemArray.GetValue(1));
}

Does anyone have any suggestions that I can try?

Thanks
Laura

[finito]

MyConnection.Open()
ComboType.Items.Clear();
SqlDataReader dr = new SqlCommand("SELECT STATEMENT", MyConnection).ExecuteReader();
while(dr.Read())
{
   ComboType.Items.Add(dr.GetInt32(0)); // Use CorrectCast and replace 0 with correct placement zero means first.
}
MyConnection.Close();