IF EXISTS error! (0x80040E14)

[Robin Lawrie]

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(FirstN ame, Surname,
Email, Username, Password) VALUES('" & frmFirstName & "','" & frmSurname &
"','" & frmEmail & "','" & frmUsername & "','" & frmPassword & "') "

strSQL = strSQL & "END"

'-- Create object and open database

Set DataConnection = Server.CreateOb ject("ADODB.Con nection")

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(FirstN ame, Surname, Email, Username, Password)
VALUES('test',' test','test','t est','test') END

Thanks in advance....

Robin.

[Bob Barrows [MVP]]

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.

[Bob Barrows [MVP]]

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.

[Robin Lawrie]

Thanks Bob....

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

Robin.

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP12.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.

[Robin Lawrie]

Thanks Bob....

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

Robin.

"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP12.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.