Is the code (marked with *) a right way to generate a user id that's not 
already in the database, or are there better ways to do so ?


Function GenerateUserID
'Generates a user Id that is not already in the database.
Dim sUserId, IsValidNew
	IsValidNew = False
	sUserID = ""

	While NOT IsValidNew

		* sql = "SELECT MAX(UserID) from Users Where UserID = '" 
                * &  sUserID & "'"
		* Set Rs = conn.Execute (sql)
		* sKlantID = Rs + 1

		'Query how many times the UserId is in the database 
(should be 0 or 1)
		sql = "SELECT COUNT(*) FROM Users Where UserID = '" & 
sUserID & "'"
		Set Rs = conn.Execute (sql)

		If NOT (rs.EOF and rs.BOF) Then
			If Rs(0) = 0 Then
				'Does not exist
				IsValidNew = True
			Else
				'Already exists!
				IsValidNew = False
			End If

		Else
			'If if fails to perform the operation suppose it 
is correct
			'this is to avoid an infinite loop
			IsValidNew = True
		End If

		'Close an clean up recordset
		rs.Close
		set rs = nothing
	Wend
	GenerateUserID = sUserID

End Function

Does sUserID come from a request.form or querystring?  Why are u using
MAX and COUNT(*)?  Do you have non-Primary keys meaning more than one
UserID with the same value?  What database r u using MS SQL, Access or
Oracle?

-----Original Message-----
From: vincent_cpr@y... [mailto:vincent_cpr@y...]
Sent: Monday, April 29, 2002 10:01 AM
To: ASP Databases
Subject: [asp_databases] Generate a user Id that is not already in the
database ?


Is the code (marked with *) a right way to generate a user id that's not

already in the database, or are there better ways to do so ?


Function GenerateUserID
'Generates a user Id that is not already in the database.
Dim sUserId, IsValidNew
	IsValidNew =3D False
	sUserID =3D ""

	While NOT IsValidNew

		* sql =3D "SELECT MAX(UserID) from Users Where UserID =3D '"

                * &  sUserID & "'"
		* Set Rs =3D conn.Execute (sql)
		* sKlantID =3D Rs + 1

		'Query how many times the UserId is in the database
(should be 0 or 1)
		sql =3D "SELECT COUNT(*) FROM Users Where UserID =3D '" &
sUserID & "'"
		Set Rs =3D conn.Execute (sql)

		If NOT (rs.EOF and rs.BOF) Then
			If Rs(0) =3D 0 Then
				'Does not exist
				IsValidNew =3D True
			Else
				'Already exists!
				IsValidNew =3D False
			End If

		Else
			'If if fails to perform the operation suppose it

is correct
			'this is to avoid an infinite loop
			IsValidNew =3D True
		End If

		'Close an clean up recordset
		rs.Close
		set rs =3D nothing
	Wend
	GenerateUserID =3D sUserID

End Function

> Does sUserID come from a request.form or querystring?  Why are u using
MAX and COUNT(*)?  Do you have non-Primary keys meaning more than one
UserID with the same value?  What database r u using MS SQL, Access or
Oracle?

sUserID comes from a querystring. I'm using MAX to select the highest 
UserId in de database and count to see how many times the userID is in 
the database. (0 or 1). I don't have more than one UserID with the same 
value and I'm using a Access database.

OK on querystring and access DB.  Are u using a combination of
autonumber and UserID?  Like  12345, DoeJ
I use something like this to test for the userid.  In my case, I want
the userid and zipcode.  You can cut and paste and just do the insert if
the userid does not exist...
<%
Dim sName, sZip
   sName =3D Request.Form.Item("lastname")
   sZip =3D Request.Form.Item("zipcode")
   If sName =3D "" Then
      sName =3D "No Name"
   End If
   If sZip =3D "" Then
      sZip =3D "No Zip"
   End If
   Dim objConn, objRS, sQuery, scrMsg
   set objConn =3D Server.CreateObject("ADODB.Connection")
   mdbPath =3D Server.MapPath("..\member\member.mdb")
   set objConn =3D Server.CreateObject("ADODB.Connection")
   objConn.Open "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _
                     "Data Source=3D" & mdbPath & ";" & _
                     "User Id=3Dadmin;" & _
                     "Password=3D;"     
   Set objRS=3Dserver.CreateObject("ADODB.Recordset")
   sQuery =3D "select * from webmember where member =3D '" & sName & "' 
" &_
             "and zip =3D '" & sZip & "';"
   'Response.Write sQuery
   objRS.Open sQuery, objConn
  
   if objRS.EOF then
        set objRS =3D Nothing
        scrMsg =3D "<br><p><b>The Entry for..." & sName &_
        " and Zip Code " & sZip &_
        "<hr><br><br>is NOT valid<br>Please go back and try again or
email Webmaster."
        Response.Write scrMsg  
   else
        Response.Redirect "membermenu.htm"
   end if

   objConn.Close
   set objConn =3D Nothing
%>

-----Original Message-----
From: vincent_cpr@y... [mailto:vincent_cpr@y...]
Sent: Monday, April 29, 2002 10:35 AM
To: ASP Databases
Subject: [asp_databases] RE: Generate a user Id that is not already in
the database ?


> Does sUserID come from a request.form or querystring?  Why are u using
MAX and COUNT(*)?  Do you have non-Primary keys meaning more than one
UserID with the same value?  What database r u using MS SQL, Access or
Oracle?

sUserID comes from a querystring. I'm using MAX to select the highest
UserId in de database and count to see how many times the userID is in
the database. (0 or 1). I don't have more than one UserID with the same
value and I'm using a Access database.

Have you ever heard of "autonumber"?

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <vincent_cpr@y...>
Subject: [asp_databases] Generate a user Id that is not already in the
database ?


: Is the code (marked with *) a right way to generate a user id that's not
: already in the database, or are there better ways to do so ?
:
:
: Function GenerateUserID
: 'Generates a user Id that is not already in the database.
: Dim sUserId, IsValidNew
: IsValidNew = False
: sUserID = ""
:
: While NOT IsValidNew
:
: * sql = "SELECT MAX(UserID) from Users Where UserID = '"
:                 * &  sUserID & "'"
: * Set Rs = conn.Execute (sql)
: * sKlantID = Rs + 1
:
: 'Query how many times the UserId is in the database
: (should be 0 or 1)
: sql = "SELECT COUNT(*) FROM Users Where UserID = '" &
: sUserID & "'"
: Set Rs = conn.Execute (sql)
:
: If NOT (rs.EOF and rs.BOF) Then
: If Rs(0) = 0 Then
: 'Does not exist
: IsValidNew = True
: Else
: 'Already exists!
: IsValidNew = False
: End If
:
: Else
: 'If if fails to perform the operation suppose it
: is correct
: 'this is to avoid an infinite loop
: IsValidNew = True
: End If
:
: 'Close an clean up recordset
: rs.Close
: set rs = nothing
: Wend
: GenerateUserID = sUserID
:
: End Function