Change Sort_Order up/down SP problem

Hi,

I'm having a problem with some stored procedures which is supposed to change the position of an item in a list to move it up or down. I've written two stored procedures - one for up or down - but they work intermittenly or not at all and I'm not sure why. Could someone help me out? I'm sure my logic is messed up somewhere and probably more complicated than it needs to be.

Thanks!

My table structure:

SpecialNeedID (PK, int)
SpecialNeed (varchar)
Sort_Order (int)

My stored procedures:

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
(
@.SpecialNeedID int
)
AS

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT TOP 1 Sort_Order-10 FROM lkpSpecialNeeds WHERE Sort_Order >
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID))
WHERE SpecialNeedID=
(SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order >
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID) ORDER BY Sort_Order)
END

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT Sort_Order+10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID)
WHERE SpecialNeedID=@.SpecialNeedID
END

==================================================

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsDown]
(
@.SpecialNeedID int
)
AS

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT TOP 1 Sort_Order+10 FROM lkpSpecialNeeds WHERE Sort_Order <
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID))
WHERE SpecialNeedID=
(SELECT TOP 1 SpecialNeedID FROM lkpSpecialNeeds WHERE Sort_Order <
(SELECT Sort_Order FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID) ORDER BY Sort_Order)
END

BEGIN
UPDATE lkpSpecialNeeds SET Sort_Order=(
SELECT Sort_Order-10 FROM lkpSpecialNeeds WHERE SpecialNeedID=@.SpecialNeedID)
WHERE SpecialNeedID=@.SpecialNeedID
ENDWell, that is messy. For one thing, you've got some TOP statements in there without ORDER BYs, making them meaningless or even misleading.

Explain what you are trying to do. It looks like you are trying to swap the places of two entries 10 records apart, which doesn't make sense to me. I'd think you would want to be swapping the places of neighboring records.

Maybe some sample data would help clarify. Regardless, I think the final solution which be much simpler than what you have come up with so far.|||Thanks for the reply. Here's some sample data:

SpecialNeedID SpecialNeed Sort_Order
---------------
1 Blind 10
2 Deaf 20
3 Autistic 30

These items are arranged in a list - the last column on the list are two buttons: up and down. Using these, the admins should be able to move items up and down to dictate the order they appear in drop-down lists.|||OK, why are you incrementing the sort order by 10? That certainly leaves open a lot of opportunities for bad data leading to unpredictable results from your resorting procedure. Why not just increment by 1 with integers?|||try this... assuming Sort_Order is numeric... need a check for the top one

CREATE PROCEDURE [dbo].[up_SortSpecialNeedsUp]
( @.CurID int )
AS

declare @.PrevId int

set @.PrevId = (select top 1 SpecialNeedID from lkpSpecialNeeds where Sort_Order < (select Sort_Order from lkpSpecialNeeds where SpecialNeedID=@.CurId) order by Sort_Order desc)
update lkpSpecialNeeds set Sort_Order=Sort_Order-10 where SpecialNeedID = @.CurId
update lkpSpecialNeeds set Sort_Order=Sort_Order+10 where SpecialNeedID = @.PrevId|||try this... assuming Sort_Order is numeric... need a check for the top one

This worked perfectly, thanks so much!

change recordset position

