Remove Duplicate Records - SQL Source Code in ASP

Special : Web Developer's Collection CD-ROM
50,000+ Web Templates, Logos, Cliparts, Scripts. Order Now!

Add to Favorites Make Home Page

8912 Online

Language Categories

Source Codes Home
Project Ideas New!
Interview Questions
FAQs Home
ASP Home: ASP Source Codes; ASP Script Directory New!; ASP .Net Script Directory New!; ASP Interview Questions; ASP FAQs; ASP How Tos
C Home: C Source Codes; C Script Directory New!; C Interview Questions; C FAQs; C How Tos
C++ Home: C++ Source Codes; C++ Script Directory New!; C++ Interview Questions; C++ FAQs; C++ How Tos
Java Home: Java Source Codes; Java Directory New!; Java Interview Questions; Java FAQs; Java How Tos
JavaScript Home: JavaScript Directory New!; JavaScript Source Codes; JavaScript FAQs; JavaScript How Tos
COBOL Home: COBOL Source Codes; COBOL FAQs; COBOL How Tos
Pascal Home: Pascal Source Codes; Pascal FAQs; Pascal How Tos
PHP Script Directory New!
Python Script Directory New!
Perl & CGI Script Directory New!
Flash Script Directory New!
CFML Script Directory New!
Remotely Hosted Scripts New!
Tools & Utilities Directory New!
XML Script Directory New!
Best Programmers: Amit Mathur; Vishal Bhardwaj; Deepesh Jain
Vyom NetWork

Our Services

Get 9,000 Interview Questions & Answers in an eBook.

9500+ Pages
9000 Question & Answers
All Tech. Categories

14 MB Content

Get it now !!

Home » ASP Home » SQL Home » Remove Duplicate Records

A D V E R T I S E M E N T

