Solved

Check if record exists before insert function or procedure.

Posted on 2008-09-29
1
2,793 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 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