Hi
Table Structure
company name
Phone No
Products
Example
Company Name Phone No Products
Sri balaji Enterprises 26554432 Advertising Agents
KarthicK Call Taxi 43215434 Call Taxi
Chitra Electricals 65437654 Electricals
Udhayam Call Taxi 24358901 Call Taxi
Annamalaiyar Advertising 67542317 Advertising Agents
Mathi Vehicles 23456789 Automobiles
Chennai Media 45326754 Advertising Agents
OUTPUT FORM
Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
First Combo Box loads
Advertising Agents
Automobiles
Call Taxi
Eletricals
if i click the Advertising agents in first combo box the 2nd combox will
shows the company names of who are all dealing Advertising.
so the second combo box shows
Sri balaji Enterprises
Annamalaiyar Advertising
Chennai Media.
As the same when i click call taxi
Karthick Call taxi
Udhayam Call taxi
Its working...But my problem is..........
Next time when i click Advertising agents the second combo box have to show
Annamalaiyar Advertising(2nd record have 2 come to first)
Chennai Media.(3rd record have 2 come second)
Sri balaji Enterprises(1st record have 2 go last)
same when i click call taxi
Udhayam Call Taxi(2nd record have 2 come to first)
Karthick Call taxi(1st record have 2 go last)
i dont know how to do...if anybody knows help me to do that...
Thanks in advance.Hi
"nila_vaanan" wrote:
> Hi
> Table Structure
> company name
> Phone No
> Products
> Example
> Company Name Phone No Products
> Sri balaji Enterprises 26554432 Advertising Agents
> KarthicK Call Taxi 43215434 Call Taxi
> Chitra Electricals 65437654 Electricals
> Udhayam Call Taxi 24358901 Call Taxi
> Annamalaiyar Advertising 67542317 Advertising Agents
> Mathi Vehicles 23456789 Automobiles
> Chennai Media 45326754 Advertising Agents
> OUTPUT FORM
> Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
> First Combo Box loads
> Advertising Agents
> Automobiles
> Call Taxi
> Eletricals
> if i click the Advertising agents in first combo box the 2nd combox will
> shows the company names of who are all dealing Advertising.
> so the second combo box shows
> Sri balaji Enterprises
> Annamalaiyar Advertising
> Chennai Media.
> As the same when i click call taxi
> Karthick Call taxi
> Udhayam Call taxi
> Its working...But my problem is..........
> Next time when i click Advertising agents the second combo box have to show
> Annamalaiyar Advertising(2nd record have 2 come to first)
> Chennai Media.(3rd record have 2 come second)
> Sri balaji Enterprises(1st record have 2 go last)
> same when i click call taxi
> Udhayam Call Taxi(2nd record have 2 come to first)
> Karthick Call taxi(1st record have 2 go last)
> i dont know how to do...if anybody knows help me to do that...
> Thanks in advance.
>
You don't say how this listbox is being populated, but SQL Server does not
guaranteed and the order of records returned by a SQL query unless you
specify an ORDER BY clause on your statement. Check out the section on ORDER
BY in books online.
John

change recordset position

Hi
Table Structure
company name
Phone No
Products
Example
Company Name Phone No Products
Sri balaji Enterprises 26554432 Advertising Agents
KarthicK Call Taxi 43215434 Call Taxi
Chitra Electricals 65437654 Electricals
Udhayam Call Taxi 24358901 Call Taxi
Annamalaiyar Advertising 67542317 Advertising Agents
Mathi Vehicles 23456789 Automobiles
Chennai Media 45326754 Advertising Agents
OUTPUT FORM
Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
First Combo Box loads
Advertising Agents
Automobiles
Call Taxi
Eletricals
if i click the Advertising agents in first combo box the 2nd combox will
shows the company names of who are all dealing Advertising.
so the second combo box shows
Sri balaji Enterprises
Annamalaiyar Advertising
Chennai Media.
As the same when i click call taxi
Karthick Call taxi
Udhayam Call taxi
Its working...But my problem is..........
Next time when i click Advertising agents the second combo box have to show
Annamalaiyar Advertising(2nd record have 2 come to first)
Chennai Media.(3rd record have 2 come second)
Sri balaji Enterprises(1st record have 2 go last)
same when i click call taxi
Udhayam Call Taxi(2nd record have 2 come to first)
Karthick Call taxi(1st record have 2 go last)
i dont know how to do...if anybody knows help me to do that...
Thanks in advance.
Hi
"nila_vaanan" wrote:

