Solved

Check if record exists before insert function or procedure.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Prevent URL from printing on header 5 65
API Location, separating out information 10 45
syntax error on ASP ? 6 42
HTML in email body has extra  tick marks 3 61
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…
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

947 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now