Title	Remove Duplicate Records
Description	This code removes duplicate records from a database.
Category	ASP » SQL
Hits	380092
Code	Select and Copy the Code
'************************************** ' Name: Remove duplicate records ' Description:This code removes duplicat ' e records from a database. ' ' Returns:All duplicate records of the s ' elected database table will be removed a ' nd a report will be shown. ' ' Assumes:It is as easy as browsing thro ' ugh the Internet. No coding is required, ' just only forms to fill in. ' ' Side Effects:The script uses the recor ' dset object and is very fast even if tho ' usands of records. the removement cannot ' be undone after it is once started. ' <%@Language="vbscript" %> <!DOCTYPE HTML Public "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <TITLE>Remove Duplicate Records</TITLE> <STYLE Type="text/css"> BODY { background-color: #336699; font-family: Verdana; font-size: 10pt; color: #FFFFFF } TD { font-family: Verdana; font-size: 10pt; color: #000000 } </STYLE> </HEAD> <BODY> <% Const adOpenForwardOnly = 0 Const adLockReadOnly = 1 Const adCmdTableDirect = &H0200 Const adCmdText = &H0001 sPassword = "admin" ' fill in your desired password here If Request("password") <> "" Then Session("Password") = Request("password") End If If Session("Password") <> sPassword Then Response.Write "<HTML><BODY><FORM ACTION=""removeduplicates.asp"" METHOD=""post"">Password: (CaSe SeNsiTivE) <INPUT TYPE=""password"" NAME=""password""><INPUT TYPE=""submit""></FORM>" Else Dim sdbPath Dim sdbTable Dim spriKey Dim sdbField Dim strSQL Dim strConn Dim rs Dim ttCount Dim i Dim totalFound Dim lookFor Dim tempVal Dim foundDups Dim tempArr Dim prCount Dim File Dim Posi Dim delDub delDub = False File = Request.ServerVariables("SCRIPT_NAME") File = StrReverse(File) Posi = InStr(File, "/") File = Left(File, Posi - 1) File = StrReverse(File) If Request.Form("db") <> "" Then On Error Resume Next DSNName = Request.Form("db") sdbTable = Request.Form("tb") spriKey = Request.Form("pk") sdbField = Request.Form("fn") sdbPath = "DBQ=" & Server.MapPath(DSNName) & ";Driver={Microsoft Access Driver (.mdb)};" strSQL = "SELECT COUNT() AS ttCount FROM " & sdbTable Set strConn = Server.CreateObject("ADODB.Connection") strConn.Open sdbPath Set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.Open(strSQL), strConn, adOpenForwardOnly, adLockReadOnly, adCmdText Set rs = strConn.Execute(strSQL) tempArr = "" totalFound = rs("ttCount") rs.Close Set rs = Nothing totalFound = CInt(totalFound) Response.Write totalFound Response.Write " records" & vbCrLf Response.Write "<HR>" & vbCrLf strSQL = "SELECT " & sdbField & "," & spriKey & " FROM " & sdbTable & " ORDER BY " & sdbField Set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = adUseClient rs.Open(strSQL), strConn, adOpenForwardOnly, adLockReadOnly, adCmdText If Err = 0 Then For i = 0 To (totalFound - 1) rs.MoveFirst rs.Move(i) lookFor = rs.fields(sdbField) If lookFor = tempVal Then foundDups = rs.fields(spriKey) tempArr = tempArr & foundDups & "," End If tempVal = lookFor Next rs.Close Set rs = Nothing If tempArr <> "" Then Response.Write "Primary Key ID containing duplicate data in the selected field:" & vbCrLf Response.Write "<BR>" & vbCrLf End If If tempArr = "" Then Response.Write "No duplicate records found." & vbCrLf Else tempArr = Left(tempArr, Len(tempArr) - 1) Response.Write tempArr tempArr = Split(tempArr, ",", -1, 1) prCount = UBound(tempArr) prCount = prCount + 1 Response.Write "<BR>" & vbCrLf For i = 0 To (prCount - 1) strSQL = "DELETE FROM " & sdbTable & " WHERE " & spriKey & " = " & tempArr(i) strConn.Execute(strSQL) Next End If delDub = True Else Response.Write Err.description End If strConn.Close Set strConn = Nothing If Err = 0 And delDub = True And tempArr <> "" Then Response.Write "<BR><B>" & prCount & "</B> duplicate record" If prCount <> 1 Then Response.Write "s" End If Response.Write " deleted successfully." & vbCrLf End If End If %> <HR><BR><DIV ALIGN="center"><B>Remove duplicate database records</B></DIV> <FORM ACTION="<%=File %>" METHOD="post"> <TABLE CELLPADDING="4" CELLSPACING="1" ALIGN="center" WIDTH="50%" STYLE="border: 1px outset" BGCOLOR="#FFFFFF"> <TR> <TD WIDTH="50%" BGCOLOR="#E9E9E9">Database:</TD> <TD WIDTH="50%" BGCOLOR="#E9E9E9"> <Input Type="text" SIZE="25" NAME="db" STYLE="text-align: right"><B>.mdb</B></TD></TR> <TR> <TD WIDTH="50%" BGCOLOR="#E9E9E9">Table Name:</TD> <TD WIDTH="50%" BGCOLOR="#E9E9E9"> <Input Type="text" SIZE="25" NAME="tb"></TD></TR> <TR> <TD WIDTH="50%" BGCOLOR="#E9E9E9">Field Name:</TD> <TD WIDTH="50%" BGCOLOR="#E9E9E9"> <Input Type="text" SIZE="25" NAME="fn"></TD></TR> <TR> <TD WIDTH="50%" BGCOLOR="#E9E9E9">Primary Key:</TD> <TD WIDTH="50%" BGCOLOR="#E9E9E9"> <Input Type="text" SIZE="25" NAME="pk"></TD></TR> <TR> <TD WIDTH="50%" BGCOLOR="#E9E9E9"> </TD> <TD WIDTH="50%" BGCOLOR="#E9E9E9"> <Input Type="submit" VALUE="Remove Duplicates"></TD></TR></TABLE></FORM> <HR> <% End If %> </BODY> </HTML>

Related Source Codes

Script Name Author

�� VyomWorld

Resistor color code reader A.Chermarajan.

Telephone Directory dhivya

card swapping game (Mini Project) nityanand

simple hangman-pascalsource Seabert

college dirtectory (Mini Project) msridhar

Poll Application John van Meter

ASP Daily Hit Counter. Tejaskumar Gandhi

To avoid null in asp environment using sql Sami

Maklumbalas webmaster

poll John van Meter

EasyASP Template Engine. TjoekBezoer

Basic Calculator using HTML & Javascript. Patrick M. D Souza

What servers support ASP ? VyomWorld

What is ASP? VyomWorld

A D V E R T I S E M E N T

Google Groups Subscribe to SourceCodesWorld - Techies Talk

Email:

Free eBook - Interview Questions: Get over 1,000 Interview Questions in an eBook for free when you join JobsAssist. Just click on the button below to join JobsAssist and you will immediately receive the Free eBook with thousands of Interview Questions in an ebook when you join.

Free Tutorials - HTML, Java, SQL, PHP, ASP, Perl, SQL Tutorials
Discuss Programming Problems and Freshers Jobs related issues here.
Discuss Final Year Project Ideas here.