We currently have an area of our site that people must register to enter. Registrants are put into an MSSQL database. They are assigned a userid #, but the names, etc. are inserted in a random fashion. That is, id #2 does not necessarily follow id #1. Since we have a few hundreds names now, this makes it difficult to see the latest additions to the database (we are generally notified by email through the registration page, but not always).

I can find an "dbOrderBy" command or code in the .asp page that apparently sends the information to the database, but I have no idea what to change it to. (I don't find any additional command or string other than "dbOrderBy." Does anyone have any idea how to change it so that it orders by id #?

Thanks,

John Breeden
Mini Mitter Co., Inc.
http://www.minimitter.com

Ok firstly have you got the id number to auto increment?

What I would also do is store the created date in the database and whenever you add someone store a now() command

you can then in your SQL statement run something like

SELECT * FROM users ORDER BY id DESC

(Instead of desc you can use asc which makes the retrieved database output in ascending order instead of descending)

Originally posted by Rich2k
Ok firstly have you got the id number to auto increment?

What I would also do is store the created date in the database and whenever you add someone store a now() command

you can then in your SQL statement run something like

SELECT * FROM users ORDER BY id DESC

(Instead of desc you can use asc which makes the retrieved database output in ascending order instead of descending)

The id # does autoincrement.

Here's the code that I think you're talking about from the registration page (forgive me, I am not well-versed in .asp):

SQL = "SELECT TOP 1 * FROM tblUsers;"
rs.Open SQL, Conn, 1, 2
rs.AddNew
rs("Email") = request.form("txtEmail")
rs("Password") = request.form("txtPassword")
rs("FirstName") = request.form("txtFirstName")
rs("LastName") = request.form("txtLastName")
rs("Address1") = request.form("txtAddress1")
rs("Address2") = request.form("txtAddress2")
rs("City") = request.form("txtCity")
rs("State") = request.form("txtState")
rs("ZIP") = request.form("txtZIP")
rs("Country") = request.form("txtCountry")
rs("Phone") = request.form("txtPhone")
rs("Active") = 0
rs.Update
rs.Close

Is the top line what I should edit?

A little more info: From looking at where entries seem to be made in the database, it's almost like it's adding an entry where the last entry we deleted was.

Thanks for your help.

John

It seems that you are looking at the DB tables directly for the infromation you want. If you want to see the entries in order, without resorting to outputting to a page, then use Query Analyser to view the results.

SQL Server input does seem to be pretty random when you look at the tables. If you really MUST have the table ordered by ID field then you can create a clustered index on the ID to force the order.

Remember, you can only create 1 clustered index per table.

Try also running it as full SQL rather than the VB hybrid... Try INSERT INTO table (name) VALUES (value)

Either make your ID field identity type in the increments of 1 or store the current time in a field for exact time when the registration was done. You can order by any of these fields later.