Hi,

I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.

What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.

Ideas? Are there (build-in) funtions for this?

Thank!

You could easily write a stored proc to do this.  One of the many
wonderful things about the UPDATE statement in the SQL language is that
it is an all-at-once operation.  You can increment the vieworder for a
row and either increment or decrement its neighbour as appropriate all
at the same time.  Try this batch:

    use tempdb
    go

    -- A little setting up...
    create table #tmp
        (
        vieworder int not null,
        blah char(1) primary key clustered
        )

    insert into #tmp (vieworder, blah) values (1,  'A')
    insert into #tmp (vieworder, blah) values (2,  'B')
    insert into #tmp (vieworder, blah) values (3,  'C')
    insert into #tmp (vieworder, blah) values (4,  'D')
    insert into #tmp (vieworder, blah) values (5,  'E')
    insert into #tmp (vieworder, blah) values (6,  'F')
    insert into #tmp (vieworder, blah) values (7,  'G')
    insert into #tmp (vieworder, blah) values (8,  'H')
    insert into #tmp (vieworder, blah) values (9,  'I')
    insert into #tmp (vieworder, blah) values (10, 'J')
    -- --------------------------------


    -- the row to shuffle
    declare @id int
    set @id = 10



    -- The original result set
    select * from #tmp order by vieworder


    -- Shuffle up
    update #tmp
    set vieworder =
        case
            when (vieworder = @id)     then (vieworder + 1)
            when (vieworder = @id + 1) then (vieworder - 1)
        end
    where (vieworder = @id or vieworder = @id + 1)             -- Only
    shuffle the 2 rows in question
    and exists (select * from #tmp where vieworder = @id + 1)  -- Just
    to make sure you don't shuffle a row "off the end"


    -- Let's see it after the shuffle up
    select * from #tmp order by vieworder


    -- Shuffle down
    update #tmp
    set vieworder =
        case
            when (vieworder = @id)     then (vieworder - 1)
            when (vieworder = @id - 1) then (vieworder + 1)
        end
    where (vieworder = @id or vieworder = @id - 1)             -- Only
    shuffle the 2 rows in question
    and exists (select * from #tmp where vieworder = @id - 1)  -- Just
    to make sure you don't shuffle a row "off the end"

    -- Let's see it after the shuffle down
    select * from #tmp order by vieworder


    -- Tidy up
    drop table #tmp


Embed that kind of logic inside a stored procedure and then you just
call the stored proc with the appropriate parameters (a direction to
shuffle and a baseline row to shuffle along with its neighbour) from
your webpage using ADO (with a postback to reload the same webpage I guess).

Hope this helps.

Show quoteHide quote

Okay, thanks!
I will try this.

Arjen


  You could easily write a stored proc to do this.  One of the many wonderful things about the UPDATE statement in the SQL language is that it is an all-at-once operation.  You can increment the vieworder for a row and either increment or decrement its neighbour as appropriate all at the same time.  Try this batch:

    use tempdb
    go

    -- A little setting up...
    create table #tmp
        (
        vieworder int not null,
        blah char(1) primary key clustered
        )

    insert into #tmp (vieworder, blah) values (1,  'A')
    insert into #tmp (vieworder, blah) values (2,  'B')
    insert into #tmp (vieworder, blah) values (3,  'C')
    insert into #tmp (vieworder, blah) values (4,  'D')
    insert into #tmp (vieworder, blah) values (5,  'E')
    insert into #tmp (vieworder, blah) values (6,  'F')
    insert into #tmp (vieworder, blah) values (7,  'G')
    insert into #tmp (vieworder, blah) values (8,  'H')
    insert into #tmp (vieworder, blah) values (9,  'I')
    insert into #tmp (vieworder, blah) values (10, 'J')
    -- --------------------------------


    -- the row to shuffle
    declare @id int
    set @id = 10



    -- The original result set
    select * from #tmp order by vieworder


    -- Shuffle up
    update #tmp
    set vieworder =
        case
            when (vieworder = @id)     then (vieworder + 1)
            when (vieworder = @id + 1) then (vieworder - 1)
        end
    where (vieworder = @id or vieworder = @id + 1)             -- Only shuffle the 2 rows in question
    and exists (select * from #tmp where vieworder = @id + 1)  -- Just to make sure you don't shuffle a row "off the end"


    -- Let's see it after the shuffle up
    select * from #tmp order by vieworder


    -- Shuffle down
    update #tmp
    set vieworder =
        case
            when (vieworder = @id)     then (vieworder - 1)
            when (vieworder = @id - 1) then (vieworder + 1)
        end
    where (vieworder = @id or vieworder = @id - 1)             -- Only shuffle the 2 rows in question
    and exists (select * from #tmp where vieworder = @id - 1)  -- Just to make sure you don't shuffle a row "off the end"

    -- Let's see it after the shuffle down
    select * from #tmp order by vieworder


    -- Tidy up
    drop table #tmp


  Embed that kind of logic inside a stored procedure and then you just call the stored proc with the appropriate parameters (a direction to shuffle and a baseline row to shuffle along with its neighbour) from your webpage using ADO (with a postback to reload the same webpage I guess).

  Hope this helps.

  --
  mike hodgson
  blog: http://sqlnerd.blogspot.com



  Arjen wrote:
Hi,

I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.

What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.

Ideas? Are there (build-in) funtions for this?

Thank!

Time Difference Calculations
Customers & Phones
Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum