?
Solved

Check if record exists before insert function or procedure.

Posted on 2008-09-29
1
Medium Priority
?
2,794 Views
Last Modified: 2012-05-05
Hi,
I'm looking for a function that retuns True(1) or False(0) if a record allready exists in a database .I'd like to use it to catch duplicates. before a new record is posted.
Also how it would be used when posting a new record.
PS if there is a better or easier way i'm all ears!
Thanks
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
'see if item exists BEFORE trying to insert into database
Function itemExists()
 Dim qSQL
 Dim conn
 Dim rs
 Dim totalrecs
 Dim result
    
	'inittize totalrecs
	totalrecs=0
	'initialize result
	result=""
  
	Set conn = Server.CreateObject("ADODB.Connection")
	Set rs = Server.CreateObject("ADODB.RecordSet")
    
	'open database connection
	conn.Open "test"
	
	'query here
	qSql ="SELECT Count(*) as reccount FROM tbltest"  + " WHERE fldtest=" + "'" + "Apple" + "'"
	
	'execute query here
	Set rs = Conn.Execute(qSQL)
	
    'assign record count to totalrecs 
	totalrecs = rs("reccount")
	'debug
	'response.Write(cint(totalrecs))
	
	
	if cint(totalrecs) > 0 then
	 'debug
	 'response.write("True")
	 result=True
	 end if
	if cint(totalrecs) = 0 then
	 'debug
	 'response.write("False")
	 result=False
	 end if	 
 
' Close the Recordset object and destroy it
	rs.Close
	Set Rs = Nothing
	conn.Close
	Set Conn = Nothing
	
 itemexists=result
 
 'debug
 response.Write("<br>")
 response.Write(itemexists)
End Function
%>
 
<%
'insert record here
 
%>
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Untitled Document</title>
</head>
 
<body>
<p><a href="<%=Request.ServerVariables("SCRIPT_NAME")%>?teststuff=1">Click Me</a>
  <%
teststuff = Request.QueryString("teststuff")
 
	If teststuff = 1 Then
		Call itemexists
	End If
%>
</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
<p>&nbsp;</p>
</body>
 
</html>

Open in new window

0
Comment
Question by:philosullivan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 12

Accepted Solution

by:
R_Harrison earned 375 total points
ID: 22604113
You can simply use a select statement to look for the record in the database - use something like your line 23 - use where to specifit the data that should match.    If the returned reccount is greater than 0 you have a matching 'duplicate' record(s) in the database, if not then insert.....
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question