Why does "table.fieldname" cause problems in SQL statement?

[may1hem]

I'm using ASP to display menus & content from an Access database.

The main menu table (tblMainMenu) had field names "m_id", "m_title", etc. The sub-menu table (tblSubMenu) had field names "m_id", "m_title", etc. I found this confusing when coding.

The SQL statement started like this: "SELECT tblMainMenu.m_title, tblSubMenu.m_title, ......."

So today I changed the names of the sub-menu fields to "s_id", "s_title", etc. I changed the SQL statement accordingly but found the Web page gave this error: "ADODB.Recordset error '800a0cc1'. Item cannot be found in the collection corresponding to the requested name or ordinal."

The SQL statement now looks like this: "SELECT tblMainMenu.m_title, tblSubMenu.s_title, ......."

This is the line on the Web page where the problem occurs:
mySubmenuTitle = rs("tblSubMenu.s_title&quot

I found that if I remove the table name then it works fine. I guess that this is something to do with the fact that "m_title" wasn't unique before and so I needed the table name to clarify which "m_title" I was referring to. But now, "s_title" is unique and so I don't need the table name. But why should it cause a problem if I include the table name? Surely it should just be optional, just like brackets are optional when clarifying expressions?

I've noticed this problem several times in the past. Any ideas why? I'm not happy just accepting this as it is. Is there a good reason why the table name is not just optional?

Thanks,
May

 

[unseth]

what you should do is this:

  SELECT tblMainMenu.m_title AS maintitle, tblSubMenu.m_title AS subtitle, .......

every db has its own syntax. Sql vs. mysql vs. oracle vs. access... etc.

it also depends what you're calling it from (vbscript, c++, query analyzer, etc.)

in the above example (using asp) you can reference the two m_title's as

maintitle = rs("maintitle&quot
subtitle = rs("subtitle&quot

my guess is the asp/access combo doesn't like the tablename in the recordset request object.

 

[may1hem]

Hi Unseth, ASP seems to be happy with the table name prefix when the field names are duplicated, but is unhappy when the field names are unique.

I just wondered if anyone knew why.

Thx,
May