what is wrong with this SQL statement's syntax?

Question

SQL Statement

INSERT INTO Account (Fname,Lname,Address,
Username,Password,Email,Activated) VALUES
("fred","flintstone","bedrock rd","fredflinstone","fredflintstone",
"[e-mail]","yes")


Source code that generates error message "Syntax error in INSERT INTO statement."


Dim Conn = Server.CreateObject("ADODB.Connection") 'Holds the Database Connection Object
' Dim rsGuestbook 'Holds the recordset for the records in the database
Dim strSQL As String 'Holds the SQL query to query the database

Conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\db1.mdb")

sql = "INSERT INTO Account (Fname,Lname,Address,Username,Password,Email,Activated) VALUES "

sql = sql & "('" & Request.Form("Fname") & "',"
sql = sql & "'" & Request.Form("Lname") & "',"
sql = sql & "'" & Request.Form("Address") & "',"
sql = sql & "'" & Request.Form("Username") & "',"
sql = sql & "'" & Request.Form("Password") & "',"
sql = sql & "'" & Request.Form("Email") & "',"
sql = sql & "'yes')"
sql = Replace(sql, "'", Chr(34))
'Exit Sub
Conn.Execute(sql) ' Crashed here with Syntax error in INSERT INTO statement.

Double checked the fields. Database file was created in Access 2000. Text fields are
Fname, Lname, Address, Username, Password, Email. Activated is a yes/no value.

Can't figure this out it looks valid.

Answer 1

9 times out of ten it's a quote or apostraphe that got misplaced. Try combining your sql into one big ugly string on a single line, and omit or comment out the Replace line. Just type it out exactly the way it's going into the database. If THAT doesn't work, try inserting your values one at a time. Otherwise, you can split it back apart one variable at a time and retry it until it's formatted to your liking once you've figured out where the problem is.

Answer 2

eliminate all parenthesis, this works opt for Articles.ART_Title, Writers.WRT_LastName, revealed.MAG_ID, revealed.difficulty FROM Writers inner connect Articles ON Writers.WRT_ID=Articles.WRT_ID inner connect revealed ON Articles.ART_ID=revealed.ART_ID

Answer 3

do not use " (chr(34)) . Use only single quotes.

So, your code is actually ok, just delete sql=replace... line

Answer 4

lol.looks ok to me.