I have a table with following columns:
Tutorial Number
Lesson Number
Order Number
where each tutorial has many lessons and there is a order number for
each lesson within that tutorial.
In the UI, once the user re-sorts the lessons in any manner which they
like, I am to store the new order as is the order in the datagrid.
How can I do that?
what should the Stored Procedure do?
thanks in advance,On 30 Sep 2005 14:15:08 -0700, nashak@.hotmail.com wrote:
>I have a table with following columns:
>Tutorial Number
>Lesson Number
>Order Number
>where each tutorial has many lessons and there is a order number for
>each lesson within that tutorial.
>In the UI, once the user re-sorts the lessons in any manner which they
>like, I am to store the new order as is the order in the datagrid.
>How can I do that?
>what should the Stored Procedure do?
>thanks in advance,
Hi nashak,
Homework assignment for school?
I think you need one stored procedure, to move one lesson to a new
location. Input parameters would be the tutorial number, the lesson to
be moved and the new location (either "before" or "after" another
lessen; in both cases you'll need a special value for "end of list" or
"start of list").
Draw some examples, think about possible cases (move forward - move
backward - "move", but new location = old location - etc), and work out
how in each of these cases the assigned Order Numbers should change.
Then translate those rules into SQL, hand in the assignment to your
teacher, and hope he or she doesn't read this group.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
Wish it were just as simple as homework assigment.. :-)
The actual columns are different. I thought of these just as a sample.
What I am trying to do is as follows:
My table stores the Tutorial, lessons in that tutorial and the order of
the lessons.
The datagrid in my UI has been bound to the dataset that I created with
the above table. This grid has buttons to move the lessons up or
down(and thereby changing the order) as well as a checkbox to delete
the lesson from the tutorial. I need to first delete all the deleted
lessons and then capture the order of lessons and store this new order
in the "OrderOfLesson" column.
One way I think is to put a client side script that would change order
at every button click and store that order as well as change status of
rows that have been deleted. Then just update the database by calling
update() of the datatable. Is there some example code of the above that
i could look at?|||Some more stuff:
The table stores Order of lessons. However the Datagrid does not
display the order number. Is there anyway for the dataset to contain
order number column but for the Datagrid to not display that column?
In the above case, I could then at the Save event
(1) Go thru dataset and re-order all the rows and then call Update() of
dataset to update the rows in the database.
Does this sound possible?|||On 1 Oct 2005 08:06:35 -0700, nashak@.hotmail.com wrote:
>Hugo,
>Wish it were just as simple as homework assigment.. :-)
>The actual columns are different. I thought of these just as a sample.
>What I am trying to do is as follows:
>My table stores the Tutorial, lessons in that tutorial and the order of
>the lessons.
>The datagrid in my UI has been bound to the dataset that I created with
>the above table. This grid has buttons to move the lessons up or
>down(and thereby changing the order) as well as a checkbox to delete
>the lesson from the tutorial. I need to first delete all the deleted
>lessons and then capture the order of lessons and store this new order
>in the "OrderOfLesson" column.
>One way I think is to put a client side script that would change order
>at every button click and store that order as well as change status of
>rows that have been deleted. Then just update the database by calling
>update() of the datatable. Is there some example code of the above that
>i could look at?
Hi nashak,
Not a homework assignment, then. Okay. Sorry for being suspicious.
Anyway, that doesn't change the rest of my answer: you should have a
stored procedure that you can call to move a lesson around in the order.
The body of the stored procedure would probably consist of some IF's to
determine which of the various cases applies (giving one order a higher
place in the list, giving one order a lower place in the list, maybe
some more as well), and one UPDATE statement for each of the cases.
Here's the outline of an UPDATE statement that will move the lesson that
held the 6th place to the 3rd place (and simulteneously move the lessons
that were at 3rd, 4th and 5th place one place down):
UPDATE Lessons
SET Place = CASE WHEN Place = 6 THEN 3 ELSE Place + 1 END
WHERE Place BETWEEN 3 AND 6
AND other criteria
The real procedure would use variables, of course :-)
If you need more help, then please provide some more information about
your tables (as CREATE TABLE statements) and sample data (as INSERT
statements) that I can use to build and test the procedure. For tips on
how to assemble this information: www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
I did think of that. However the concern that I have is that this SP is
called everytime the user moves a lesson up and down. i.e if lesson
goes up I do order-- for that and order++ for the previous one. Too
many db calls.
Instead, I would like to see if I can let the user do whatever he wants
and then try to store the complete data from grid/dataset with a new
order starting from the first row.
Thanks,|||A general SP for re-ordering a sequential column has been posted
before.|||On 1 Oct 2005 15:13:03 -0700, nashak@.hotmail.com wrote:
>Hello,
>I did think of that. However the concern that I have is that this SP is
>called everytime the user moves a lesson up and down. i.e if lesson
>goes up I do order-- for that and order++ for the previous one. Too
>many db calls.
>Instead, I would like to see if I can let the user do whatever he wants
>and then try to store the complete data from grid/dataset with a new
>order starting from the first row.
>Thanks,
Hi nashak,
The advantage of using "too many db calls" is that changes are persisted
directly. No data loss if the client crashes. Of course, that's a down
side as well, as you'll either have to settle for no "abort all changes"
button, or use a long running transaction that will cause blocking and
hurt concurrency.
If you prefer using less db calls, build an app roughly like this:
- Get lessons and their relative positions from DB; store into array.
- Allow user to change order. Note changes in array.
- If user decides to abort changes, do nothing.
- If user decides to accept changes, call stored procedure that updates
order column for all lessons in one UPDATE statement. Do check if
someone else made changes in the meantime (you can use a rowversion
column for this - google "optimistic locking" for more details).
- If stored procedure reports changes by other users, go back to step 1
(or allow user to choose to override changes by other, or ...)
Getting the changed information back to the stored procedure requires an
array parameter. Unfortunately, there is no such thing in SQL Server.
See http://www.sommarskog.se/arrays-in-sql.html for some alternatives.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql
