April 14, 2009 at 2:04 pm
I have 2 tables
Candidate (table name)
PK - CandidateID
Candidate_Name
Position (table name)
PositionID
CandidateID - FK
PositionName
What I need to do is when I write the candidate name to the candidate table I need to take the primary key of this table and populate it in the FK column CandidateID in the positions table.
Can someone point me in to how to do this?
April 14, 2009 at 2:07 pm
There seems to be something missing in your description unless you just want to insert a new record to the Position table with no PositionName when you insert a candidate.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 2:15 pm
Ok let's look at it this way
Candidate table
CandidateID PK
CandidateName field
Position (table)
PositionID Pk
PositionDescription
Candade_Position(table)
CandidateID Comes from CandidateTable
PositionID Comes from PositionTable
If I create a webform that will allow me to put in the candidate name and position then
CandidateName_______________________
PositionName_______________________
When it's submitted, the ID's are GUIDS and everything goes into the corresponding tables (Candidate and Position)
Whe I need to figure out is how to make the candidate ID and PositionID Populate the Candade_position table.
I hope this makes since. It's like a phone book
I have a PersonTable and an Phonenumber table
WHen I enter a persons name into the database and have the phonenumber information on there like Work, Cell, Home and fill those 3 in it should populate the table phonenumber with 3 entries and the PK or the PersonTable is a FK in the phonenumber table. I need to konw how when I create the form to populate the field (FK PersonID).
God I hope this makes since.
April 14, 2009 at 2:24 pm
If you use int (or bigint if you expect LOTS of rows) columns set as identity instead of GUIDs it would be a lot easier. The you can just use the scope_identity function to return the ID you are looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 14, 2009 at 2:27 pm
Ok, if I use Identity instead of GUID how then when I createing a webform, would I update the 3rd table which just holds the Primary Key's of the other tables would I populate them?
April 14, 2009 at 2:31 pm
That leads to my other question. do you really need the intermediate table? One candidate can have multiple positions AND one position can have multiple candidates.
This would be many times easier if you can post your create scripts so we have something to work with. 😀
Sean
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply