Syntax Error

[steelegbr]

When running the following code:

<%
dim fsver, copyright, name, location, review, score

fsver=Request.Form("fsver")
copyright=Request.Form("copyright")
name=Request.Form("name")
review=Request.Form("review")
score=Request.Form("score")

set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open server.mappath("/marcandross/database.mdb")
sql="INSERT INTO " & fsver & " (by, name, "
sql=sql & "review, score)"
sql=sql & " VALUES "
sql=sql & "('" & copyright & "',"
sql=sql & "'" & name & "',"
sql=sql & "'" & review & "',"
sql=sql & "'" & score & "')"
Response.Write(sql) ' Added for testing purposes
conn.Execute sql,recaffected
conn.close

%>

and I insert the control data I get the following output:

INSERT INTO fs2000 (by, name, review, score) VALUES ('Various','Hercules C130E (C130H.ZIP)','Realistic panel, model and lights. Good overall workmanship. Well worth the download on domain.com','5')

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/marcandross/staffonly/goadd.asp, line 25

Can anybody help please! (I am new to ASP!)

[edited by: Xoc at 10:13 pm (utc) on Mar. 2, 2004]
[edit reason] no domain names [/edit]

[txbakers]

Is score a numeric field? You are sending it as a string. '5'

[steelegbr]

I have just tried entering score as numeric:

INSERT INTO fs2000 (by, name, review, score) VALUES ('Various',''Hercules C130E (C130H.ZIP)','Realistic panel, model and lights. Good overall workmanship. Well worth the download on domain.com',5)

with no change in results....

[edited by: Xoc at 10:14 pm (utc) on Mar. 2, 2004]
[edit reason] no domain names [/edit]

[txbakers]

('Various',''Hercules C130E

You have two single quotes before Hercules. Could that be the problem?

Also, try running the query directly in Access to see what happens.

[steelegbr]

Sorry. I retyped it. Same results:

INSERT INTO fs2000 (by, name, review, score) VALUES ('Hercules C130E (C130H.ZIP)','Various','Realistic panel, model and lights. Good overall workmanship. Well worth the download on domain.com',5)

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/marcandross/staffonly/goadd.asp, line 25

[edited by: Xoc at 10:14 pm (utc) on Mar. 2, 2004]
[edit reason] no domain names [/edit]

[WebJoe]

I have a hunch that

BY

is a reserved keyword.

what happens if you execute that statement in access directly? sometimes it gives you a more understandable error message.

If this verifies my hunch try this:

INSERT INTO fs2000 ([by], [name], [review], [score]) VALUES ('Hercules C130E (C130H.ZIP)','Various','Realistic panel, model and lights. Good overall workmanship. Well worth the download on domain.com','5')

(of course, you could use the

[

and

]

just for the keyword)

[edited by: Xoc at 10:15 pm (utc) on Mar. 2, 2004]
[edit reason] no domain names [/edit]

[steelegbr]

I used access at school to create the blank database so cannot query it...

[WebJoe]

I just created a table with the fields here and tried your SQL-statement: I was right, use the parentheses, it'll work with that

[steelegbr]

Finally it's not showing that error! The [] seamed to work but now I get this error:

INSERT INTO fs2000 ([by], [name], [review], [score]) VALUES ('Various','Hercules C130E (C130H.ZIP)','Realistic panel, model and lights. Good overall workmanship. Well worth the download on domain.com',5)

Microsoft JET Database Engine error '80040e57'

The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

/marcandross/staffonly/goadd.asp, line 25

To sort this problem does it involve using Access to edit field size?

[edited by: Xoc at 10:15 pm (utc) on Mar. 2, 2004]

[txbakers]

yes

[steelegbr]

I'll get onto it tomorrow at school and thanks for the help!