469,320 Members | 2,160 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,320 developers. It's quick & easy.

IF EXISTS error! (0x80040E14)

I've written an asp form that successfully takes a users firstname, surname,
email address, username and password and then add's those details to an
Access database.

I've been trying to modify the code so that the script checks that the
selected username doesn't already exist using the SQL IF EXIST command but I
am getting the famous error as shown below:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected
'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
/hk/admin/adduser.asp, line 42

I've spent the last 2 hours scouring the web and newsgroups for answers or
ideas to help fix this but haven't got anywhere.....I'm hoping someone here
can help! :-)

The code I have is:

'-- Create SQL statement

strSQL = "IF EXISTS(SELECT 'True' FROM tblUsers WHERE Username =
'" & frmUsername & "') "

strSQL = strSQL & "BEGIN "

strSQL = strSQL & "SELECT 'This record already exists!' "

strSQL = strSQL & "END ELSE BEGIN "

strSQL = strSQL & "SELECT 'Record Added' "

strSQL = strSQL & "INSERT INTO tblUsers(FirstName, Surname,
Email, Username, Password) VALUES('" & frmFirstName & "','" & frmSurname &
"','" & frmEmail & "','" & frmUsername & "','" & frmPassword & "') "

strSQL = strSQL & "END"

'-- Create object and open database

Set DataConnection = Server.CreateObject("ADODB.Connection")

DataConnection.Open strConnUsers

Set rsUser = DataConnection.Execute (strSQL)
The strSQL string above equates to:

IF EXISTS(SELECT 'True' FROM tblUsers WHERE Username = 'test') BEGIN SELECT
'This record already exists!' END ELSE BEGIN SELECT 'Record Added' INSERT
INTO tblUsers(FirstName, Surname, Email, Username, Password)
VALUES('test','test','test','test','test') END

Thanks in advance....

Robin.
Jul 22 '05 #1
4 3650
Robin Lawrie wrote:
I've written an asp form that successfully takes a users firstname,
surname, email address, username and password and then add's those
details to an Access database.

I've been trying to modify the code so that the script checks that the
selected username doesn't already exist using the SQL IF EXIST
command but I am getting the famous error as shown below:

There is no such thing as "IF" in Access (JetSQL) queries. You have to
implement that IF logic in your vbscript code, not in the sql sent to the
database.

That said, it is possible to do an "upsert" in Access. See:
http://groups-beta.google.com/group/...bb23311e454af2

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #2
More information in this thread:
http://groups-beta.google.com/group/...3750ef0155b07b
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #3
Thanks Bob....

I've just installed MSDE 2000 and will give things a try with that......

Robin.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
More information in this thread:
http://groups-beta.google.com/group/...3750ef0155b07b --
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


Jul 22 '05 #4
Thanks Bob....

I've just installed MSDE 2000 and will give things a try with that......

Robin.

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
More information in this thread:
http://groups-beta.google.com/group/...3750ef0155b07b --
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 22 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ninjaboy | last post: by
4 posts views Thread by DEWright_CA | last post: by
reply views Thread by b.coolsaet | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.