Hi, I have two things I have been struggling with in an app I am writing.

First, I have a registration page. I want to check the username the user enters against the DB to make sure there is no other user with the same login name.

Second, On that registration page, I would like to have an auto incremental user ID number based on the previous user ID. For example. if the last student in the table as a user ID or 3 I want the next person who registers to be 4 and so on.

Any help would be appreciated.

Thanks,
krs

Does it only autoincrement when the user enters a previously entered user ID? I don't understand the second part, but the first part is easy, you create a SQL statement to check in the database, and compare against the user ID. What DB are you using, and are you checking the user against the DB, or does each user have a login set up in the database?

Brian Mains

Brian, there is no auto-increment set up at all. I am still in development phase and I am the only person in the app. Right now I just put in the ID manually.

As for the compare of User ID's, I am not clear on how to do that. I believe the beginning will be something like this:

select * from students where user_name =" & request.Querystring("user")

but not sure where to go from there. Haven't had success with if statements and queries.

I am using SQL 2000 and the ASP runs on a win 200 server.

Thanks for your help!
(Edited to follow my own advice. :))

Use SQL Server stored procedures, create a stored procedure that verifies the user ID:
Stored procedures are more efficient. Also, don't pass the user ID by querystring; allows for hacking. Are you doing forms-based login? Is this application an intranet app or internet app? If intranet, you can access their windows login through Request.ServerVariables("LOGON_USER"). Then you use ADO code to execute the stored procedure and compare the results.

Do you know about:

ADODB.Connection
ADODB.Command
ADODB.Recordset

objects? If not you will need to use these.

It is a forms based login. It is extranet, so some people will have server ID's and some will not, which is why I went with this option.

Not too familiar with stored procedures, in the past I have mostly done queries and such. How does it work? Where do I put the "if()" statement to redirect to another page if the username exists?

I currently use Command, Connection and Recordset. I have the app set up so you can edit your profile after log in. Also, Admins can Create, edit and delete users. I have security built in to the app with security levels.

just wanted to give you a little more info.

thanks Brian.

Krs

'Setup of connnection/command objects
set objRS = objCmd.Execute

if ( not objRS.EOF ) then
    'user exists
    Response.Redirect("invalidlogin.asp")
else
    'user doesn't exist, create the user
end if

Check out books online for stored procedure examples. The previous example will work in a stored procedure; stick in the code and run it in query analyzer, and it will create the stored procedure. Just make sure that the owner is dbo (add dbo to "create procedure DBO.selectUser).

For security level, when they log in, you'll want to store that in the session, or every transaction that you submit to a stored procedure, do validation at the stored procedure level. Stored procedures are very flexible, and can do more than one function.

Can this only be done from the query analyzer? Can it be done Via the web? I don't have access to the SQL Server. It's in another bulding.

Query Analyzer or Enterprise Manager, but not web (at least yet). Can you get the SQL Server client installed on your machine?

Thanks Brian, I got it to work.

:)

Krstofer