Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ironcladlou
Starting Member
2 Posts |
 Posted - 2003-05-15 : 11:22:19
|
| Here's something that probably has an easy answer that is simply eluding me at the moment...Consider two tables with the following structure:table: ITEMS (pk_item_id, description, quantity, status_id)table: ITEM_STATUS (pk_status_id, description)Now, ITEMS.status_id is a foreign key referencing ITEM_STATUS.pk_status_id. From an end-user perspective, let's say there is a page displaying to them the item ID, description, and ITEM_STATUS.description for all items (As the ITEM_STATUS.pk_status_id is irrelevant to the person doing data retrieval/entry). So, the user may see a table on a web page such as:Item Desc Qty Status-------------------------------------------------1000 An item 2 In stock1001 Another item 4 In stock1002 Yet another 1 OutHerein lies the problem. Consider that the user should have the ability to change the status of the item. In the database, the item status is represented in the ITEMS table by a foreign key reference to another table, and stored as an ID. The view that creates the report above, as I understand it, is not updateable.The question: What is the proper way to present this data so that it can be easily updated by a user? Ideally, they could click an "Edit" link adjacent to each row, which would, for instance, convert the "Status" column into a series of combo boxes. This combo box would list the status descriptions for each status type. The way I'd currently handle it would be to redirect to an edit page, which would have to do a separate query to the ITEM_STATUS table in order to populate the possible status names in the box. This also requires comparisons against the current status description of the row being edited to set which item in the combo box would be initially selected. This all gets messy very quickly. Once all the data was initally populated into the controls, then the actual update would be done against YET ANOTHER connection to the ITEMS table itself -- so that the status_id could be inserted numerically.Is this the way to accomplish this, or am I missing something? It seems there should be some way to provide a list of possible status descriptions in a combo box, derive the status_id from the ITEM_STATUS table from the selection, and update the ITEM table without all the runaround (using a stored procedure, somehow?).Any suggestions? If I have been unclear in what I am trying to accomplish, let me know. This is a lingering issue in my brain!-Dan |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-15 : 11:46:55
|
| Include BOTh the statusID and the status field in your view. Don't show the user the StatusID.Then, present the list of all statuses for the user to pick, grab the ID of that status, and then just update the view changing the statusID of that one row. The statusdescription column should change automatically.- Jeff |
 |
|
|
|
|
|
|
|
Updates to joined tables from end-user perspective