Letting users unsubscribe from asp/sqlserver DB mailing list (the best way?)

BTW - There's no formula - The string is purely random

For iCount = 1 to 20 ' To Generate 20 characters in a random string

thanks for straightening me out their dragonlaird, can I get the database to automatically generate the id or do I have to generate the value in ASP then put that into the database?

Dragonlaird I also found this one which seems to work great.

<%
'Our Function generatePassword accepts one parameter 'passwordLength'
'passwordLength will obviously determine the password length.
'The aplhanumeric character set is assigned to the variable sDefaultChars
Function generatePassword(passwordLength)
'Declare variables
Dim sDefaultChars
Dim iCounter
Dim sMyPassword
Dim iPickedChar
Dim iDefaultCharactersLength
Dim iPasswordLength
'Initialize variables
sDefaultChars="abcdefghijklmnopqrstuvxyzABCDEFGHIJKLMNOPQRSTUVXYZ0123456789"
iPasswordLength=passwordLength
iDefaultCharactersLength = Len(sDefaultChars)
Randomize'initialize the random number generator
'Loop for the number of characters password is to have
For iCounter = 1 To iPasswordLength
'Next pick a number from 1 to length of character set
iPickedChar = Int((iDefaultCharactersLength * Rnd) + 1)
'Next pick a character from the character set using the random number iPickedChar
'and Mid function
sMyPassword = sMyPassword & Mid(sDefaultChars,iPickedChar,1)
Next
generatePassword = sMyPassword
End Function
Response.write generatePassword(20) 'Call the function & pass in 6 as the parameter
%>

Sorry about cutting off mid-type there, I didn't realise the last comment saved and I had to shoot off to work.

The function provided by CyrexCore2k above basically provides what I was going to type above, a random string of letters and numbers to be saved against each record which you can then use in your email to provide an unsubscribe link as explained above. If the user clicks the link, just verify in your unsubscribe.asp page that the ID and verification string both match and display a suitable message upon success or failure as required.

Another point, raised by CyrexCore2k, there really isn't any need to check that the string of characters is unique since the string AND ID  are passed via the URL and must match before the person is unsubscribed UNLESS you intend to use ONLY the random string as part of your URL for unsubscribes.

I'd personally prefer to use the ID/Verify method as this makes it almost impossible for someone to generate random strings and hit your unsubscribe page with them to try and randomly unsubscribe your users.

To back-populate your existing list of emails, just create a loop to retrieve each email record, generate your random string, update the record and save it, then move on to the next record...

When users subscribe, just generate the random string at the time you create the new record and save it along with the email address etc.

Hi Dragonlaird
Ok have this pretty well worked out now, just having a bit of trouble figuring out some code.
Im on the mailinglistremove.asp.asp page where someone will click on the link such as
http://localhost/mailinglistremove.asp?ID=17&EMAIL=email@isp.com&GUID=czu40bNSrkbuDYP5vEc8
that I generate for them in when the recieve an email from the list.

I am having trouble trying to do a proper if statement for when I am checking the querystring variables from the link against those in the DB. Its listed below, it was working when only checking 2 values (guid and email), but if I changed the id value I would get
Error Type:
(0x80020009)
Exception occurred.

so now that I am also checking ID its not working. Obviously i need to check that all 3 values are the same as those in the DB.


if id = objRS("id") And guid = objRS("guid") And email = objRS("email") then
dbOpen()
dbConnect.Execute("DELETE FROM emaillist WHERE ID = " & id & ";")
dbClose()
response.write "You have been successfully removed from the Mailing List."
else
'I want this message displayed if there is any characters that do not match what is in the DB
response.write "You have entered an incorrect user ID, please click on the unsubscribe link from an email we have sent you."
end if


Im sure it something simple that I am not doing right.

Any help would be great.

Firstly, there's no need to include the email address in the URL, the ID and GUID comparison are more than sufficient and also allows for the possibility that the user may have changed their email address then unsubscribed.

The error you see is might be because one of the values you've retrieved from the DB is NULL (as opposed to an empty string) and as such, when used in comparison strings, it generates an error.

Try using something like this...

If Not IsNull(objRS("id")) AND Not IsNull(objRS("guid")) Then
  If id = objRS("id") AND guid = objRS("guid") Then
    dbOpen()
    dbConnect.Execute("DELETE FROM emaillist WHERE ID = " & id & ";")
    dbClose()
    Response.Write "You have been successfully removed from the Mailing List."
  Else
    'I want this message displayed if there is any characters that do not match what is in the DB
    Response.Write "You have entered an incorrect user ID, please click on the unsubscribe link from an email we have sent you."
  End If
Else
  ' The GUID is probably null - cannot unsubscribe, needs attention
  Response.Write "There was a problem processing your request. Please email XXX"
End If

You shouldn't even need a recordset to accomplish this.

Just do this

dbOpen()
If dbconnect.Execute("SELECT id FROM emaillist WHERE id = " & Request.QueryString("ID") & " AND guid = '" & Request.QueryString("GUID") & "'").EOF Then
      Response.Write "The unsubscribe link provided does not seem to be valid. blah blah blah"
Else
    dbConnect.Execute("DELETE FROM youruserstable WHERE id = " & Request.QueryString("ID")
End If
dbClose()

And remember to protect your variables from SQL insertion.