Wrox Programmer Forums
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 10th, 2005, 04:01 AM
Authorized User
Join Date: Sep 2004
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mikersantiago
Default avoid duplicate entry of records

Hi guys,

Here are my existing asp pages,

<form method="POST" action="PROFILE_ADD_ac.asp" name="form" onSubmit="return validateForm(this)">
<input type="text" name="BldgnameNew" size="50">

BldgnameNew= ChkString(Request.Form("BldgnameNew"))

sql_insert = "insert into TblSubgroup (Bldgname, cgroup, Datebuilt, GFA, GLA) values ('" & BldgnameNew & "','" & group & "', '" & Datebuilt & "', '" & GFA & "', '" & GLA & "', )"

I just want to ask how is it done in asp/access, to avoid duplicate or identical records to be added by the user in the database.

Here's exactly the scenario and what I want to happen with the application, I have a table in Access that holds records such as Bldgname, Datebuilt, Gross Floor Area (GFA), etc. Users can add new records (Bldgname, Datebuilt, GFA, etc.) but if the user enters the exact Building Name (Bldgname) that already exists in the database (i.e. Tower One - tower one), he will be prompted not to continue either by another asp page or a pop-up window, "Another record of the same building name already exists in our database, thank you.".

Can this be done by adding a condition in my sql insert statement such as...

sql_insert = "insert into TblSubgroup (Bldgname, cgroup, Datebuilt, GFA, GLA) "
sql_insert = sql_insert & " values ('" & BldgnameNew & "','" & group & "', '" & Datebuilt & "', '" & GFA & "', '" & GLA & "', ) "
sql_insert = sql_insert & " where Bldgname <> (Request.Form("BldgnameNew"))"

I'm getting errors when I tried the sql_insert above...
Expected end of statement in sql_insert.

Any other ways? Thanks a lot in advance to all of you:)

Old February 10th, 2005, 06:32 AM
Friend of Wrox
Join Date: Oct 2003
Posts: 326
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to Santhi Send a message via MSN to Santhi

You were getting errors because you have used Response.Form in the string as it is.You should have appended like this
sql_insert = sql_insert & " where Bldgname <> ("+Request.Form("BldgnameNew")+")"

To avoid duplicate entry ,
   Write one more select * query to the see the record Bldgname exists.If it is not 0,then throw an javascript alert script.

Old February 10th, 2005, 06:39 AM
Friend of Wrox
Join Date: Jul 2003
Posts: 683
Thanks: 0
Thanked 1 Time in 1 Post

AFAIK you cannot use a WHERE clause in an insert query.


Old February 10th, 2005, 09:02 PM
Friend of Wrox
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41

As Chris said you can not use a where condition like that. In addition Santhi's suggestion of:

sql_insert = sql_insert & " where Bldgname <> ("+Request.Form("BldgnameNew")+")"

If it were possible should be changes to:

sql_insert = sql_insert & " where Bldgname <> '" & Request.Form("BldgnameNew") & "';"

1.. the + replaced with &
2.. The single double quotes around the form element (if the value is a string)
3.. BTW : the semi colon at the end and my removal of the brackets is my personal style. The query would work without the semi colon (IMO should be used), It would also work with the brackets.

Wind is your friend
Old February 10th, 2005, 09:14 PM
Friend of Wrox
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41

One method to get around your problem:

Dim recordPresent
recordPresent = false
IF request.form("someName") <> "" THEN
   'check for an existing duplicate
   sql = "SELECT .... Where [existing val = new val];"
   set checkInfo = conn.execute(sql)
   if checkInfo.EoF then
      'not found so insert
       sql = "INSERT INTO....;"
      'duplicate found so dont insert
      recordPresent = true
   end if
end if

Now in your page somewhere detect:

if recordPresent = true then
   Some message saying did not insert, record already present
end if

;;;then throw an javascript alert script
You have to go to the server to check is the record there, I wouldnt use a JS alert myself.

Wind is your friend

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for Duplicate Records in Data Entry Form roznix Access VBA 5 June 7th, 2012 08:53 AM
How Do I avoid duplicate values xsltier XSLT 3 June 4th, 2008 01:36 PM
avoid adding duplicate records in access/asp mikersantiago Classic ASP Basics 1 September 21st, 2004 03:24 AM
Avoid duplicate node values nambati XSLT 1 September 8th, 2004 02:41 PM
deleting only 1 duplicate entry qwjunk Classic ASP Databases 3 February 2nd, 2004 11:45 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.