Delete Unwanted Email Addreses

Delete Unwanted Email Addreses

--------------------------------------------------------------------------------

Description : A nifty script to delete unwanted or invalid Email addresses from a database. Works with Access mdb and SQL Server databases.

'**************************************
' Name: Delete unwated Email Addresses
' Description:A nifty script to delete u
' nwanted or invalid Email addresses from
' a database. Works with Access mdb and SQ
' L Server databases. No config required,
' ready to use. Can be used for cleaning u
' p any other unwanted records as well
' By: Cenk Yurtseven
'
' Returns:Deletes all unwanted email add
' resses from a database in one step.
'
' Assumes:The code can bu used for remov
' ing any other records as well. The user
' must be able to change the field name in
' the code.
'
' Side Effects:The script deletes the un
' wanted records in all tables and fields
' of the selected database regardless of t
' he table or field names.

<%@Language="vbscript" %>
<%
'***************************************
' *************************************
' This script deletes all unwanted Email
' addresses from a database in multiple ta
' bles
' Note: If your email address field name
' is not named "EMail", change the field n
' ame EMail to your fields name.....
'
'***************************************
' *************************************
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adCmdText = &H0001
Const adUseClient = 3
If Request.QueryString("md") = "dl" And Request.Form("sAddress") <> "" And Request.Form("dbName") <> "" Then
DSNName = Request.Form("dbName") & ".mdb"
Set Con = Server.CreateObject("ADODB.Connection")
Con.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath(DSNName)
strAddresses = Request.Form("sAddress")
splitAddress = Split(strAddresses, ",")
Set Table = Con.OpenSchema (20)
Do While Not Table.EOF
TblName = Table("Table_Name")
'The condition below excludes the system tables from beeing processed
If UCase(Left(TblName,4)) <> "MSYS" And UCase(Left(TblName,3)) <> "SYS" And UCase(Left(TblName,4)) <> "RTBL" Then
strSQL = "SELECT * FROM " & TblName
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.Open (strSQL), Con, adOpenForwardOnly, adLockReadOnly, adCmdText
For Each fldName In rs.Fields
If fldName.Name = "EMail" Then
For i = 0 To UBound(splitAddress)
oneAddress = Trim(splitAddress(i))
strSQL = "DELETE FROM " & TblName & " WHERE EMail = '" & oneAddress & "'"
Con.Execute(strSQL)
Next
End If
Next
rs.Close
Set rs = Nothing
End If
Table.MoveNext
Loop
Con.Close
Set Con = Nothing
End If
%>
<!DOCTYPE HTML Public "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE>Delete Multiple Addresses</TITLE>
</HEAD>
<BASEFONT FACE="Verdana">
<BODY BGCOLOR="#000080" Text="#FFFFFF">
<BR>
<DIV ALIGN="center"><B>Delete Multiple Addresses</B>
<FORM ACTION="deladdre.asp?md=dl" METHOD="post">Database Name:
<Input Type="text" NAME="dbName" STYLE="text-align: right"><B>.mdb</B>
<BR>
<BR>Paste all unwanted addresses into the textarea below, separated by a comma<B>,</B>
<BR>
<TEXTAREA COLS="80" ROWS="8" NAME="sAddress"></TEXTAREA>
<Input Type="submit" VALUE="Delete Addresses"></FORM>
<SMALL><B>Note:</B> This action will delete the selected addresses from all tables of the selected database.</SMALL>
<BR>
<BR>This file must be located In the same folder of the database.</DIV>
</BODY>
</HTML>