maggiemel
Posts: 75
|
| Posted: 09/21/2004, 10:17 AM |
|
Okay, I'm hoping someone out there will have a suggestion for how to approach a problem with my ASP application.
I have a database where users enter documents and can manually assign each document a "tab" number. Let's say there are currently 10 docs in the db, numbered 1-10, and a user wants to squeeze one in between 7 and 8. Rather than having them update the other records, is there something I can do either on the SQL side (that will automatically update the rows in the table) or within the CodeCharge application (that will allow users to assign a tab number of 7.1 and then somehow manipulate the returned recordset) so that a whole, correctly ordered number is displayed?
Which is the better solution? I'm thinking it would be more elegant if the database ran some kind of function that would re-order numbers whenever a new record is inserted, but then what happens if users decide to re-order things? By allowing them to enter float numbers, they can easily add documents wherever they want. The problem then is how to manipulate the output so that the tab numbers are displayed as whole integers and stay in the correct order when the users re-sort data in the grid.
Any thoughts? Every suggestion is appreciated!
Thanks.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
 |
 |
DonB
|
| Posted: 09/21/2004, 2:31 PM |
|
Tthat sort of thing can be done by
UPDATE table set ItemNumber= ItemNumber+ 1 WHERE ItemNumber>= newNumber
in this case, in the Before Insert event would be a logical place to do it,
because CCS will be doing:
INSERT table (ItemNumber,...) VALUES (newNumber,...)
right after that when the Insert is performed
or, just maintain the items in a linked list fashion (where each record has
a "parentID"), not worrying about the numbering. When you display them,
increment a counter as you traverse the list and display the calculated
number - not the value from the database. The linked list is nice because
you never have to renumber anything.
--
DonB
http://www.gotodon.com/ccbth
"maggiemel" <maggiemel@forum.codecharge> wrote in message
news:6415062280f3f9@news.codecharge.com...
> Okay, I'm hoping someone out there will have a suggestion for how to
approach a
> problem with my ASP application.
>
> I have a database where users enter documents and can manually assign each
> document a "tab" number. Let's say there are currently 10 docs in the db,
> numbered 1-10, and a user wants to squeeze one in between 7 and 8.
Rather
> than having them update the other records, is there something I can do
either
> on the SQL side (that will automatically update the rows in the table) or
> within the CodeCharge application (that will allow users to assign a tab
number
> of 7.1 and then somehow manipulate the returned recordset) so that a
whole,
> correctly ordered number is displayed?
>
> Which is the better solution? I'm thinking it would be more elegant if
the
> database ran some kind of function that would re-order numbers whenever a
new
> record is inserted, but then what happens if users decide to re-order
things?
> By allowing them to enter float numbers, they can easily add documents
wherever
> they want. The problem then is how to manipulate the output so that the
tab
> numbers are displayed as whole integers and stay in the correct order when
the
> users re-sort data in the grid.
>
> Any thoughts? Every suggestion is appreciated!
>
> Thanks.
>
> _________________
> Melissa Cahill
> http://www.hellcatmaggie.net/
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
maggiemel
Posts: 75
|
| Posted: 09/22/2004, 11:24 AM |
|
Don, thanks for these ideas. I'm not sure I see how the first one would work. If I have 10 items already numbered, and I decide to insert a new one as number 4, then wouldn't all the updated ones (6-10) become 5 when using the UPDATE statement you suggest?
>>UPDATE table set ItemNumber= ItemNumber+ 1 WHERE ItemNumber>= newNumber
I'd like to try the linked list" idea, but again I'm not clear on how to set a parent ID for each record. And what becomes of the numbering scheme if a user decides to resort the grid by a different column?
Are there any practical examples you can point me too?
Thanks again. Your help is much appreciated.
Melissa
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
DonB
|
| Posted: 09/22/2004, 11:57 AM |
|
No it will add one to each row's ItemNumber. It's setting all the rows with
an ItemNumber > 4 (for your example), so 10 becomes 11, 9 becomes 10, 4
becomes 5, etc. Thus, it leaves a "hole" for the new "4".
To do it the other way you add a new column and insert/update it's value to
be that of the one preceding it in the "list".
--
DonB
http://www.gotodon.com/ccbth
"maggiemel" <maggiemel@forum.codecharge> wrote in message
news:64151c3492efd8@news.codecharge.com...
> Don, thanks for these ideas. I'm not sure I see how the first one would
work.
> If I have 10 items already numbered, and I decide to insert a new one as
number
> 4, then wouldn't all the updated ones (6-10) become 5 when using the
UPDATE
> statement you suggest?
>
> >>UPDATE table set ItemNumber= ItemNumber+ 1 WHERE ItemNumber>= newNumber
>
> I'd like to try the linked list" idea, but again I'm not clear on how to
set a
> parent ID for each record. And what becomes of the numbering scheme if a
user
> decides to resort the grid by a different column?
>
> Are there any practical examples you can point me too?
>
> Thanks again. Your help is much appreciated.
>
> Melissa
>
>
> _________________
> Melissa Cahill
> http://www.hellcatmaggie.net/
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
maggiemel
Posts: 75
|
| Posted: 09/23/2004, 5:48 PM |
|
Okay, Don, you probably think I'm a dolt but I'm still stuck here.
Yes, I see now how scenario #1 works (I was adding the +1 to my newNumber rather than the existing ItemNumber in the db table), but this only works when inserting a new record.
If a user decides that he wants to move #9 into the #2 spot, and edits the record to change the 9 to 2, then what happens is that everything renumbers correctly between 2 and 4, but there's a gap where the old number 9 was, and number 10 becomes 11, etc.
So, I guess I need some extra function to deal with situations where a user wants to re-order existing records. Or maybe I'm trying to approach this all the wrong way ...?
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
maggiemel
Posts: 75
|
| Posted: 09/23/2004, 5:50 PM |
|
Oops, sorry -- I meant everything re-orders correctly between 2 and 9.
_________________
Melissa Cahill
http://www.hellcatmaggie.net/ |
|
|
|
|
