| 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
Else
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:
CREATE PROCEDURE InsertName
(
@username varchar(25),
@userpassword varchar(25)
)
AS
IF EXISTS(SELECT 'True' FROM MyTable WHERE username = @username)
BEGIN
--This means it exists, return it to ASP and tell us
SELECT 'This record already exists!'
END
ELSE
BEGIN
--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)
END
|
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
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
%>
|
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
FROM USERS
WHERE 'name to insert' & 'pword' NOT In
(select UserName & Password from USERS);
|
Happy Programming!
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>"
Else
Response.Write "Unexpected error: " & oops.number & " -- " & oops.description
End If
Next
%>
|
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!
|
