i switch from Access to my MYSQL, then start getting all kind of error 
message, although everything works fine with access:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]You have an error in your SQL 
syntax near '() ORDER BY AD_DATED desc' at line 1

The sql :
1st : SELECT CATS.CAT_ID, CAT_NAME, CAT_IMAGE, (SELECT COUNT (*)  FROM 
ADS  WHERE ADS.CAT_ID = CATS.CAT_ID AND AD_APPROVED = 1 AND AD_EXPIRED > 
DATE() ) AS TOTAL
FROM CATS
WHERE cat_name like 'varcat_name%'
__________________________________________

2nd : SELECT *,(SELECT COUNT (*)
FROM ads
WHERE ads.Type_ID = types.Type_ID AND AD_APPROVED = 1 AND AD_EXPIRED > 
DATE()) AS TOTAL  FROM TYPES
ORDER BY TYPE_NAME ASC

3rd: SELECT DISTINCT ADS.AD_ID,FAVORITES.AD_ID,FAVORITES.U_ID, CAT_ID, 
SUB_ID, AD_TITLE, AD_DATED,AD_PRICE, AD_STATE
FROM ADS, Favorites
WHERE AD_APPROVED = MMColParam and Ads.ad_id like favorites.ad_id and 
U_ID ='MMColParam2'

Is the SQL statment Above not compatible with MYSQL.

Any help will be appreciate it.
Thank you

Read this section on using SubSELECTs in your SQL:
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html

Regards,
Peter Foti


> -----Original Message-----
> From: jhd67@v... [mailto:jhd67@v...]
> Sent: Wednesday, February 05, 2003 7:25 PM
> To: ASP Databases
> Subject: [asp_databases] Access and MYSQL with ASP
> 
> 
> i switch from Access to my MYSQL, then start getting all kind 
> of error 
> message, although everything works fine with access:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [MySQL][ODBC 3.51 Driver][mysqld-3.23.55-nt]You have an error 
> in your SQL 
> syntax near '() ORDER BY AD_DATED desc' at line 1
> 
> The sql :
> 1st : SELECT CATS.CAT_ID, CAT_NAME, CAT_IMAGE, (SELECT COUNT 
> (*)  FROM 
> ADS  WHERE ADS.CAT_ID = CATS.CAT_ID AND AD_APPROVED = 1 AND 
> AD_EXPIRED > 
> DATE() ) AS TOTAL
> FROM CATS
> WHERE cat_name like 'varcat_name%'
> __________________________________________
> 
> 2nd : SELECT *,(SELECT COUNT (*)
> FROM ads
> WHERE ads.Type_ID = types.Type_ID AND AD_APPROVED = 1 AND 
> AD_EXPIRED > 
> DATE()) AS TOTAL  FROM TYPES
> ORDER BY TYPE_NAME ASC
> 
> 3rd: SELECT DISTINCT 
> ADS.AD_ID,FAVORITES.AD_ID,FAVORITES.U_ID, CAT_ID, 
> SUB_ID, AD_TITLE, AD_DATED,AD_PRICE, AD_STATE
> FROM ADS, Favorites
> WHERE AD_APPROVED = MMColParam and Ads.ad_id like favorites.ad_id and 
> U_ID ='MMColParam2'
> 
> Is the SQL statment Above not compatible with MYSQL.
> 
> Any help will be appreciate it.
> Thank you
>