CODEWITHSUNDEEP
mysqlsqlasp-classiccommand-objects
Jason
Jason

Reputation: 29

asp command object execute error on parameters including encoding

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

Upvotes: 0

Views: 189

Answers (1)

David
David

Reputation: 218798

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 = ?

Upvotes: 2

Related Questions