ASP Classic recordset unable to see columns with &

Question

I am currently in the process of converting a large amount of ASP classic/VBscript pages from an old database (Unify Dataserver) to MySQL.

Say you have a query like this:

sql = "SELECT c.container_type, c_amount, c_sdate, c_edate, csrt " & _
"FROM containers c, container_chars cc"
objRS.Open sql, objConn, 3, 1

If I want to reference the column "c_edate", I can simply use this and it works fine:

x = objRS("c_edate")

However, when it comes to referencing a column like "c.container_type" (With a . used to differentiate it from another table, like so:

x = objRS("c.container_type")

It will say

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

I can fix it by using a number instead:

objRS(0)

This was never an issue until we switched to MySQL. In our old database, using the rs(table.column_name) format worked just fine. But in MySQL, once you add a (.) to the code, it can't find that item unless you switch it to a number.

As you can imagine, this is quite a pain as I go through the 700+ pages of this website manually counting the placement of each column in the corresponding select statement every time something from the query is referenced.

Does anyone know why this is happening or how to make the rs(table.column_name) format work with MySQL like it does with our old database?

Answer 1

In SQL Server, and apparently in MySQL too, the way to reference a field in the result set is to just use the name, without the prefix.

x = objRS("container_type")

The prefix is needed by the database to differentiate between identically-named columns, but once you send the results to a recordset, that recordset doesn't know or care where the columns came from.

The same goes for aliases:

SQL = "SELECT c.container_type AS ctype, [...]"
...
x = objRS("ctype")

Combining these two facts, if you do have identically-named columns in the result set, you must alias at least one of them. If you don't, it won't necessarily give an error, but you will not be able to reference the second column using the rs("name") syntax.

SQL = "SELECT c1.container_type, c2.container_type AS c_type2, ..."
...
x = objRS("container_type")
y = objRS("c_type2")

_{[Note that while you're at it, you probably should also modify your FROM clauses to use proper FROM table1 INNER JOIN table2 ON table1.fieldA = table2.fieldB type syntax. The FROM table1, table2 WHERE table1.fieldA = table2.fieldB syntax has been deprecated for many years now.]}