ASP >> General

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

Visitor Ratings (0) :

Description

You can do it 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
Else
Response.Write "Record exists"
End If
%>
For example You can check if the user exists and add him if he doesn't in ONE call!

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.You can either do this with a stored procedure or from ASP.

Code

Select All

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
  Else
    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 & "END ELSE BEGIN "
  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
  Else
    Response.Write "Your user name and password has been accepted."
  End If
%>