Update Column Serial Wise

cancer10 · October 22nd, 2006, 11:33 PM

There are 2 columns in 1 database for Id and name but the serial number is random.

I want to update those serial number in serial order, how to?

Like

id Name
------------
1 Shouvik
5 Joe
6 Mathew
3 John
4 James
2 Jack

I want to update it like

id Name
------------
1 Shouvik
2 Joe
3 Mathew
4 John
5 James
6 Jack

if i code it like

Code:

for x=1 to 4
conndb.execute "update tblcontest set id=" & x 
rs2.movenext
next

This way its updating all the colum with the number 4 only, any ideas?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 23rd, 2006, 07:38 AM

Actually, your command changes the ID column to 1, 2, 3 and 4, 4 is just the last number in the loop. In anycase your post doesnt make much sense to me.

You update command is setting the ID column to value of x, but the way I read this is you want to update the columns in sequential order. What I think you want to do is:

UPDATE tblContest set id=[something] WHERE id=" & x

This will update ID 1 first, ID 2 second etc.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

cancer10 · October 23rd, 2006, 08:35 AM

Does work that way either.... :(

Anyone else??

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 23rd, 2006, 08:39 AM

Humor me. Inside your loop do a

response.write x

Your output should be 1 2 3 4

In which case the update statements will be:
UPDATE tblContest set id=[something] WHERE id=1
UPDATE tblContest set id=[something] WHERE id=2
UPDATE tblContest set id=[something] WHERE id=3
UPDATE tblContest set id=[something] WHERE id=4

Also, rs2.movenext, what is that for? MoveNext is for a recordset you dont have a resultset returned when you execute an update.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

cancer10 · October 23rd, 2006, 08:41 AM

What should be there where u placed [something]?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 23rd, 2006, 08:43 AM

....the value you are trying to change id to.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

cancer10 · October 23rd, 2006, 10:00 AM

Like this?

Code:

for x=1 to rs2.recordcount
conndb.execute "update tblcontest set id=" & x " where id=" & x
next

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 23rd, 2006, 10:01 AM

if your trying to change the id to the value of x, yes.
But, thing is, it wont change the order of your database. You actually will update 0 rows because you saying:

Change ID to this value where it equals the same value.

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature

cancer10 · October 23rd, 2006, 10:36 AM

Then wot do I do to make it work?

-----------------------------------------------
www.chargertek.in - Cheapest WebHosting

dparsons · October 23rd, 2006, 10:46 AM

Set your ID column to an auto count field for one, then all the values would be sequential ;]

In anycase loop through your recordset, not a for to loop, and call your update.

Dim i
i = 1
While not rs.eof
x = rs("id")
update tblcontest set id=" & i " where id=" & x
//execute above command
i = i + 1
Wend

-------------------------
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.

^^Thats my signature