Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Help with update query

Posted on 2012-08-15
9
Medium Priority
?
699 Views
Last Modified: 2012-08-21
Hello EE,

I have a TreeView in vb.net and when I take a node and drag it over another node..I want the order to be change. it works well in vb.net because I remove and insert the node correctly. but in the database it needs to change also becasue If I reload the Treeview i need to see the new order.


My table looks like this:

Code   Description  Sequence

ABC          Test              1
ABC1        Test1            2
ABC2        Test2            3
ABC3        Test3            4


Lets say I take the node ABC and Drag on the node ABC3
I need ABC in the table to be "after" ABC3 I would like to modify the Sequence that the result is:

ABC1          1
ABC2          2
ABC3          3
ABC            4

same thing reverse... lets say now we take ABC3 move to node ABC1, I need to update to that ABC3 be After ABC1  like this:

ABC1       1
ABC3       2
ABC2       3
ABC         4


how can I do a query update for that?  Im confused with the new Sequence, old sequence +1 .... -1... update the others to fit....


can you help?
thanks
0
Comment
Question by:PhilippeRenaud
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38298674
You need to know the old and new position plus code ... then apply the logic for when the entry moved down or up in the list.  Assuming the old position is in the variable oldPos and new position is in newPos and the code was movedCode ... then logic like (pseudo code):

if newPos > oldPos then
  UPDATE seq_table
  SET Sequence = CASE WHEN Code = movedCode THEN newPos
                      ELSE Sequence - 1
  WHERE Sequence BETWEEN oldPos AND newPos
else
  UPDATE seq_table
  SET Sequence = CASE WHEN Code = movedCode THEN newPos
                      ELSE Sequence + 1
  WHERE Sequence BETWEEN newPos AND oldPos
endif

Open in new window

0
 
LVL 5

Assisted Solution

by:Jason Schlueter
Jason Schlueter earned 1000 total points
ID: 38298683
You will need to know the index of the new position and whether it is being moved up or down.
Say that you are moving ABC from position 0 to position 3:
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence - 1 where sequence <= 3
update @temp set sequence = 3 where code = 'abc0'
select * from @temp order by sequence

Open in new window


If you were to move it from position 3 to 0, the comparison sign in the first update statement would be reversed (>=) and then we would add instead of subtract the sequence index.
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence + 1 where sequence >= 0
update @temp set sequence = 0 where code = 'abc3'
select * from @temp order by sequence

Open in new window

You could easily generate the SQL in a vb.net string and execute it that way.
0
 
LVL 9

Expert Comment

by:keyu
ID: 38299401
i think you have display order and on selection of specific code you want to replace it with new selected code sequence.

say as per your first example you want to interchange sequence of 'ABC' and 'ABC3'


-- first we will take input of new change sequence selected from dropdown lets say its 4 -----here i am setting it to 4
--[ You have to pas it as input in your storeprocedure ]


DEclare @newseq as int
declare @oldseq as int
declare @replacecode as varchar(255)

set @newseq=4


select @oldseq =Sequence from table_nme where code='ABC3'

select @replacecode =code from table_nme where Sequence =@newseq

Update table_nme set Sequence=@newseq where code='ABC3'
update table_nme set Sequence=@oldseq where code=@replacecode
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 38301689
Thanks!
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38302976
I am sorry but you NEED to put the boundary on the update on the updates with the +1 or -1 to limit the updates being between the old position and the new position.  This is what I did in my example.  In the code from @JasonSchlueter it only uses the boundary on the new position end.
Try the @JasonSchlueter code where it is not moving to/from a beginning i.e.

Say that you are moving ABC1 from position 1 to position 2:
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence - 1 where sequence <= 2
update @temp set sequence = 2 where code = 'abc1'
select * from @temp order by sequence

Open in new window


or ... move it from position 2 to 1
DECLARE @temp TABLE(
  code        CHAR ( 4 ),
  description CHAR ( 5 ),
  sequence    INT) 


insert into @temp values
('abc0','test0',0),
('abc1','test1',1),
('abc2','test2',2),
('abc3','test3',3)

select * from @temp

update @temp set sequence = sequence + 1 where sequence >= 1
update @temp set sequence = 1 where code = 'abc2'
select * from @temp order by sequence

Open in new window

0
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 38303069
oh youre right
i will reopen to fix points
0
 
LVL 5

Expert Comment

by:Jason Schlueter
ID: 38305216
I'm glad that I could assist.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38305260
Another possibility would be to do the original Sequence assignments 10000 apart, then just split the difference when moving it.  You can use ROW_NUMBER() OVER(...) to assign sequential numbers when subsequently reading the table.

ABC          Test             10000
ABC1        Test1            20000
ABC2        Test2            30000
ABC3        Test3            40000

To move ABC to between ABC2 and ABC3:

UPDATE dbo.tablename
SET sequence = 35000
WHERE code = 'ABC'
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question