Solved

Check if record exists before insert function or procedure.

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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 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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

10 Experts available now in Live!

Get 1:1 Help Now