asp command object execute error on parameters including encoding

Question

I am running a query using a command object but getting an unexpected error executing a query I need some help with.

The same code appears to work just a few lines above and seems to have only appeared after I replaced the original db table to a -dev copy table.

The code is as follows:

sql = "SELECT blah, blah... " &_
        "FROM tracker p " &_
        "LEFT JOIN ... " &_
        "LEFT JOIN ... " &_
        "WHERE p.id = ? "
Dim cmdObj : set cmdObj = server.CreateObject("ADODB.Command")
cmdObj.ActiveConnection = conn
cmdObj.CommandText = sql
cmdObj.Parameters(0) = sID
Set rs = cmdObj.execute()

This runs fine and I store out some results. I then run another query over the next lines use the same parameter:

sql = "SELECT * FROM table-dev p WHERE p.id = ? "
cmdObj.CommandText = sql
cmdObj.Parameters(0) = sID
Set rs = cmdObj.execute()

generates the error:

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

[MySQL][ODBC 5.1 Driver][mysqld-5.0.95-log]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 '-dev p WHERE p.id = _latin1'21115'' at line 1

I am hoping someone can point in the right direction why this might be showing now and throwing the encoding to the parameter. I have seen this before and managed to find a fix but can't for the life of me remember how.

All help greatly appreciated. Many thanks, JB

score 2 · Accepted Answer · answered Sep 02 '15 at 12:34

2

It looks like the keyword table is confusing the query engine. Just wrap the identifiers in back-ticks to make them explicit:

SELECT * FROM `table-dev` p WHERE p.id = ?

answered Sep 02 '15 at 12:34

David

208,112
36
198
279

The table name used was not actually called "table" but once I used the back-ticks it seemed to work!! Thanks for that. Was driving me a bit crazy and the thought of the back ticking never entered my head. Many thanks :) – Jason Sep 02 '15 at 12:57
1

Could it be that MySQL is expecting an explicit `AS` for the aliasing, and the backticks work around that? – Martha Sep 02 '15 at 15:29
1

@Martha: I don't think so. Though without knowing what the *actual* code is which caused the problem (since the OP apparently decided to change it for the question), it could be anything. Even an errant whitespace character between the name and `-dev`. The only thing clear form the error message is that the problem is immediately before the hyphen. – David Sep 02 '15 at 15:33

asp command object execute error on parameters including encoding

1 Answers1