Hi there

I was wondering if somebody could help me with the following problem. I am using ASP and SQL Server.

I have a table in SQl Server which contains product information. One of the columns is called OrderNo and is an int field. On my simple update page the administrator chooses a value for OrderNo (ranging from 1st to 10th, or 1 to 10) and on the front end the recordset is ordered by this OrderNo field. So, the product with OrderNo=1 will display first, then OrderNo=2 etc. This is OK but when I add a new record to the database using my insert page, I then have to change the Order of all the records in the database.

What I would like to do is this:
Have a results page which contains all records in my database, and in the column for OrderNo, have a dropdown box in each record. I can then change the order of all the products from this one page and then click on one update button which would update all the records accordingly.

BUT, I don't know how to write the stored procedure/ASP to go this.

Can anyone help me out?

Thanks in advance

Lucy

Why not use a query that utilizes a sort?? The order of entry into the data table is irrelevant...the query will look at the data and sort it for you...that is precisely the type of function a query is designed to perform

John

If the new product slots in and just bumps the other up, then why not just set the order for the item you are adding and before you do an insert update all the order fields, for entries whose order field is greater than or equal to the one you are adding, by adding 1 therefore shifting all the orders up one, then dot eh insert.

so if you were adding an item at 3rd in the order you would first of all update all the products which had an order number of 3 or above by adding 1 to them and then you would insert the new one at order position 3.

OrderNo is the number of items? If that is what it is used for, you should use another field such as trackno to use for the query and sort on that. Whenever you want to add a new order, increase the the trackno by 1...example:

cmd = "select max(trackno) AS Lasttix FROM dbfname"
set rs = cn.Execute ( cmd )
Nexttix = rs("Lasttix") + 1