Arrgh!!

[Newsgroup_User]

Hi All

(MySQL 5, ASP on both Live and Dev Machines)

Bit of a quandry here, my code works perfect on my dev machine (XP Pro) but
when I move the code onto my live Intranet server I get the following:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Unknown column 'tblemployeeskills.es_employee' in 'on clause'

/HR/reports/chooseskillsreport.asp, line 47

Anyone any ideas? The DBs are exactly the same.

TIA
Bren

[Newsgroup_User]

Bren,

DW caches DB info to speed things up, so it sounds like you may have a
stale cache in DW. Here's a list of all the caches and how to clear them:

1. Refresh the Data Bindings Panel using button or context menu.

2. Right-click on a Recordset and select "cache" to toggle it off to
clear it, and then again to turn it back on.

3. In the Site Panel, select "Design Notes..." from the context menu for
the page. You can edit the cached data.

Be sure to shutdown DW before deleting the remaining ones.

4. There is a Site cache in the (User) Configuration\SiteCache folder
that you can delete (to force a refresh).

5. There is a general data cache in the (User)
Configuration\*FileCache*.dat that you can delete (to force a refresh).

HTH,
Randy


> Bit of a quandry here, my code works perfect on my dev machine (XP Pro) but
> when I move the code onto my live Intranet server I get the following:-
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> Unknown column 'tblemployeeskills.es_employee' in 'on clause'
>
> /HR/reports/chooseskillsreport.asp, line 47
>
> Anyone any ideas? The DBs are exactly the same.

[Newsgroup_User]

Hi Randy

Thanks so far but it looks like it is something to do with the SQL statement
due to the upgrade from MySQL 4 to 5.
Apparently to bring MySQL into line with the SQL 2003 Standard the JOIN
statement has changed, I am scratching my head a little with the info on
MySQL.com about this.

My SQL code that doesn't work on MySQL 5 is:-

varSQL = "SELECT tblemployeeskills.*, tblemployees.e_firstname,
tblemployees.e_lastname, tblskillsquals.* "
varSQL = varSQL & "FROM tblemployeeskills, tblskillsquals "
varSQL = varSQL & "LEFT JOIN tblemployees ON (tblemployeeskills.es_employee
= tblemployees.e_recid) "
varSQL = varSQL & "WHERE tblemployeeskills.es_sqid = " &
Replace(rsSkillsResult__param1, "'", "''")
varSQL = varSQL & " AND tblemployees.e_left = 'N' AND
tblskillsquals.sq_recid = " & Replace(rsSkillsResult__param1, "'", "''")

The change is something to do with how I am joining the tables but I am
still trying to work out what the issue is.

If you take a look at
http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html you may be
able to understand it better than me.

Cheers
Bren