maggiemel
Posts: 75
|
| Posted: 03/25/2005, 1:26 PM |
|
Hello everybody. I have an editable grid which uses a composite primary key to keep many details (Detail_ID) for each related master record (Master_ID). How do I go about incrementing the Detail_ID when the user adds details using the editable grid? I already use a custom insert -- I'm thinking I could add the Detail_ID as a Parameter, but I'm not sure of how to get the last numerical value, increment it by one, and submit it with the form for insert.
Hope this makes sense. I'm only beginning to play with composite keys in my applications, and not sure how to deal with this.
Many thanks.
Melissa
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
 |
 |
Oper
Posts: 1195
|
| Posted: 03/26/2005, 3:43 PM |
|
Maggimel,
Why dont you use AutoIncrement Primary kEY?
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
maggiemel
Posts: 75
|
| Posted: 03/27/2005, 4:21 AM |
|
I already use an indentity key to identify each record in the table. But I need to keep a separate key for each master record's individual details. So for each record in the master table, I need to be able to distinguish between detail 1, detail 2, etc. So that when I query I can say, fetch me the first detail record for each master record. See?
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
peterr
Posts: 5971
|
| Posted: 03/27/2005, 11:31 AM |
|
Looks like you're talking about master/detail relation.
There is Order Entry example in CCS Example Pack that shows how to implement this.
Online version is at http://examples.codecharge.com/ExamplePack/OrderEntry/OrderEntry.php
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
maggiemel
Posts: 75
|
| Posted: 03/30/2005, 8:48 AM |
|
Peter, thanks -- I've been only half successful with this.
Because I could not figure out how to increment the DetailID for each MasterID automatically, I added a textbox field to the editable grid. When there are already records in the db, the textbox correctly displays the DetailID. However, when there are no details entered yet, I would like my grid to display "1" when the user clicks on "Add Row" and then subsequently increment so that if I need to add 3 details to my master record, and I click "Add Row" 3 times, my textboxes will display 1 through 3.
I have found a couple of methods to do this using javascript, but they don't seem to work properly in combination with the custom Add Row code. Likewise, trying to do this on the server-side either doesn't work -- it breaks the Add Row feature altogether.
I would like to use this method of displaying a textbox with the DetailID because then a user can re-order the detail records. But it would be better if the user doesn't have to enter the numbers manually as part of the data entry process. Does anybody have some ideas...?
Thanks a million...
Melissa
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
peterr
Posts: 5971
|
| Posted: 03/30/2005, 9:32 AM |
|
I wouldn't recommend this method because if users select [x] Delete for certain rows (when editing the records later) then you may need to recalculate all row numbers later anyway, probably on the server during the inserts/updates. You also may need to handle conflicts in record numbering (duplicates).
Therefore I still recommend using autoincremented keys as this is most common method I've seen used, also used in our example. If you need to allow users to enter sort order for each record then I'd recommend creating another non-key field for this - also common solution.
If you still do decide to show record numbers in your form then you can use this code in the "Before Show Row" event of your editable grid (PHP):
global $RowNumber
$RowNumber++;
$GridName->FieldName->SetValue($RowNumber);
(replace GridName & FieldName accordingly)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
maggiemel
Posts: 75
|
| Posted: 03/30/2005, 12:05 PM |
|
Hmm, well, I'd be happy if I could get either method working.
If using the autoincremented primary keys is the best method, then I would like to understand how to make that work when the seed number has to begin from 1 with each new Master record added. Is this something that can be made to happen on the db end? (Sorry for my ignorance -- if you haven't guessed by now, I'm a complete charlatan when it comes to this stuff).
The alternative method which you outlined in PHP (I translated to ASP as follows: GridName.DetailID.Value = RowNumber) breaks the grid altogether. After filling out the values and submitting the form, an error is returned telling me that the value in my drop-down list is required. So somehow the value is being lost or stomped on by the additional line of code which tried to set the value of the textbox with the row number.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
|
