Member Avatar for mundetas

Hi, I was wondering if someone could please help me. I have an access database with two tables. One table is Movie and the other is Genre all in a relationship. I have a foreign key in Movie pointing to Genre. My Movie table looks like this "MovieID, MovieName, GenreFK". My Genre table looks like this "GenreID, GenreName". My problem is when I create an input form. I don't want the drop down box to show the FK number but instead the GenreName. And I submit The Movie table should have the right number on the FK. I hope this makes sense please please can someone help me.

  • 2 Contributors
  • 3 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by jbisono

Dani AI

Generated

— the short answer: populate the dropdown/combobox from the Genre table so the control contains both the genre ID and the genre name, show the name to the user, but keep the ID as the control's underlying value so when you insert/update Movie you write that ID into Movie.GenreFK. was correct to point you in that direction.

If you are using an Access form, set the combo's Row Source to a query that returns the ID and the name, set ColumnCount to 2, set BoundColumn to the ID column (usually 1) and set ColumnWidths so the ID column is hidden (for example "0;2"). That displays only the name while the combo.Value is the numeric GenreID. If you are using WinForms, use DataSource with DisplayMember/ValueMember; for ASP.NET WebForms use DataSource with DataTextField/DataValueField — the pattern is the same: display the name, keep the ID as the value.

When saving, read the control's selected value, convert/validate it as an integer, and use a parameterized INSERT or UPDATE so Movie.GenreFK receives that ID. When you need to show movies with their genre text, join the tables instead of relying on the FK column itself. Example SQL for display:

SELECT m.MovieID, m.MovieName, g.GenreName
FROM Movie AS m
LEFT JOIN Genre AS g
ON m.GenreFK = g.GenreID;

Troubleshooting: if the list shows numbers instead of names, check the display/display-member property or ColumnWidths; if the saved FK is wrong, log the control.Value before the DB call and ensure types match (int vs string). Enforce referential integrity in Access relationships to prevent orphaned FK values.

Recommended Answers

All 3 Replies

Member Avatar for jbisono

Ok, first i imagine you have a dropdownlist to display all genres in the new movie entry form. is that is the case you will have a select statment like. select GenreID, GenreName from Genre then fill a dataset with that result, lets say the dataset is called dsGenres so to fill the dropdown you will have this.

DropDown.DataSource = dsGenre;
DropDown.DataTextField = "GenreName";
DropDown.DataValueField = "GenreID";
DropDown.DataBind();

Then to retrieve the key in the insert statement you need to say this

DropDown.SelectedItem.Value;

I hope that help you a little bit.

Member Avatar for mundetas

Thanks I will try it but doesn't the select statement need details of the other table I am rather new to C#

Member Avatar for jbisono

In my opinion, regardless the operation in issue (Insert a new record or displaying data from db) you need to fill the dropdown with all values from genre table.

Now if you want to display a specific genre belongs to a movie i guess you will have a datareader or something like that to retreive those values, select MovieID, MovieName, GenreFK from movies now the c# is imagine the datareader is called dr

DropDown.Items.FindByValue(dr[2]).Selected = true;

remember for dr[2] you have to make the cast to integer if its the case.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.