Unique Field Verification

[frontpagecustoms.com]

Does anyone have a script or code that will verify a user name or email
address is unique in a database entry form?

 

[Stefan B Rusynko]

The way to check is to query the DB for that new user name and that email address before you write the new ones to the DB
If you get a EOF the new ones were not a dupe
If you get a hit the new ones are dupes

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| Does anyone have a script or code that will verify a user name or email
| address is unique in a database entry form?
|

 

[Jon Spivey]

you can also set the field as unique in the db then try the insert, if
there's an error ask the user to chose a new name, eg
on error resume next
' try the insert
if err.number <> 0 then
' name exists, get user to chose a new one
else
' success - name doesn't exist
end if
on error goto 0

The advantage is that you're only hitting the database once rather than
twice. Obviously this only applies to access, in sql server you'd use a
stored procedure to do the insert and return success or failure as an output
param.

Cheers,
Jon

 

[tg]

Those statements will work fine to handle an insert error with SQLserver
(and probably other DB's), no stored procedures required, and it's a single
trip to the DB, here's a few lines of code that work.

str = "insert into emaillist values( '(e-mail address removed)' )"
on error resume next
' try the insert
Set RS = Conn.Execute ( str )

if err.number <> 0 then
response.write "oops, error on DB insert attempt"
'name exists, get user to choose a new one or DB not available
else
response.write "life is good, address added"
'success - address has been inserted
end if

you can also set the field as unique in the db then try the insert, if
there's an error ask the user to chose a new name, eg
on error resume next
' try the insert
if err.number <> 0 then
' name exists, get user to chose a new one
else
' success - name doesn't exist
end if
on error goto 0

The advantage is that you're only hitting the database once rather than
twice. Obviously this only applies to access, in sql server you'd use a
stored procedure to do the insert and return success or failure as an
output param.

Cheers,
Jon

The way to check is to query the DB for that new user name and that email
address before you write the new ones to the DB
If you get a EOF the new ones were not a dupe
If you get a hit the new ones are dupes

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| Does anyone have a script or code that will verify a user name or email
| address is unique in a database entry form?
|

 

[frontpagecustoms.com]

I have the database set to reject duplications, however when the user
enters their duplicate it still treats it as if they have registered
even though the database rejects it.

I need something to send an error message to the user.

Those statements will work fine to handle an insert error with SQLserver
(and probably other DB's), no stored procedures required, and it's a single
trip to the DB, here's a few lines of code that work.

str = "insert into emaillist values( '(e-mail address removed)' )"
on error resume next
' try the insert
Set RS = Conn.Execute ( str )

if err.number <> 0 then
response.write "oops, error on DB insert attempt"
'name exists, get user to choose a new one or DB not available
else
response.write "life is good, address added"
'success - address has been inserted
end if

you can also set the field as unique in the db then try the insert, if
there's an error ask the user to chose a new name, eg
on error resume next
' try the insert
if err.number <> 0 then
' name exists, get user to chose a new one
else
' success - name doesn't exist
end if
on error goto 0

The advantage is that you're only hitting the database once rather than
twice. Obviously this only applies to access, in sql server you'd use a
stored procedure to do the insert and return success or failure as an
output param.

Cheers,
Jon

The way to check is to query the DB for that new user name and that email
address before you write the new ones to the DB
If you get a EOF the new ones were not a dupe
If you get a hit the new ones are dupes
_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________
| Does anyone have a script or code that will verify a user name or email
| address is unique in a database entry form?
|

 

[tg]

How does your database reject duplications other than defining a field as
unique?
Without the error appearing how do you know the database actually rejected
it vs. the insert statement not being executed or some other issue ? Are you
seeing duplicate data in the database table?

The code I provided will produce an error if that data within that insert
statement is in the database when that insert statement is executed. My
database table was created with a "unique" specification on the field that I
wanted to be unique. (note it could be any type of data - email address,
text, name, number, etc.)

Here's a few things to try:
- create a new text table with one field - defined as unique
- create a new page with one button which calls a second page (asp)
containing the database connection and code provided
- click the button, the text should be written to the table, go back and
click it a second time - it should fail the second time
- try displaying the contents of "err.number" - the first time it should
be 0, second time not 0

I have the database set to reject duplications, however when the user
enters their duplicate it still treats it as if they have registered
even though the database rejects it.

I need something to send an error message to the user.

Those statements will work fine to handle an insert error with SQLserver
(and probably other DB's), no stored procedures required, and it's a
single
trip to the DB, here's a few lines of code that work.

str = "insert into emaillist values( '(e-mail address removed)' )"
on error resume next
' try the insert
Set RS = Conn.Execute ( str )

if err.number <> 0 then
response.write "oops, error on DB insert attempt"
'name exists, get user to choose a new one or DB not available
else
response.write "life is good, address added"
'success - address has been inserted
end if

you can also set the field as unique in the db then try the insert, if
there's an error ask the user to chose a new name, eg
on error resume next
' try the insert
if err.number <> 0 then
' name exists, get user to chose a new one
else
' success - name doesn't exist
end if
on error goto 0

The advantage is that you're only hitting the database once rather than
twice. Obviously this only applies to access, in sql server you'd use
a
stored procedure to do the insert and return success or failure as an
output param.

The way to check is to query the DB for that new user name and that
email
address before you write the new ones to the DB
If you get a EOF the new ones were not a dupe
If you get a hit the new ones are dupes

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________

| Does anyone have a script or code that will verify a user name or
email
| address is unique in a database entry form?
|