I have one database running several sites, with one 'user' table.
Each site registration is based around the users email address and password.
Each site uses a global application variable to identify the site. So when a user registers, the application variable for that site is added to the customer table.
I want to allow a user to register using the same email address if they so wish, but not on the same site.
So if a user wants to register firstname.lastname@example.org on site 1 they can also register email@example.com on site 2. But they cannot register the same email address on the same site.
I can check to see if the user's email address exists in the database using the code below. The registration page submits to itself and requests the email text field to check against.
if not CustomerRS.eof then response.redirect "UserTaken.asp
At the same time, i need to check if the email address is already registered on that particular site.
Going to make a pile of assumptions here as I have nothing else to go on....
Lets assume you have three tables. Customer for storing user details, Site for storing site details and CustomerSite for storing what customers as registered to what sites.... you could have two tables, customer and site with multiple customer records if they register at mutliple sites or you might have one table that has a site field in it that is a free form char value, it doesn't really matter.
Lets assume CustomerSite stores to email address and the site name
emailAddress = Request("CustomerEmail")
site = Application("siteName")
sqlString = "select CustomerEmail from CustomerSite where Customer.Site = '" + site "' and CustomerEmail = '" + emailAddress + "'"
set conn = Server.CreateObject("ADODB.Connection")
set customerRS = conn.OpenRecordset(sqlString)
' customer exists with same email in same site
' customer does not exist
With the assumptions and some code corrections the above should do what you want.
Alternatively you could tell us more about your database tables in question and the sql you are using to build your Customer recordset in your example.