works as query but not in asp??

[Spudhead]

Hey,

I've got mySQL running on my home pc, under PWS, and I'm doing admin stuff with mySQL-Front. I have a SQL query that I can run fine in mySQL-Front; get exactly the records back that I expect. But I put this in an ASP page and I don't get anything. No error, no results.

I'm creating a command object, setting its commandText to the SQL string, creating a recordset object and making it execute the command. I then check for BOF and EOF and they return true.

I've tried putting different SQL queries on that page and they work - the database connection is fine, the command executes, results are returned.

I know what you're going to say; post the SQL / ASP. But I can't because I left it at home. I'll post it up when I get back, honest. :)

But in the meantime, I'm completely in the dark as to why a (reasonably) straighforward query would return results when run directly against the database (in mySQL-Front) but not when run via ASP - has anyone had this happen? I'm a little unsure where to actually start looking for errors.

Ta,
Spud

[BigDaddy]

It's gremlins....

Them dang gremlins....

Seriously, though-I was going to suggest you post the SQL statement.

[whammy]

Can you post the SQL statement and the relevant ASP scripting? (Yeah, you BOTH guessed right!)... although, it might be a difference in how MySQL Front and ASP interpret SQL? (just guessing here). :D

[Spudhead]

Right, sorry about the delay - have a look at this. I can't see anything wrong with it; it doesn't return any errors but neither does it return any rows, and when I paste the query into mySQL-Front, it does.

I've tried creating the SQL string as a single line of code; made no difference. I've also tried opening the recordset with different lock types; same effect.

I'm at a loss...



cn = Server.CreateObject("ADODB.Connection");
cn.ConnectionTimeout = 15;
cn.CommandTimeout = 10;
cn.Open("Driver={MySQL}; server=localhost; database=db1; username=******; password=******");


var strSQL="SELECT forum_topics.id, forum_topics.title, forum_topics.date_started, MAX(forum_posts.post_date) AS lastpost";
strSQL+=" FROM forum_topics INNER JOIN forum_posts ON forum_posts.topic_id=forum_topics.id";
strSQL+=" INNER JOIN forum_users ON forum_topics.started_by=forum_users.id";
strSQL+=" GROUP BY forum_topics.id, forum_topics.title, forum_topics.date_started";
strSQL+=" ORDER BY lastpost DESC";


//strSQL="SELECT * FROM forum_topics";
cmd=Server.CreateObject("ADODB.Command");
cmd.ActiveConnection=cn;
cmd.CommandType=adCmdText;
cmd.CommandText=strSQL;
rs=Server.CreateObject("ADODB.RecordSet");
rs=cmd.Execute();


I've also tried using this SQL string: (it just includes the user's name), and guess what? It does exactly the same thing :(


SELECT
forum_topics.id,
forum_topics.title,
forum_topics.date_started,
MAX(forum_posts.post_date) AS lastpost,
forum_users.fullname
FROM forum_topics
INNER JOIN forum_posts ON forum_posts.topic_id=forum_topics.id
INNER JOIN forum_users ON forum_topics.started_by=forum_users.id
GROUP BY forum_topics.id, forum_topics.title, forum_topics.date_started
ORDER BY lastpost DESC



Any ideas? Anyone? Please??

[Alekz]

Hi,
What if You try this way:

ConnStr = "Driver={MySQL}; server=localhost; database=db1; username=******; password=******"
theSQL = "SELECT * FROM forum_topics"
Set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open theSQL, ConnStr


Alex

[Spudhead]

Yeah, I tried that too. Tried various cursor and lock types, and the "SELECT * FROM forum_topics" works fine whatever method I use to open the recordset.

I've tried everything with the connection - I can only conclude that the myODBC driver for ADO is interpreting the SQL differently (ie: in a way that makes it return no results) to whatever SQL interpreter mySQL-Front is using. Does that make sense? And if so, how can I tell and what can I do about it?

[Alekz]

I'd install PHP then... I've never heard anything good about myODBC :(

Alex

[Spudhead]

Alekz - I can't help but think, however, that redoing this particular page another way - for instance using two seperate queries - would be slightly more cost-effective than buying the necessary PHP hosting, learning a new language from scratch and re-coding the entire site.

Can you be more specific about myODBC? For instance, "I've seen page x, that explains how myODBC parses queries with a particular structure in a way unlike other SQL parsers, and goes on do discuss ways of avoiding this feature."

Thanks.

[whammy]

How about trying it without any parameters specified?

Like:

cn = Server.CreateObject("ADODB.Connection");

var strSQL="SELECT forum_topics.id, forum_topics.title, forum_topics.date_started, MAX(forum_posts.post_date) AS lastpost";
strSQL+=" FROM forum_topics INNER JOIN forum_posts ON forum_posts.topic_id=forum_topics.id";
strSQL+=" INNER JOIN forum_users ON forum_topics.started_by=forum_users.id";
strSQL+=" GROUP BY forum_topics.id, forum_topics.title, forum_topics.date_started";
strSQL+=" ORDER BY lastpost DESC";

Set rs = cn.Execute(strSQL)

I'm just shooting in the dark here though... that might eliminate a few possibilities however...

[Alekz]

Hi Spudhead,
Sorry I have no URL about myODBC. Just heard several people complaining... I wont post here their words - my post will be deleted if I do :)

Alex

[whammy]

I don't care personally - but I have to make the forum acceptable to the kids that might visit - so I do appreciate your concern. :D