[thelist] SQL Query Help after upgrade from Access

Michael Pack michaelpack at wvdhhr.org
Mon Sep 13 15:46:04 CDT 2004


Hi all, I'm using ADO and SQL Server 2000. I'm upgrading an application
from Access to SQL and ran into a problem with no error response.

What worked against an Access database:

strSelCount = "SELECT count(gender)/" & strRecCount & " as GenderCnt
FROM facttbl WHERE gender = 'female'"
set rs = cn.Execute(strSelCount)
Response.Write("Females: " & FormatPercent(rs("GenderCnt")) & vbcrlf )

and also worked of course with the divisional number hardcoded......

strSelCount = "SELECT count(gender)/588 as GenderCnt FROM facttbl WHERE
gender = 'male'"
set rs = cn.Execute(strSelCount)
Response.Write("Males: " & FormatPercent(rs("GenderCnt")) & vbcrlf )

I cannot get to work against the SQL database, it's returning 0 with no
errors....

I'm getting my record count number with no problems with

set rs1 = oConn.execute("SELECT COUNT(pk) as RecCount FROM facttbl")
strRecCount = rs1("RecCount")

and passing it in as

set rs2 = oConn.execute("SELECT count(gender)/" & strRecCount & " as
GenderCnt FROM facttbl WHERE gender = 'male'")
Response.Write("Males: " & FormatPercent(rs2("GenderCnt")) & vbcrlf )

I've ran the rs2 query directly through Query Analyzer and it parses
fine but returns a 0 as well. So I must assume I am making a  mistake
with the calculation portion of the query.

Any assistance is greatly appreciated.

TIA, 

MP


More information about the thelist mailing list