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:
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:
CREATE PROCEDURE InsertName
First, we check if the record exists with the
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
With the sotred procedure solution, our ASP code would look like:
Simple! And only one call. If you prefer not to use a stored procedure, you can easily do the same right from ASP.
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.
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!
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.
And, as noted, you don't even have to change your INSERT query one little iota!
(this was taken from the website: http://www.aspfaqs.com/ASPscripts/PrintFAQ.asp?FAQID=106)