I have what probably appears to most SQL literate folks a really silly
question.  However, I've looked through several SQL books, and can't
seem to find a situation described which approximates mine, and
therefore, haven't found an answer to my question.

We've got a health education website, where users create their own
logins, then receive tailored information based on who they are (they
answer some questions along the way).

So, no two logins can be the same - each needs to be a unique
identifier.  My question has to do with the create logins SQL routine.

Here's how the routine works.  Users fill out a form where they choose
login and password.

Upon form posting, the ASP script does two simple things.

1.  Check login script.  It opens a database connection, and checks to
see if someone is already assigned to the database with the login
they've chosen.  If someone already exists with that login, it informs
the user and asks them to try a different one.

2.  Insert login script.  If no one already exists with the login and
password they've chosen, it inserts a new record in to the database with
the login they've chosen, and lets the user know this.  The user can now
come and go at the site as little or much as they want, with the login
they've been assigned.

So, now the question, and it really shows my shortcomings with SQL.

Let's say hypothetically two users are creating logins for the first
time ever.  They both happen to want to choose the same login.  They
both click their "submit" buttons at the same time.  Is it possible for
both of the users to end up with the same login - if they're both doing
this at exactly the same time (highly improbable in our situation, but
nonetheless, I want to do this right), can both of these people get
through the check login script, and then execute the insert login
script, at the same time?  I assume yes.

If this is theoretically possible, what is the best way to go about
making sure this doesn't happen?  Should I lock the recordset once one
user starts the check login routine?  Is the best way to handle this by
using one of the locking cursors - if so, which is best for this type of
scenario?

Or, what about using transactions?  They don't seem to lock the
recordset, unless I explicitly the transaction to do so.  Most of the
SQL stuff I've done so far has not needed transactions - can anyone tell
them if this is an instance where a transaction would be the way to go?

Or, is there some other method I haven't found that would even be
better?

I appreciate any and all help.  Direct email is probably best for me...

Sincerely,

Bryan

Sent via Deja.com http://www.*-*-*.com/
Before you buy.

David

AS

Begin


end
else
Begin



end

Then in the vb code use and ado command with one output parameter something
like
dim cmd as new adodb.command
dim iExists as integer

with cmd
cmd.CommandText = "sp_MyProcedure"
cmd.CommandType = adCmdStoredProc
cmd.Name = "sp_MyProcedure"

'assuming you have a valid connection called cnn
Set cmd.ActiveConnection = cnn

'Assuming you have two textboxes named txtNewLogin and txtNewPassword.
cnn.sp_MyProcedure txtNewLogin.Text, txtNewPassword.Text

End With
Set cmd = Nothing
Set cnn = Nothing
select case iExists
 case 0
  'there already exists a login by that name do your thing to tell the user
to choose another one.
 case 1
  'A new login was inserted into the database direct the user to the next
step.
 case else
end select