Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
on adding a new enry check table in database for duplicate 1
- Thread starter ashw
- Start date
-
1
- #3
First run a query that pulls value from the database that match the web address submitted in the form...
<cfquery name="WebAdd_Similar" DATASOURCE="YourDSN">
SELECT Web_Address, YourDatabaseTable_PrimaryKey
FROM YourDatabaseTable
WHERE (Web_Address LIKE '#Trim(form.Web_Address)#'
</cfquery>
Then run the following CFIF..
<cfif WebAdd_Similar.recordcount GT 0>
The following record IDs contain the same web address just submitted:
<CFOUTPUT QUERY="WebAdd_Similar">
#YourDatabaseTable_PrimaryKey#<br>
</CFOUTPUT>
<cfelse>
No records match the web address.
I have simplified the code greatly. You can change the reference of the primary key to the "name" or "title" associated with the records.
Please also note that if you run this check AFTER you have just inserted the record - you will always get one commanality - the data you just inserted.
You can also place this among the server-side code before you insert the information into the database.
Hope this gets you started.
<cfquery name="WebAdd_Similar" DATASOURCE="YourDSN">
SELECT Web_Address, YourDatabaseTable_PrimaryKey
FROM YourDatabaseTable
WHERE (Web_Address LIKE '#Trim(form.Web_Address)#'
</cfquery>
Then run the following CFIF..
<cfif WebAdd_Similar.recordcount GT 0>
The following record IDs contain the same web address just submitted:
<CFOUTPUT QUERY="WebAdd_Similar">
#YourDatabaseTable_PrimaryKey#<br>
</CFOUTPUT>
<cfelse>
No records match the web address.
I have simplified the code greatly. You can change the reference of the primary key to the "name" or "title" associated with the records.
Please also note that if you run this check AFTER you have just inserted the record - you will always get one commanality - the data you just inserted.
You can also place this among the server-side code before you insert the information into the database.
Hope this gets you started.
my recommendation:
1. make sure there is a UNIQUE constraint on the column(s) where you want to detect duplication
2. go ahead and do the insert first, but use cfcatch to trap the rejected duplicate
the reason for this is simple statistics
let's say that out of every 100 rows you want to insert, 5 will be duplicates
if you do the select first, to see if the record exists, and follow it with the insert, then for every 100 rows you want to insert, you will be doing 195 database operations, and any time you do two database operations instead of one, the performance is poorer
conversely, if you just do the insert and trap the database error message, then for every 100 rows you want to insert, you will be doing only 100 database operations
simple, eh?
note that with cfcatch, you still get to present a customized error message to your users just like you would if you did the select first
rudy
1. make sure there is a UNIQUE constraint on the column(s) where you want to detect duplication
2. go ahead and do the insert first, but use cfcatch to trap the rejected duplicate
the reason for this is simple statistics
let's say that out of every 100 rows you want to insert, 5 will be duplicates
if you do the select first, to see if the record exists, and follow it with the insert, then for every 100 rows you want to insert, you will be doing 195 database operations, and any time you do two database operations instead of one, the performance is poorer
conversely, if you just do the insert and trap the database error message, then for every 100 rows you want to insert, you will be doing only 100 database operations
simple, eh?
note that with cfcatch, you still get to present a customized error message to your users just like you would if you did the select first
rudy
- Thread starter
- #5
<cftry>
<cfquery name="myinsert" dataSource="foo">
insert into links (website)
values ('#newurl#')
</cfquery>
<cfcatch type="database">
<cfoutput>
<p>Oops.<br />
error message: #cfcatch.message#<br />
type = #cfcatch.type#</p>
</cfoutput>
</cfcatch>
</cftry>
as i said, you can detect what the error message is, like "duplicate primary key rejected" (depending on your database), or using the SQLSTATE code
rudy
<cfquery name="myinsert" dataSource="foo">
insert into links (website)
values ('#newurl#')
</cfquery>
<cfcatch type="database">
<cfoutput>
<p>Oops.<br />
error message: #cfcatch.message#<br />
type = #cfcatch.type#</p>
</cfoutput>
</cfcatch>
</cftry>
as i said, you can detect what the error message is, like "duplicate primary key rejected" (depending on your database), or using the SQLSTATE code
rudy
- Status
Similar threads
- Locked
- Question