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.

Robin Lawrie wrote:
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/microsoft.public.access.queries/browse_frm/thread/ffbb23311e454af2

Bob Barrows

More information in this thread:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/browse_frm/thread/f8a0a4245817b9a7/6e3750ef0155b07b#6e3750ef0155b07b

Thanks Bob....

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

Robin.

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/browse_frm/thread/f8a0a4245817b9a7/6e3750ef0155b07b#6e3750ef0155b07b
Show quoteHide quote

Thanks Bob....

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

Robin.

http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/browse_frm/thread/f8a0a4245817b9a7/6e3750ef0155b07b#6e3750ef0155b07b
Show quoteHide quote

Session_OnEnd does not DeleteFile()
aspfunction does not work?
Global.asa problems with IIS 6
Do Until Loop problem
Accessing Stored Procedure from IIS