Solved

Check if record exists before insert function or procedure.

Posted on 2008-09-29
1
2,789 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
1 Comment
 
LVL 12

Accepted Solution

by:
R_Harrison earned 125 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Filter Question 8 83
Want the count number from this QUery 2 49
Scheduled IIS .Net2 AppPools recycle and SQL connection Hangs 33 113
Microsoft SQL ADO Conn Issue 6 47
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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