|Written for||Scriptwriters and survey authors|
How can we be sure the email provided by the respondent is valid - that is not just following the firstname.lastname@example.org syntax but fully registered to a real address?
The classic way is to send an email to the respondent in which they are asked to click on a link. The link usually updates a database and that database is then read to ensure the address is live.
The Structure of the survey will look like this:
We will use the Seed as a unique respondent identifier to send parameters to an .asp page and to write / retrieve data to an external database. The seed is a unique number among all interviews across all surveys (in IT, a seed is a number used to generate random numbers - in Askia surveys, the Seed is actually used to uniquely define rotations in an interview).
Here we will use an MS Access DB but of course, it's better (as in more reliable and more scalable) to use a SQL server DB.
You need to create one table that we will call Recruitment with the following fields:
- id ( a unique ID generated by the database)
- seed - integer: our unique interview identifier
- Mail - string: the email address entered by our recruited panelist
- State - integer: panelists will have a value of 0 when the email is sent, the field will be set to 1 once the link on the email has been clicked
You then need to create a DSN called Respondent - a DSN is unique string mapping to a named database on a computer. Alternatively you could have used ADO. Let's go through the structure of our survey:
- The "seed" question will be used to store the unique identifier of the interview. If you want something absolutely fool proof, you could also have used the GUID which is a unique string generated for each interview To populate the Seed variable we used a set value routing from the Recruitment chapter with a value if true:
- The "mail" question will be used to store the email given by the respondent in our survey where we also check that it follows the . . .
email@example.com. . . rule.
- The checkDuplicate will be used to check if the email already exists in the database. If that's the case, we won't allow the respondent to continue. This is achieved by having a SQL import of that question. We run the query . . .
SELECT Id FROM Recruitment WHERE Mail='??Mail??'. . . and if checkDuplicate has a value > 0, we know we have a duplicate in our database. In this example a message is displayed and the survey should be abandoned there.
- We use a Start query routing to create the record in the database - initialising the record for this interview and ensuring the State field is at 0:
INSERT INTO Recruitment (Seed, Mail,State) VALUES (??Seed??, '??Mail??',0)
- We use a "Send e-mail" routing to send this:
<!DOCTYPE HTML><!-- The new doctype HTML5--> <html>We are referring to an asp page called setValidation.asp (which is contained in the resources) and will be uploaded in the survey resources directory.
<head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <title>askia</title> </head> <body> <div>To validate your mail please click on the link below:<br /><br /><a href="??ResPath??setValidation.asp?seed=!!??Seed??!!">Click here</a><br /><br />If it doesn't work, copy the link below and paste it into your internet browser:<br /><br />??ResPath??setValidation.asp?seed=!!??Seed??!!</div> </body> </html>
- Here is what the setValidation.asp file looks like:
<html> <head> <title>askia</title> </head> <body> <% response.clear() if request("seed")<>"" then dim cnx 'Connection to database Set cnx=Server.CreateObject("ADODB.Connection") cnx.open "DSN=Recruitment;" dim sql sql="UPDATE Recruitment SET State=1 WHERE Seed = " & request("seed") & "" on error resume next cnx.execute(sql) if err<>0 then response.write("No update permissions!") else response.write("Your mail is now validated, you can go back to the survey and close this window.") end if 'Close connection at the end cnx.close set cnx=nothing end if %> </body> </html>This is quite bare, but what we do is simply change the value of State for the record which has the matching value for "seed" and we show a polite message asking them to close the window.
- And here is the content of the getValidation.asp page:
<% response.clear() if request("mail")<>"" then dim cnx 'Connection to database Set cnx=Server.CreateObject("ADODB.Connection") cnx.open "DSN=Recruitment;" dim sql sql="SELECT State from Recruitment where Mail = '" & replace(request("mail"),"'","''") & "'" dim rs set rs=cnx.execute(sql) while not rs.EOF response.Write(rs("State")) rs.movenext wend 'Close the recordset rs.close set rs=nothing 'Close connection at the end cnx.close set cnx=nothing end if %>