> Hi
> Table Structure
> company name
> Phone No
> Products
> Example
> Company Name Phone No Products
> Sri balaji Enterprises 26554432 Advertising Agents
> KarthicK Call Taxi 43215434 Call Taxi
> Chitra Electricals 65437654 Electricals
> Udhayam Call Taxi 24358901 Call Taxi
> Annamalaiyar Advertising 67542317 Advertising Agents
> Mathi Vehicles 23456789 Automobiles
> Chennai Media 45326754 Advertising Agents
> OUTPUT FORM
> Here i am using 2 combo boxes. first box list out all distinct products.(i.e)
> First Combo Box loads
> Advertising Agents
> Automobiles
> Call Taxi
> Eletricals
> if i click the Advertising agents in first combo box the 2nd combox will
> shows the company names of who are all dealing Advertising.
> so the second combo box shows
> Sri balaji Enterprises
> Annamalaiyar Advertising
> Chennai Media.
> As the same when i click call taxi
> Karthick Call taxi
> Udhayam Call taxi
> Its working...But my problem is..........
> Next time when i click Advertising agents the second combo box have to show
> Annamalaiyar Advertising(2nd record have 2 come to first)
> Chennai Media.(3rd record have 2 come second)
> Sri balaji Enterprises(1st record have 2 go last)
> same when i click call taxi
> Udhayam Call Taxi(2nd record have 2 come to first)
> Karthick Call taxi(1st record have 2 go last)
> i dont know how to do...if anybody knows help me to do that...
> Thanks in advance.
>
You don't say how this listbox is being populated, but SQL Server does not
guaranteed and the order of records returned by a SQL query unless you
specify an ORDER BY clause on your statement. Check out the section on ORDER
BY in books online.
John

change recordset position

Hi
Table Structure
company name
Phone No
Products
Example
Company Name Phone No Products
Sri balaji Enterprises 26554432 Advertising Agents
KarthicK Call Taxi 43215434 Call Taxi
Chitra Electricals 65437654 Electricals
Udhayam Call Taxi 24358901 Call Taxi
Annamalaiyar Advertising 67542317 Advertising Agents
Mathi Vehicles 23456789 Automobiles
Chennai Media 45326754 Advertising Agents
OUTPUT FORM
Here i am using 2 combo boxes. first box list out all distinct products.(i.e
)
First Combo Box loads
Advertising Agents
Automobiles
Call Taxi
Eletricals
if i click the Advertising agents in first combo box the 2nd combox will
shows the company names of who are all dealing Advertising.
so the second combo box shows
Sri balaji Enterprises
Annamalaiyar Advertising
Chennai Media.
As the same when i click call taxi
Karthick Call taxi
Udhayam Call taxi
Its working...But my problem is..........
Next time when i click Advertising agents the second combo box have to show
Annamalaiyar Advertising(2nd record have 2 come to first)
Chennai Media.(3rd record have 2 come second)
Sri balaji Enterprises(1st record have 2 go last)
same when i click call taxi
Udhayam Call Taxi(2nd record have 2 come to first)
Karthick Call taxi(1st record have 2 go last)
i dont know how to do...if anybody knows help me to do that...
Thanks in advance.Hi
"nila_vaanan" wrote:

> Hi
> Table Structure
> company name
> Phone No
> Products
> Example
> Company Name Phone No Products
> Sri balaji Enterprises 26554432 Advertising Agents
> KarthicK Call Taxi 43215434 Call Taxi
> Chitra Electricals 65437654 Electricals
> Udhayam Call Taxi 24358901 Call Taxi
> Annamalaiyar Advertising 67542317 Advertising Agents
> Mathi Vehicles 23456789 Automobiles
> Chennai Media 45326754 Advertising Agents
> OUTPUT FORM
> Here i am using 2 combo boxes. first box list out all distinct products.(i
.e)
> First Combo Box loads
> Advertising Agents
> Automobiles
> Call Taxi
> Eletricals
> if i click the Advertising agents in first combo box the 2nd combox will
> shows the company names of who are all dealing Advertising.
> so the second combo box shows
> Sri balaji Enterprises
> Annamalaiyar Advertising
> Chennai Media.
> As the same when i click call taxi
> Karthick Call taxi
> Udhayam Call taxi
> Its working...But my problem is..........
> Next time when i click Advertising agents the second combo box have to sho
w
> Annamalaiyar Advertising(2nd record have 2 come to first)
> Chennai Media.(3rd record have 2 come second)
> Sri balaji Enterprises(1st record have 2 go last)
> same when i click call taxi
> Udhayam Call Taxi(2nd record have 2 come to first)
> Karthick Call taxi(1st record have 2 go last)
> i dont know how to do...if anybody knows help me to do that...
> Thanks in advance.
>
You don't say how this listbox is being populated, but SQL Server does not
guaranteed and the order of records returned by a SQL query unless you
specify an ORDER BY clause on your statement. Check out the section on ORDER
BY in books online.
John