stopping users registering with the same username?

[lawtonl]

Hi, i've got a asp page to register a user and check that the username has
not been previously registered, heres the code i have ... that doesnt work


<SCRIPT LANGUAGE=vbscript RUNAT=server>


'Adds a record to the table

Dim SQL 'A variable for the SQL statement
Dim ObjConn 'A connection varaible
Dim ObjRS


Set ObjConn = Server.CreateObject("ADODB.Connection")
Set ObjRS = Server.CreateObject("ADODB.Recordset")




rsSQL = "SELECT * FROM tblUsers WHERE strUser='" & Request.Form("strUser") &
"' "

SQL = "INSERT INTO tblUsers "
SQL = SQL & "(strUser, strPwd, strEmail) "
SQL = SQL & "VALUES ('"& Request.Form("strUser") & "', '" &
Request.Form("strPwd") & "','" & Request.Form("strEmail") & "')"

ObjRS.Open rsSQL, "DSN=Project"

If ObjRS.RecordCount > 1 Then

Response.Redirect ("retry.html")

Else
ObjConn.Open "DSN=project"
ObjConn.Execute SQL

End If

ObjConn.Close
Set ObjConn = nothing

Response.Redirect("ok.html")

</script>

i am going the right way about it?
if so where have i gone wrong?
if not could someone please point me in the right direction


any help is appeciated
Regards

 

[only me]

Dim SQL 'A variable for the SQL statement
Dim ObjConn 'A connection varaible
Dim ObjRS

Set ObjConn = Server.CreateObject("ADODB.Connection")
rsSQL = "SELECT * FROM tblUsers WHERE strUser='" & Request.Form("strUser") &
"' "
ObjRS = ObjConn.execute(sql) ' check for existing user
if ObjRS.EOF = True then ' it's OK its a new user so add them
SQL = "INSERT blah blah
ObjConn.execute(SQL)
else ' reject that user
response.redirect "retry.html"
end if

I'd do it like this
just my 2p worth

 

[martin]

plus why not specify the field rather than use the * in the Select, those
*

can bite you know (http://www.aspfaq.com/show.asp?id=2188)

or just use count(*)