ASP and mySQL error - Please help

[JayBuys]

I'm trying to access a mySQL database with ASP and am running into some errors. I got most everything working fine except for a couple things. When I try to use a COUNT(*) in my SQL statement, it causes problems.
The error message I'm getting is:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Driver does not support this parameter

The live number it gives me referrs to the objRS.MoveFirst command. Strangly, if I remove the "COUNT(*) as message_count" from the SQL statement, everything works fine. Any ideas why this might be happening? My source code is below.
FYI - I'm using the latest version of the myODBC drivers for mySQL

Source code:
<!-- #INCLUDE FILE=&quot;include/mini-adovbs.inc&quot; -->

<%
Dim objConn, objRS, strSQL

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.open &quot;DSN=Intranet2&quot;

Set objRS = Server.CreateObject(&quot;ADODB.Recordset&quot
strSQL = &quot;SELECT forum_id, COUNT(*) AS message_count FROM messages WHERE public= 'No' GROUP BY forum_id&quot;
objRS.CursorLocation = adUseClient
objRS.Open strSQL, objConn, adOpenStatic, adLockOptimistic

objRS.MoveFirst

Do While NOT objRS.EOF
' Write the data out to test.
Response.Write objRS(&quot;forum_id&quot & &quot; &quot; & objRS(&quot;message_count&quot
objRS.MoveNext
Loop

objRS.Close
objConn.Close
Set objConn = Nothing
Set objRS = Nothing
%>

 

[vasah20]

have you checked the mysql documentation to make sure that they implement count(*)? I know that most of the open source DBMS products tend to deviate from the SQL 92 standard (i can attest to this from my experience with PostgreSQL). there's a good chance that count() might be something else in mysql. If there's a MySQL forum here, it might be worth it to post there.

hth
leo

 

[Databaseguy]

I've got code that looks pretty much like that. The only difference is I start with &quot;SELECT Count(*) AS Num....etc&quot;. That is, the Count goes first. I don't think that would make a difference.

The other thing is, you say it hangs at rst.movefirst, not at the SQL statement. Me thinks you've got an empty record set ? Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[Guest_imported]

This is a guess, because it doesn't seem that there's something wrong with sql statement itself... I've encountered several times the errors, pointing to the line of code, which follows the line, that actually causes the error. So, maybe the problem is with recordset's properties you're using in the objRS.Open statement. My suggestion is(just for checking purposes):


Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.open &quot;DSN=Intranet2&quot;

strSQL = &quot;SELECT forum_id, COUNT(*) AS message_count FROM messages WHERE public= 'No' GROUP BY forum_id&quot;

Set objRS = objConn.Execute(strSQL)
objRS.MoveFirst

and so on...

Good luck.

 

[Geee]

You must start with the COUNT statement. Change you SQL statement to...

strSQL = &quot;SELECT COUNT(*) AS Message_Count, forum_id FROM messages WHERE public= 'No' GROUP BY forum_id&quot;

G
-GTM Solutions, Home of USITE-
-=

http://www.gtmsolutions.com=-

 

[JayBuys]

Thanks for all the suggestions
Problem was... I needed to check the &quot;Convert BIGINT to INT&quot; box in the DSN settings.