error on simple insert

[penguinspeaks]

I have this simple code:

but this is producing the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 5.1 Driver][mysqld-5.6.12]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key) values (1, 31644, 3)' at line 1

/requestasp.asp, line 12

I understand it may be weird to use mysql DB on windows server but i do it on a regular basis.

Anyway, what would be causing this? When I do a response.write I get the following:

everything looks ok. The database fields are set up as INT as they should be.

Any ideas?

Bam

 

[jeffwest21]

Sorry, can't see any code, can you re-post it?

'Clever boy...'

 

[bborissov]

This is MS SQL Server forum, not MySQL one
Try to ask there.

Borislav Borissov
VFP9 SP2, SQL Server

 

[penguinspeaks]

Weird the ode did not show up, let me post it outside of code brackets.


code
strSQL = "insert into history (id, songid, key) values ("&var1&", "&var2&", "&var3&")"
conn.execute (strSQL)


and the response.write is

insert into history (id, songid, key) values (1, 31644, 3)


As far as posting in the wrong area, it is using SQL with a MySQL DB.

 

[gmmastros]

I think key is a reserved word for MySQL. Try this:

strSQL = "insert into history (id, songid, [!]`[/!]key[!]`[/!]) values ("&var1&", "&var2&", "&var3&")"
conn.execute (strSQL)



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[penguinspeaks]

Thank you gmmastros, I am not sure why I didn't think of "key" being a reserved word. It is this way on any language probably.

I did the bst thing and changed the field name in the DB and changed the coding.

Thanks again!

 

[gmmastros]

This forum is for Microsoft SQL Server. Key is not a reserved word in SQL Server, so your original query would have worked without any modifications. This is why Boris suggested that you post in a more appropriate forum. People that answer questions in a MySQL forum would have probably seen the answer quicker.

While most SQL is standard among many databases, occasionally there are differences between them. This turned out to be one of the differences instead of one of the similarities.

Just to be clear, another forum was suggested primarily because you are more likely to get accurate advice in a timely manner.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom