access/asp

date posted 13th January 2003 16:40

alas my current project utilises access and i've just come a cropper on what
should be a doddle.

the function below is generating the error "Syntax error in INSERT INTO
statement"

function regUser(fname, email, pwd)

regUser=""
set myConn=getConn(application("strUDL"))
myConn.open

set objRS=myConn.execute("SELECT COUNT(email_address) AS [eCounter]
FROM USERS WHERE email_address='"&email&"'")

eCounter=objRS("eCounter")

set objRS=nothing

if eCounter=0 then
sql="INSERT INTO [USERS] (fname, email_address, password) VALUES
('"&fname&"', '"&email&"', '"&pwd&"')"
myConn.execute(sql)
else
regUser="That email address is already registered"
end if

myConn.close
set myConn=nothing

' response.End

end function

when writing out the generated sql i get this: INSERT INTO [USERS] (fname,
email_address, password) VALUES ('dave', [EMAIL REMOVED] 'nemesis')
which runs perfectly in a query window, just not through the function..
grrrrrr!

any ideas?

tia