[thelist] SQL Query Help after upgrade from Access

Michael Pack michaelpack at wvdhhr.org
Tue Sep 14 13:34:20 CDT 2004


Ken,

Thanks much for the expert help. I finally got it to work casting as
FLOAT. When I cast as Decimal I was getting mismatch errors using
FormatPercent around the record set write and could not get the Cdec
function to trick the errors. I've had so many folks tell me upgrading
Access to SQL requires no coding changes, except connection. They are
SOOOO wrong!

Thanks again.

MP


>>> Ken Schaefer <ken.schaefer at gmail.com> 9/13/2004 8:13:47 PM >>>
Two things:

a) You are running into a type issue. COUNT() returns an integer.
Dividing an integer by another integer results in an integer. Integers
are whole numbers - so the closest whole number to your result happens
to be 0.

b) You don't need to use two queries - you can do this in one query,
and avoid all the inline, messy SQL.

SELECT
   CAST(COUNT(facttbl.tableID) AS Decimal) / 
   SELECT CAST(COUNT(facttbl.tableID) AS Decimal)
FROM
   facttbl
WHERE
   Gender = 'male'

You could even put that into a stored procedure:

CREATE PROC usp_GetMales

    @MalePercentage   int   OUTPUT

AS

   SELECT
      @MalePercentage = 
      CAST
      (
         CAST(COUNT(facttbl.tableID) AS Decimal) / 
         SELECT CAST(COUNT(facttbl.tableID) AS Decimal) * 100
         AS Integer
      )
   FROM
      facttbl
   WHERE
      Gender = 'male'

GO

and then you ASP code would be:

Set objCommand = Server.CreateObject("ADODB.Command")
Set objCommand.ActiveConnection = objConn
objCommand.Parameters.Append _
   objComand.CreateParameter("@MalePercentage", adInteger,
adParamOutput, 4)
objCommand.Execute

intPercentage = objCommand.Parameters("@MalePercentage").Value 
Set objCommand = Nothing

Cheers
Ken



On Mon, 13 Sep 2004 16:46:04 -0400, Michael Pack
<michaelpack at wvdhhr.org> wrote:
> 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
> --
> 
> News! - Evolt.org conference for web professionals.
> 17-19 September 2004 in Toronto, Canada.
> Details at http://TOevolt.org 
> 
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/ 
> 
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org 
> Workers of the Web, evolt !
>
-- 

News! - Evolt.org conference for web professionals. 
17-19 September 2004 in Toronto, Canada. 
Details at http://TOevolt.org 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/ 

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt ! 


More information about the thelist mailing list