When you think ASP, think...
Recent Articles xml
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips
spgif spgif

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
spgif spgif
ASP ASP.NET ASP FAQs Feedback topnav-right

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, Queries


How can I find out if a record already exists in a database? If it doesn't, I want to add it.

[Print this FAQ]

Answer: I've seen this question posed many times in the ASP Q&A Messageboard... Typically this is done to determine if a user name already exists, common to many login required sites. Many people query the database and then check the values against the returned values through a loop of some sort. If they don't find it, they then add the record. To me, this seems like unnecessary
overhead. I've also seen it done by selecting the record, and then checking for EOF, like so:

  Dim strSQL
  strSQL = "SELECT * FROM MyTable WHERE username = '" & _
            Request.Form("username") & "'"
  Set rs = db.Execute()

  If rs.EOF Then
    'Now do the insert
    Response.Write "Record exists"
  End If

Each of these methods require at least two trips to the database. Why not let SQL do it all for you? You can check if the user exists and add him if he doesn't in ONE call! You can either do this with a stored procedure or from ASP.

The stored procedure:

  @username varchar(25),
  @userpassword varchar(25)
IF EXISTS(SELECT 'True' FROM MyTable WHERE username = @username)
  --This means it exists, return it to ASP and tell us
  SELECT 'This record already exists!'
  --This means the record isn't in there already, let's go ahead and add it
  SELECT 'Record Added'
  INSERT into MyTable(username, userpassword) VALUES(@username, @userpassword)

First, we check if the record exists with the EXISTS keyword. EXISTS executes the query we tell it to (the SELECT) and returns a boolean value. If it finds the record, we return 'This record already exists!' to our recordset and do nothing else. If it doesn't exist, we execute our INSERT statement, and then return 'Record Added' to our recordset. The -- is a comment in SQL, and is equivalent to VBScript's ' or REM.

With the sotred procedure solution, our ASP code would look like:

  Dim db, rs
  Set db = Server.CreateObject("ADODB.Connection")
  db.Open myTest 'use your connection here
  'And call our Stored procedure passing the username and userpassword
  Set rs = db.Execute("InsertName '" & _
                      Request.Form("username") & "','" & _
                      Request.Form("password") & "'")

  'Now let's check what happened
  If rs(0) = "This record already exists!" Then
    'We can either redirect back to the original page and tell
    'the user to try again or write something out to this page
    Response.Write "Your user name and password has been accepted."
  End If

Simple! And only one call. If you prefer not to use a stored procedure, you can easily do the same right from ASP.

  Dim db, rs, sSQL

  username = "Steve"
  password = "1234"

  sSQL = "IF EXISTS(SELECT 'True' FROM MyTable WHERE username = '" & _
          username & "') "
  sSQL = sSQL & "BEGIN "
  sSQL = sSQL & "SELECT 'This record already exists!' "
  sSQL = sSQL & "SELECT 'Record Added' "
  sSQL = sSQL & "INSERT INTO MyTable(username, userpassword) VALUES('" & _
        username & "','" & password & "') "
  sSQL = sSQL & "END"

  Set db = Server.CreateObject("ADODB.Connection")
  db.Open myTest 'use your connection here
                  'And execute our statement
  Set rs = db.Execute(sSQL)

  If rs(0) = "This record already exists!" Then
    'We can either redirect back to the page and
    'tell the user to try again or write something out to the page
    Response.Write "Your user name and password has been accepted."
  End If

You can also get more creative in your return values so that you could Response.Write the return value right out to the page. Either way, you're using your resources more efficiently by executing only one database call.

Marco De Luca shares a another clever way to add a record to a table if it doesn't already exist...

I was reading one of your recent articles on 'Adding a record to a
database table if it doesn't exist' and I figured out another way to do it. I don't know if this will benefit you, but here is the SQL statement for you.

It checks the USERS table to see if a user name and password exist for a user, then it inserts the user name and password if the user doesn't exist. This might be useful as it works in MS Access. I believe your solution works in all other relational database systems other then MS Access.

'Name to insert' = the user name you want to insert
'pword' = the password to insert

INSERT INTO USERS (UserName, Password)
SELECT DISTINCT 'name to insert' as theName, 'pword' as pword
WHERE 'name to insert' & 'pword' NOT In
         (select UserName & Password from USERS);

Happy Programming!

Yet another way! (from Bill Wilkinson)

I think this may be the easiest way yet, since you don't really have to change your SQL INSERT code, at all!

' I assume you have a connection open...
objConn.Errors.Clear ' just to be safe, clear out any existing errors

On Error Resume Next ' then IGNORE errors!
catConn.Execute("INSERT INTO whateverTable (field1,field2,field3) Values(777,'whatever','and more')")
On Error GoTo 0 ' turn off the ignoring of errors!

' now see if we got any errors from the insert!
For Each oops In catConn.Errors
    If oops.number = -2147217900 Then
        Response.Write "That item already exists in that table!<BR>"
        Response.Write "Unexpected error: " & oops.number & " -- " & oops.description
    End If

We use the wonderful error-catching ability of ADODB and VBS to both ignore (from the VBS perspective) and catch (from the ADODB view) a possible error on the INSERT of the possibly duplicate name/value.

The error number -2147217900 equates to &H80040E14 (0x80040E14 for you Java/C/C++/JavaScript people), which is of course the error that is thrown (at least by Access!) when you attempt such a duplicate insertion. That number may or may not be the same for other databases, but it's easy to check which error number you are getting (heck, the above code will tell you, giving an "Unexpected error" message) and modify the code to match.

And, as noted, you don't even have to change your INSERT query one little iota!

FAQ posted by Steve Cimino at 12/14/2000 4:59:12 PM to the Databases, Queries category. This FAQ has been viewed 183,511 times.

Do you have a FAQ you'd like to suggest? Suggestions? Comments? If so, send it in! Also, if you'd like to be a FAQ Admin (creating/editing FAQs), let me know! If you are looking for other FAQs, be sure to check out the 4Guys FAQ and Commonly Asked Messageboard Questions!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM

ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article