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(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.

 

[Bob Barrows [MVP]]

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...ss.queries/browse_frm/thread/ffbb23311e454af2

Bob Barrows

 

[Bob Barrows [MVP]]

More information in this thread:
http://groups-beta.google.com/group...4245817b9a7/6e3750ef0155b07b#6e3750ef0155b07b

 

[Robin Lawrie]

Thanks Bob....

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

Robin.

 

[Robin Lawrie]

Thanks Bob....

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

Robin.