Solved

Check if record exists before insert function or procedure.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

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 have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

680 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