Database help
-
I am using C# 2010 and I have an access database that connects to my form and I have 7 tables in it, all tables have the same fields but show different information (its an front end user application that connects to a database that has information on shellfish and each table is a different family but contains same field headings such as common name, size of shell, shape of shell, distribution etc...). What I would like to is have two list boxes on my form, one to choose the table and second to choose information on the first column of the table as the first column is the common name of the species you are looking for, which is different depending on the table chosen, and when you choose one, it loads the information into the correct textboxes. How would I do this? Haven't worked with creating front end database applications for about 4 years and getting a little rusty. Thanks in advance.
In the end we're all just the same
-
I am using C# 2010 and I have an access database that connects to my form and I have 7 tables in it, all tables have the same fields but show different information (its an front end user application that connects to a database that has information on shellfish and each table is a different family but contains same field headings such as common name, size of shell, shape of shell, distribution etc...). What I would like to is have two list boxes on my form, one to choose the table and second to choose information on the first column of the table as the first column is the common name of the species you are looking for, which is different depending on the table chosen, and when you choose one, it loads the information into the correct textboxes. How would I do this? Haven't worked with creating front end database applications for about 4 years and getting a little rusty. Thanks in advance.
In the end we're all just the same
You can still do it the same way you did it 4 years ago using ADO.NET. If this is not legacy data, by that I mean you can change the structure without breaking other applications, merging this data into one table will make life a lot easier. Create a Family table with Id and Name fields. Then create another table called Shellfish with all the same fields and include a FamilyId field. Then insert the records from the other tables. You can do this by creating queries in Access. The reason I suggest this is because it will make developing your UI a lot easier. Normalising the data schema will make a huge difference. Also is there any requirement for the data to be stored in Access? Access has an upgrade wizard which will make it easy to convert to a SQL Express database. This will enable you to use Entity Framework which will make development quicker if you don't include the learning curve. Once you have done that, filling your ComboBoxes, ListBoxes, TextBoxes etc., will be a piece of cake. If it is a legacy system you are probably resigned to cutting a lot of code in your Data Access Layer to replicate what I have mentioned above using ADO.NET.
FamilyListBox.DataSource = myData.GetFamilies();
...private void FamilyListBox_SelectedValueChanged(object sender, EventArgs e)
{
CommonNameListBox.DataSource = MyData.GetShellFish();
}private void CommonNameListBox_SelectedValueChanged(object sender, EventArgs e)
{
ShellFish shellFish = CommonNameListBox.SelectedItem as ShellFish;
CommonNameTextBox.Text = shellFish.CommonName;
...
}You will also need to set the DisplayName property for the ListBoxes. Hope that helps but it is a hard question to answer in a few words.
"You get that on the big jobs."