-2

I'm getting an error and unsure what could be the cause. I have response.write'd out the SQL and copy pasted it into access and it runs perfectly fine, so I suspect it is something outside of just the SQL statement.

I actually copy pasted my code from when I was inserting values into a different database and just changed the database and the values I was inserting. Any ideas?

set conn = server.createobject("ADODB.Connection")
conn.open "provider=Microsoft.Jet.OLEDB.4.0; data source=" &server.mappath("/test_framework/rbxDB.mdb")

user = request.form("user")
pass = request.form("password")
sql = "INSERT INTO users (userID, password, Type) VALUES ('"&user&"', '"&pass&"', 'S');"

response.write(sql)
set rs=conn.execute(sql)

conn.close
set conn = nothing

Yes I'm aware there are a host of issues with this code, however they don't give me an error, I am just trying to fix this error "syntax" error.

What is response.writed is: 

INSERT INTO users (userID, password, Type) 
VALUES ('asdf', 'q123eqeds', 'S');
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jonathan Lin
  • 124
  • 1
  • 1
  • 11

1 Answers1

1

Type and password (password is a access db engine reserve word) is a Reserve Word and thus needs to be escaped using [] like

INSERT INTO users (userID, [password], [Type]) VALUES ('asdf', 'q123eqeds', 'S');
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • You forgot [password] ... – Gustav Aug 17 '16 at 06:00
  • @Gustav, NO, password is not a reserve word. see the link in answer or this https://support.office.com/en-us/article/Access-2007-reserved-words-and-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe#__toc272229038 – Rahul Aug 17 '16 at 06:43
  • Well, I and many have found it is. See link (top) from @Andre. – Gustav Aug 17 '16 at 06:54
  • @Gustav, Yes seems so cause `password` actually not a access RW but looks like DB engine RW. – Rahul Aug 17 '16 at 06:58