I have a user table in my database. It consists of the following fields:
UID (varchar)
datecreated (varchar)
useremail (varchar)
name (varhcar)
address (varchar)
[several other fields that are not important]
for whatever reason I did not use an autonumber at the time I created the table. SQL will not allow it to be converted ( i get error messages). Please do not focus in on that. I'm not looking for a solution involving breaking my index and relationships. I have two other tables that are tied to this one based on the UID field.
I have used a manual index to generate my userID's. This works well for the most part. Another table holds numbers, one to a record. I retrieve the number, increment it and set the next number to be used back in the table.
The catch, and my problem, is that sometimes numbers would get skipped if there was a data writing error. A combination of bad input, poor error control, and some particularly vicious robots that have created 20 and 30 empty accounts at a whack on my particular site.
The end result is that I have data that looks like this:
UID - Name
1 PersonA
5 PersonB
6 PersonC
10 PersonD
What I want to do is renumber my UID column. I propose to use the datecreated column to hold a new ID number. Then I'll update UID=datecreated and then set datecreated back to null. Datecreated was intended for a purpose and has not been implemented so the column is empty.
I have tested this on a single account using a single update statement. It worked fine and didn't break any of the management tools we use to manage users.
Where I am stumped is the code to generate the sequential numbers using SQL.
I've searched and can't find anything to use as an example.
If I were writing it in Basic..... or VBscript it would go:
A hashed up version of what I want to do is this:
for a=1 to numrows(recordset)
set datecreated=a
next
I suppose I could use a cursor.... it's a one time problem to be fixed once. Just wondering if anyone has a better way.
We fixed the data entry problem by adding some controls and implementing CAPTCHA to stop the robots.
Start Free Trial
