ASP - MYSQL select problems

I have an asp application, which looks at three database tables, and
retreives results, the SQL is not massively complex, but long
sConnection = "my connection string goes here"
Set strCon = Server.CreateObject("ADODB.Connection")
strCon.Open(sConnection)
set rsView = Server.CreateObject("ADODB.recordset")

sql = "SELECT TBL_EMPLOYEE.Employee_ID, TBL_EMPLOYEE.Employee_Forename,

TBL_EMPLOYEE.Employee_Surname,
TBL_EMPLOYEE.Employee_Sex,TBL_EMPLOYEE.Employee_Holiday_Allo wance,
TBL_EMPLOYEE.Employee_Place_Of_Work, TBL_EMPLOYEE.Employee_Job_Title,
concat(TBL_EMPLOYEE_1.Employee_Forename ,' ' ,
TBL_EMPLOYEE_1.Employee_Surname) AS V_Mgr_Name,
TBL_EMPLOYEE_1.Employee_ID AS v_mgr_ID, TBL_LOGIN.login_username,
TBL_LOGIN.login_password, TBL_LOGIN.login_email from TBL_EMPLOYEE LEFT
OUTER JOIN TBL_EMPLOYEE AS TBL_EMPLOYEE_1 ON
TBL_EMPLOYEE.Employee_Manager_ID = TBL_EMPLOYEE_1.Employee_ID INNER
JOIN TBL_LOGIN ON TBL_EMPLOYEE.Employee_ID = TBL_LOGIN.Employee_ID"


rsview.open sql, strcon


It works fine, until I add a field such as below:


sConnection = "connection stgring goes here"
Set strCon = Server.CreateObject("ADODB.Connection")
strCon.Open(sConnection)
set rsView = Server.CreateObject("ADODB.recordset")


sql = "SELECT TBL_EMPLOYEE.Employee_ID, TBL_EMPLOYEE.Employee_Forename,

TBL_EMPLOYEE.Employee_Surname,
TBL_EMPLOYEE.Employee_Sex,TBL_EMPLOYEE.Employee_Holiday_Allo wance,
TBL_EMPLOYEE.Employee_Place_Of_Work, TBL_EMPLOYEE.Employee_Job_Title,
concat(TBL_EMPLOYEE_1.Employee_Forename ,' ' ,
TBL_EMPLOYEE_1.Employee_Surname) AS V_Mgr_Name,
TBL_EMPLOYEE_1.Employee_ID AS v_mgr_ID, TBL_LOGIN.login_username,
TBL_LOGIN.login_password, TBL_LOGIN.login_email,
TBL_EMPLOYEE.Employee_NINO, TBL_EMPLOYEE.Employee_Payroll,
TBL_EMPLOYEE.Employee_FTE from TBL_EMPLOYEE LEFT OUTER JOIN
TBL_EMPLOYEE AS TBL_EMPLOYEE_1 ON TBL_EMPLOYEE.Employee_Manager_ID =
TBL_EMPLOYEE_1.Employee_ID INNER JOIN TBL_LOGIN ON
TBL_EMPLOYEE.Employee_ID = TBL_LOGIN.Employee_ID"


rsview.open sql, strcon


Where it returns no rows. I've using mySQL5, and know that there is a
way of using views, but my webhosts have disabled the create or replace

views command for me (nice!)


Is there a limit to the number of fields I can return, or the length of

a select statement (I know in oracle the select cannot be more than 64k

diana nodes), but don't suppose my statement will be hitting that as
it's not massively complext.


any help greatly appreciated

Re: ASP - MYSQL select problems

kevinfairs@googlemail.com wrote on 11 Dec 2006 01:48:49 -0800:

> I have an asp application, which looks at three database tables, and
> retreives results, the SQL is not massively complex, but long

> It works fine, until I add a field such as below:
>
> sConnection = "connection stgring goes here"
> Set strCon = Server.CreateObject("ADODB.Connection")
> strCon.Open(sConnection)
> set rsView = Server.CreateObject("ADODB.recordset")
>
> sql = "SELECT TBL_EMPLOYEE.Employee_ID, TBL_EMPLOYEE.Employee_Forename,
>
> TBL_EMPLOYEE.Employee_Surname,
> TBL_EMPLOYEE.Employee_Sex,TBL_EMPLOYEE.Employee_Holiday_Allo wance,
> TBL_EMPLOYEE.Employee_Place_Of_Work, TBL_EMPLOYEE.Employee_Job_Title,
> concat(TBL_EMPLOYEE_1.Employee_Forename ,' ' ,
> TBL_EMPLOYEE_1.Employee_Surname) AS V_Mgr_Name,
> TBL_EMPLOYEE_1.Employee_ID AS v_mgr_ID, TBL_LOGIN.login_username,
> TBL_LOGIN.login_password, TBL_LOGIN.login_email,
> TBL_EMPLOYEE.Employee_NINO, TBL_EMPLOYEE.Employee_Payroll,
> TBL_EMPLOYEE.Employee_FTE from TBL_EMPLOYEE LEFT OUTER JOIN
> TBL_EMPLOYEE AS TBL_EMPLOYEE_1 ON TBL_EMPLOYEE.Employee_Manager_ID =
> TBL_EMPLOYEE_1.Employee_ID INNER JOIN TBL_LOGIN ON
> TBL_EMPLOYEE.Employee_ID = TBL_LOGIN.Employee_ID"
>
> rsview.open sql, strcon
>
> Where it returns no rows. I've using mySQL5, and know that there is a
> way of using views, but my webhosts have disabled the create or replace
>
> views command for me (nice!)
>
> Is there a limit to the number of fields I can return, or the length of
>
> a select statement (I know in oracle the select cannot be more than 64k
>
> diana nodes), but don't suppose my statement will be hitting that as
> it's not massively complext.
>
> any help greatly appreciated

All you've done is add some columns, so in theory it should work. However,
if you have On Error Resume Next in your code, and one of those column names
is incorrect, that could conceivably make it appear that there are no rows
returned if you're not explicitly checking for an error after the
rsview.open. Can you run that same SQL from outside of ASP, say in
phpMyAdmin (if it's available on your host) to see if there's a problem with
it?

Dan

Re: ASP - MYSQL select problems

As speed isn't any importance in this paticular app, I've gone down the
route of create tables on the fly, select, do stuff, drop tables.

It's a massive overhead, and on a larger system, it would run like a
dog, but it's not, and it doesn't.

Not happy, but it's a solution.