Professional Web Applications Themes

Updating records in order (into an order) - ASP Database

I'm storing questions for a user-defined quiz. I can store them in the order they are entered without any problem. But... The user needs to have the ability to re-order the questions if they wish, or insert a question etc... Currently, for the re-ordering, I'm either having to postback for every re-order movement (I receive the Question code, the direction (Up/Down the list) and then grab the Question and the on before/after it, and switch the assigned question numbers (If I move question 5 to wards the start, I'd grab the questionss in the quiz with field Position = (4,5) ...

  1. #1

    Default Updating records in order (into an order)

    I'm storing questions for a user-defined quiz. I can store them in the order
    they are entered without any problem. But...

    The user needs to have the ability to re-order the questions if they wish,
    or insert a question etc...

    Currently, for the re-ordering, I'm either having to postback for every
    re-order movement (I receive the Question code, the direction (Up/Down the
    list) and then grab the Question and the on before/after it, and switch the
    assigned question numbers (If I move question 5 to wards the start, I'd grab
    the questionss in the quiz with field Position = (4,5) and set 4's position
    to 5 and vice-versa. This is very slow (and could probably be done
    better...ideas please)

    To remedy the numerous postbacks, I'm working in a way to build a client
    side array which will store the re-ordering, and then the user can submit it
    all (I convert the array into a string I can p server-side back into the
    array). I end up with an array with:-

    Question Identifier (guid)
    Question position (int)(0 <-> (quizlength - 1))

    Now, I could do an UPDATE on each QuestionID, and change the position each
    time....which could take ages, and means sending a SQL UPDATE for each
    question, or...

    Is there any way I could do a block update where updates each question (by
    identifier) with the new position as a single SQL command?

    Hope I've explained the porblem well enough,

    Thanks in advance for your help,

    Ann-Marie Ratcliffe


    A Ratcliffe Guest

  2. #2

    Default Re: Updating records in order (into an order)

    Oops...forgot to say...

    I'm using Access as the backend DB....but I'd appreciate knowing how to do
    it for both Access and SQL Server(if different).

    Thanks again in advance,

    Ann-Marie Ratcliffe


    A Ratcliffe Guest

  3. #3

    Default Re: Updating records in order (into an order)

    "A Ratcliffe" <aratcliffearchiNOSPAMmagic.net> wrote in message
    news:%233cs6qYmDHA.1948TK2MSFTNGP12.phx.gbl...
    > I'm storing questions for a user-defined quiz. I can store them in the
    order
    > they are entered without any problem. But...
    >
    > The user needs to have the ability to re-order the questions if they
    wish,
    > or insert a question etc...
    >
    > Currently, for the re-ordering, I'm either having to postback for
    every
    > re-order movement (I receive the Question code, the direction (Up/Down
    the
    > list) and then grab the Question and the on before/after it, and
    switch the
    > assigned question numbers (If I move question 5 to wards the start,
    I'd grab
    > the questionss in the quiz with field Position = (4,5) and set 4's
    position
    > to 5 and vice-versa. This is very slow (and could probably be done
    > better...ideas please)
    >
    > To remedy the numerous postbacks, I'm working in a way to build a
    client
    > side array which will store the re-ordering, and then the user can
    submit it
    > all (I convert the array into a string I can p server-side back
    into the
    > array). I end up with an array with:-
    >
    > Question Identifier (guid)
    > Question position (int)(0 <-> (quizlength - 1))
    >
    > Now, I could do an UPDATE on each QuestionID, and change the position
    each
    > time....which could take ages, and means sending a SQL UPDATE for each
    > question, or...
    >
    > Is there any way I could do a block update where updates each question
    (by
    > identifier) with the new position as a single SQL command?
    >
    > Hope I've explained the porblem well enough,
    >
    > Thanks in advance for your help,
    >
    > Ann-Marie Ratcliffe
    {Access}
    1. Create a utility table called DUAL
    2. Add a yes/no field to the DUAL table and also name it DUAL
    3. Populate the table with one and only one record. The value doesn't
    matter.
    4. Construct a query similar to the one below for batch updates:

    UPDATE
    Q
    SET
    Q.QuestionPos = T.QuestionPos
    FROM
    tblQuestion AS Q,
    [
    SELECT 1 AS QuestionID, 3 AS QuestionPos FROM DUAL UNION ALL
    SELECT 2, 4 FROM DUAL UNION ALL
    SELECT 3, 5 FROM DUAL UNION ALL
    SELECT 4, 1 FROM DUAL UNION ALL
    SELECT 5, 2 FROM DUAL
    ]. AS T
    WHERE
    Q.QuestionID = T.QuestionID

    {SQL Server}
    UPDATE
    Q
    SET
    Q.QuestionPos = T.QuestionPos
    FROM
    tblQuestion AS Q,
    (
    SELECT 1 AS QuestionID, 3 AS QuestionPos UNION ALL
    SELECT 2, 4 UNION ALL
    SELECT 3, 5 UNION ALL
    SELECT 4, 1 UNION ALL
    SELECT 5, 2
    ) AS T
    WHERE
    Q.QuestionID = T.QuestionID

    You may also be interested in the IMS Global Learning Consortium
    Question And Test Interoperability (QTI) specification. Here's a link:
    [url]http://www.imsglobal.org/question[/url]

    HTH
    -Chris Hohmann



    Chris Hohmann Guest

  4. #4

    Default Re: Updating records in order (into an order)

    > Now, I could do an UPDATE on each QuestionID, and change the position each
    > time....which could take ages, and means sending a SQL UPDATE for each
    > question, or...
    Here's an example that might help. This essentially swaps the ranks of the
    question chosen with the adjacent question (the one with the higher rank or
    lower rank, depending on the dir parameter). Another option would be to
    explicitly set the rank...


    CREATE TABLE questions
    (
    QuestionID INT IDENTITY(1,1),
    Question VARCHAR(32),
    Rank INT
    )

    SET NOCOUNT ON
    INSERT questions(Question, rank) VALUES('who?', 1)
    INSERT questions(Question, rank) VALUES('what?', 2)
    INSERT questions(Question, rank) VALUES('where?', 3)
    INSERT questions(Question, rank) VALUES('when?', 4)
    INSERT questions(Question, rank) VALUES('why?', 5)
    INSERT questions(Question, rank) VALUES('how?', 6)
    GO

    CREATE PROCEDURE dbo.shiftQuestion
    id INT,
    dir CHAR(1)
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE f1 INT, f2 INT
    SET f1 = CASE WHEN dir = '+' THEN 1 ELSE -1 END
    SET f2 = -f1

    DECLARE targetID INT
    SELECT targetID = questionID FROM Questions WHERE rank =
    (SELECT rank FROM questions WHERE questionID = iD) + f1

    UPDATE questions SET rank = CASE
    WHEN QuestionID = id THEN rank + f1
    WHEN QuestionID = targetID THEN rank + f2
    ELSE rank END
    END
    GO

    -- Now, try it out:

    SELECT * FROM Questions ORDER BY rank

    EXEC dbo.shiftQuestion 2, '+'

    SELECT * FROM Questions ORDER BY rank

    EXEC dbo.shiftQuestion 4, '-'
    EXEC dbo.shiftQuestion 3, '+'

    SELECT * FROM Questions ORDER BY rank
    GO

    DROP PROCEDURE dbo.shiftQuestion
    DROP TABLE questions
    GO


    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: Updating records in order (into an order)

    That's for SQL Server, by the way.


    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:OWFpPoZmDHA.1728TK2MSFTNGP09.phx.gbl...
    > > Now, I could do an UPDATE on each QuestionID, and change the position
    each
    > > time....which could take ages, and means sending a SQL UPDATE for each
    > > question, or...
    >
    > Here's an example that might help. This essentially swaps the ranks of
    the
    > question chosen with the adjacent question (the one with the higher rank
    or
    > lower rank, depending on the dir parameter). Another option would be to
    > explicitly set the rank...
    >
    >
    > CREATE TABLE questions
    > (
    > QuestionID INT IDENTITY(1,1),
    > Question VARCHAR(32),
    > Rank INT
    > )
    >
    > SET NOCOUNT ON
    > INSERT questions(Question, rank) VALUES('who?', 1)
    > INSERT questions(Question, rank) VALUES('what?', 2)
    > INSERT questions(Question, rank) VALUES('where?', 3)
    > INSERT questions(Question, rank) VALUES('when?', 4)
    > INSERT questions(Question, rank) VALUES('why?', 5)
    > INSERT questions(Question, rank) VALUES('how?', 6)
    > GO
    >
    > CREATE PROCEDURE dbo.shiftQuestion
    > id INT,
    > dir CHAR(1)
    > AS
    > BEGIN
    > SET NOCOUNT ON
    >
    > DECLARE f1 INT, f2 INT
    > SET f1 = CASE WHEN dir = '+' THEN 1 ELSE -1 END
    > SET f2 = -f1
    >
    > DECLARE targetID INT
    > SELECT targetID = questionID FROM Questions WHERE rank =
    > (SELECT rank FROM questions WHERE questionID = iD) + f1
    >
    > UPDATE questions SET rank = CASE
    > WHEN QuestionID = id THEN rank + f1
    > WHEN QuestionID = targetID THEN rank + f2
    > ELSE rank END
    > END
    > GO
    >
    > -- Now, try it out:
    >
    > SELECT * FROM Questions ORDER BY rank
    >
    > EXEC dbo.shiftQuestion 2, '+'
    >
    > SELECT * FROM Questions ORDER BY rank
    >
    > EXEC dbo.shiftQuestion 4, '-'
    > EXEC dbo.shiftQuestion 3, '+'
    >
    > SELECT * FROM Questions ORDER BY rank
    > GO
    >
    > DROP PROCEDURE dbo.shiftQuestion
    > DROP TABLE questions
    > GO
    >
    >

    Aaron Bertrand - MVP Guest

  6. #6

    Default Re: Updating records in order (into an order)

    I spotted it was SQL Server ;). A very nice SP though. I'll see if I can
    apply the logic to Access from my ASP page somehow.

    Thanks,

    Ann-Marie

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:uXqXVqZmDHA.3288tk2msftngp13.phx.gbl...
    > That's for SQL Server, by the way.
    >
    >
    > "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    > news:OWFpPoZmDHA.1728TK2MSFTNGP09.phx.gbl...
    > > > Now, I could do an UPDATE on each QuestionID, and change the position
    > each
    > > > time....which could take ages, and means sending a SQL UPDATE for each
    > > > question, or...
    > >
    > > Here's an example that might help. This essentially swaps the ranks of
    > the
    > > question chosen with the adjacent question (the one with the higher rank
    > or
    > > lower rank, depending on the dir parameter). Another option would be
    to
    > > explicitly set the rank...
    > >
    > >
    > > CREATE TABLE questions
    > > (
    > > QuestionID INT IDENTITY(1,1),
    > > Question VARCHAR(32),
    > > Rank INT
    > > )
    > >
    > > SET NOCOUNT ON
    > > INSERT questions(Question, rank) VALUES('who?', 1)
    > > INSERT questions(Question, rank) VALUES('what?', 2)
    > > INSERT questions(Question, rank) VALUES('where?', 3)
    > > INSERT questions(Question, rank) VALUES('when?', 4)
    > > INSERT questions(Question, rank) VALUES('why?', 5)
    > > INSERT questions(Question, rank) VALUES('how?', 6)
    > > GO
    > >
    > > CREATE PROCEDURE dbo.shiftQuestion
    > > id INT,
    > > dir CHAR(1)
    > > AS
    > > BEGIN
    > > SET NOCOUNT ON
    > >
    > > DECLARE f1 INT, f2 INT
    > > SET f1 = CASE WHEN dir = '+' THEN 1 ELSE -1 END
    > > SET f2 = -f1
    > >
    > > DECLARE targetID INT
    > > SELECT targetID = questionID FROM Questions WHERE rank =
    > > (SELECT rank FROM questions WHERE questionID = iD) + f1
    > >
    > > UPDATE questions SET rank = CASE
    > > WHEN QuestionID = id THEN rank + f1
    > > WHEN QuestionID = targetID THEN rank + f2
    > > ELSE rank END
    > > END
    > > GO
    > >
    > > -- Now, try it out:
    > >
    > > SELECT * FROM Questions ORDER BY rank
    > >
    > > EXEC dbo.shiftQuestion 2, '+'
    > >
    > > SELECT * FROM Questions ORDER BY rank
    > >
    > > EXEC dbo.shiftQuestion 4, '-'
    > > EXEC dbo.shiftQuestion 3, '+'
    > >
    > > SELECT * FROM Questions ORDER BY rank
    > > GO
    > >
    > > DROP PROCEDURE dbo.shiftQuestion
    > > DROP TABLE questions
    > > GO
    > >
    > >
    >
    >

    A Ratcliffe Guest

  7. #7

    Default Re: Updating records in order (into an order)

    Hi, thanks for your reply. Could you break down what your code is doing?
    Maybe its just me being slow-minded this morning, but I can't get my head
    round what your system is doing, and I need to if I'm to build it into my
    ASP page.

    Thanks again,

    Ann-Marie


    "Chris Hohmann" <nospamthankyou.com> wrote in message
    news:e53AvdZmDHA.2068TK2MSFTNGP09.phx.gbl...
    > "A Ratcliffe" <aratcliffearchiNOSPAMmagic.net> wrote in message
    > news:%233cs6qYmDHA.1948TK2MSFTNGP12.phx.gbl...
    > > I'm storing questions for a user-defined quiz. I can store them in the
    > order
    > > they are entered without any problem. But...
    > >
    > > The user needs to have the ability to re-order the questions if they
    > wish,
    > > or insert a question etc...
    > >
    > > Currently, for the re-ordering, I'm either having to postback for
    > every
    > > re-order movement (I receive the Question code, the direction (Up/Down
    > the
    > > list) and then grab the Question and the on before/after it, and
    > switch the
    > > assigned question numbers (If I move question 5 to wards the start,
    > I'd grab
    > > the questionss in the quiz with field Position = (4,5) and set 4's
    > position
    > > to 5 and vice-versa. This is very slow (and could probably be done
    > > better...ideas please)
    > >
    > > To remedy the numerous postbacks, I'm working in a way to build a
    > client
    > > side array which will store the re-ordering, and then the user can
    > submit it
    > > all (I convert the array into a string I can p server-side back
    > into the
    > > array). I end up with an array with:-
    > >
    > > Question Identifier (guid)
    > > Question position (int)(0 <-> (quizlength - 1))
    > >
    > > Now, I could do an UPDATE on each QuestionID, and change the position
    > each
    > > time....which could take ages, and means sending a SQL UPDATE for each
    > > question, or...
    > >
    > > Is there any way I could do a block update where updates each question
    > (by
    > > identifier) with the new position as a single SQL command?
    > >
    > > Hope I've explained the porblem well enough,
    > >
    > > Thanks in advance for your help,
    > >
    > > Ann-Marie Ratcliffe
    >
    > {Access}
    > 1. Create a utility table called DUAL
    > 2. Add a yes/no field to the DUAL table and also name it DUAL
    > 3. Populate the table with one and only one record. The value doesn't
    > matter.
    > 4. Construct a query similar to the one below for batch updates:
    >
    > UPDATE
    > Q
    > SET
    > Q.QuestionPos = T.QuestionPos
    > FROM
    > tblQuestion AS Q,
    > [
    > SELECT 1 AS QuestionID, 3 AS QuestionPos FROM DUAL UNION ALL
    > SELECT 2, 4 FROM DUAL UNION ALL
    > SELECT 3, 5 FROM DUAL UNION ALL
    > SELECT 4, 1 FROM DUAL UNION ALL
    > SELECT 5, 2 FROM DUAL
    > ]. AS T
    > WHERE
    > Q.QuestionID = T.QuestionID
    >
    > {SQL Server}
    > UPDATE
    > Q
    > SET
    > Q.QuestionPos = T.QuestionPos
    > FROM
    > tblQuestion AS Q,
    > (
    > SELECT 1 AS QuestionID, 3 AS QuestionPos UNION ALL
    > SELECT 2, 4 UNION ALL
    > SELECT 3, 5 UNION ALL
    > SELECT 4, 1 UNION ALL
    > SELECT 5, 2
    > ) AS T
    > WHERE
    > Q.QuestionID = T.QuestionID
    >
    > You may also be interested in the IMS Global Learning Consortium
    > Question And Test Interoperability (QTI) specification. Here's a link:
    > [url]http://www.imsglobal.org/question[/url]
    >
    > HTH
    > -Chris Hohmann
    >
    >
    >

    A Ratcliffe Guest

  8. #8

    Default Re: Updating records in order (into an order)

    "A Ratcliffe" <aratcliffearchiNOSPAMmagic.net> wrote in message
    news:%23dBah5gmDHA.3316TK2MSFTNGP11.phx.gbl...
    > Hi, thanks for your reply. Could you break down what your code is
    doing?
    > Maybe its just me being slow-minded this morning, but I can't get my
    head
    > round what your system is doing, and I need to if I'm to build it into
    my
    > ASP page.
    Sure. Before doing so, let me explain two somewhat obscure concepts:

    1. The DUAL table:
    The DUAL table is a carry-over from my experience with Oracle databases.
    In Oracle, there is a system table called DUAL which, as I described is
    a table with one field containing only one row of data. This is useful
    for evaluating single expressions, testing functions, etc... For
    example:

    SELECT 2 + 2 AS SimpleMath FROM DUAL

    The point here is that we don't care what's in DUAL, so long as we can
    depend on the fact that there is one and only one record. That's what
    allows us to retrieve a single, non-table based value. Having said that,
    we can use the DUAL table in a UNION query to produce an arbitrary set
    of data of our own choosing.

    2. Undoented Subqueries in MS Access 97
    You did not indicate what version of Access you were using so I coded
    for the least common denominator. The code I provided should work in
    both Access2K and Access97. You may know that Access2K supports
    subqueries, i.e. queries within queries, however, Access97 does not, or
    does it?.....

    Access97 does in fact have undoented support for subqueries.
    Specifically, the syntax for embedding a subquery into a query is to
    wrap the subquery in square-brackets, followed by a period. Note, the
    period is important an can be easily missed.

    OK, so here's the breakdown (I put my comments in curly braces):

    {Start of the UPDATE statement, indicating which table is to be updated.
    Namely, the tblQuestion table which is aliased as Q}
    UPDATE
    Q

    {The SET clause, indicating the field/value pair to update. Here, we are
    setting the QuestionPos field in tblQuestion equal the value of the
    QuestionPos field in the temporary table we create below that is aliased
    as T}
    SET
    Q.QuestionPos = T.QuestionPos

    {This is were we alias tblQuestion as Q}
    FROM
    tblQuestion AS Q,

    {Here's where the magic happens. This is our embedded subquery. We
    unioned together a list of the QuestionID's along with their new
    QuestionPos values. We then alias the temporary subquery as T.}
    [
    SELECT 1 AS QuestionID, 3 AS QuestionPos FROM DUAL UNION ALL
    SELECT 2, 4 FROM DUAL UNION ALL
    SELECT 3, 5 FROM DUAL UNION ALL
    SELECT 4, 1 FROM DUAL UNION ALL
    SELECT 5, 2 FROM DUAL
    ]. AS T

    {The WHERE clause to associate each record in the tblQuestion table with
    its associated record in the temporary subquery. We could have just as
    easily used a JOIN clause.}
    WHERE
    Q.QuestionID = T.QuestionID


    By way of ogy, the above query accomplishes the following is one
    easy step:
    1. Create a table called NewQuestionPosValues
    2. Populate the new table with a separate INSERT statement for each row.
    3. Create an UPDATE query that updates the tblQuestionPos with its
    associated value in NewQuestionPosValues.
    4. Delete the NewQuestionPosValues table.


    HTH
    -Chris Hohmann


    Chris Hohmann Guest

Similar Threads

  1. Tab Order always greyed out, need to redefine order but can't
    By Dominic_De_Lello@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 5
    Last Post: July 14th, 05:06 AM
  2. Updating Sort Order on a field?
    By vkunirs in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 19th, 02:54 PM
  3. Replies: 10
    Last Post: November 2nd, 10:32 AM
  4. change/swap order of two records in access
    By rey in forum ASP Database
    Replies: 2
    Last Post: November 9th, 12:14 AM
  5. Records in Ascending Order
    By Kevin Spencer in forum Macromedia Dreamweaver
    Replies: 1
    Last Post: July 27th, 10:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139