[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