asked on

ASP and MYSQL

I am having a strange problem with ASP and MYSQL. What I am trying to do is fairly simple. I have the following:

<%

set oRS = server.createobject("ADODB.Recordset")
set oDB = server.createobject("ADODB.Connection")

oDB.Open "DSN=mydatabasestuff"

sSQL = "select name, count(*) as cnt from tbl_users group by name"

oRS.Open sSQL, oDB
if not oRS.EOF then
while not oRS.EOF
response.write oRS("name")
response.write oRS("cnt")
oRS.MoveNext
wend
else
response.write "No records found"
end if

oRS.Close
oDB.Close

set oRS = nothing
set oDB = nothing
%>

If I run this via an ASP page, it returns zero records and I get the message "No Records Found". However, if I run this query directly in MySQL or the MySQL GUI it get the results I desire. If I remove count(*) from the above query then the ASP page returns the results, but not what I want.

This works fine with Microsoft Access, Microsoft SQL and Oracle, so I know there is nothing I am doing wrong in my ASP code, but I may have to do something differently to get it to work with MYSQL.

Any advise on this would be appreciated!

Thanks

jitganguly

Try using ADO cursors

i.e.
oRS.Open sSQL, oDB, adOpenDynamic, adLockPessimistic, adCmdText
or
oRS.Open sSQL, oDB, 2,2,1

jitganguly

Try using ADO cursors

i.e.
oRS.Open sSQL, oDB, adOpenDynamic, adLockPessimistic, adCmdText
or
oRS.Open sSQL, oDB, 2,2,1

liebrand

ASKER

Nope, using ADO cursors did not fix the problem....

jitganguly

Hmmm,
Try bof also, like
if not (oRS.EOF or oRS.bof) then

liebrand

ASKER

Nope, that didnt work either -- remember, if I simply change my DSN to point to a Microsoft SQL, Access or Oracle database it works fine ...

I am pretty good with ASP, this problem has just baffled me. I cannot find any information about this on MySQL's site and nobody seems to know how to resolve it.

jitganguly

Tried mdac ? 2.5 ?

drittich

Please post your MySQL connection string.

jitganguly

You are not providing userid and password with db open statement.
I know it is working with other dbs, but just give it a try

oDB.Open "DSN=mydatabasestuff;uid=myuserid;pwd=mypassword"

John844

sounds like a problem with the myodbc driver. I see the same results. I found documentation on Mysql.com stating that count(*) is supported with an example exactly like yours. Let me keep looking for more specific info on this.

John

ASKER CERTIFIED SOLUTION

Mark Franz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John844

I found this same issue over and over on multiple message boards, you are not the only one fighting with this. This listings I read did not have any useful info. Good luck

Mark Franz

Microsoft has no vested interest in developing an OpenSource database driver, you might have to use a third-party solution, check on the www.mysql.com page for help.

John844

I have been told that mysql has a setting that you can change for compatibility issues. It appears that some versions of drivers return count(*) as a bigint. ado is having problems interpreting this.

try setting this:
There is an OPTION=16384 to treat BIGINT as INT

liebrand

ASKER

In the middle of this page it spoke about using MyODBC with ASP and setting a flag. Once I went into the MyODBC driver settings under ODBC Administration and set the "Return Matching Rows" option it then worked.

I know some other people suggested the same thing, but MGFRANZ suggested it first.

Thank you for all your help! Really appreciate it!

Mark Franz

:-)

I had to deal with MySql when building ColdFusion and JSP pages a while back... <...shiver....> NEVER again!

drittich

Yep, that is what I was getting at with your connection string.