Getting totals in a CFC

[Newsgroup_User]

I am trying to use two SQL calls and set a couple variables to gather the
balance of projects left for a member.

I first find out how many projects they have total for their account.
(available)
Then I use the recordcount to find out how many projects they current have
active. (used)

Then I subtract the active projects from the total and get the
'projectsleft'

What did I do wrong in this component and how can I output the final number
(projectsleft)

I want to create a component on another page and use the 'projectsleft'.

<cfcomponent>
<cffunction name="Balance" access="remote" returntype="numeric">
<cfquery name="UsedProjects" datasource="#Request.MainDSN#">
Select title
FROM projects
Where dirname = '#SESSION.MM_username#'
</cfquery>
<cfset used = #UsedProjects.RecordSet#>
<cfquery name="AvailProjects" datasource="#Request.MainDSN#">
Select projects
FROM Members
Where username = '#SESSION.MM_username#'
</cfquery>
<cfset available = #AvailProjects.project#>
<cfset projectsleft = available - used>
<cfreturn projectsleft>
</cffunction>
</cfcomponent>


Thanks!

--
Wally Kolcz
Developer / Support

[Dan_Bracuk]

What did you do wrong? This
<cfset used = #UsedProjects.RecordSet#>
does not give a recordcount.

and this
<cfset available = #AvailProjects.project#>
does not give you a number.

Plus if I remember correctly, cfc's do not recognize session variables.

That's the stuff that is definitely wrong. You are also being inefficient. You should be able to get the answer in one query.

[Teddy P]

CFCs do recognize session variables, unless you are storing the CFC in the application as an object.

[Newsgroup_User]

My CFCs recognize sessions.

I noticed the error on my first one. It should be RecordCount, not
RecordSet. I apologize, it was late.

Why wouldn't my AvailProjects.projects give a number? It is a number in the
'projects' column set to INT. Wouldn't that produce a number?

I am not that great at SQL queries. What should I be looking for to get the
result from one SQL query?

Help me be effective.

[Newsgroup_User]

I appreciate your submission to my question, but I am asking cause I don't
know. I apologize for my lack of knowledge. If I knew how to do it
efficiently I would not be asking.

Also, I know the item is wrong. That is why I am asking how make it right.

"Dan Bracuk" <webforumsuser@macromedia.com> wrote in message
news:e9o4fe$aqf$1@forums.macromedia.com...
> What did you do wrong? This
> <cfset used = #UsedProjects.RecordSet#>
> does not give a recordcount.
>
> and this
> <cfset available = #AvailProjects.project#>
> does not give you a number.
>
> Plus if I remember correctly, cfc's do not recognize session variables.
>
> That's the stuff that is definitely wrong. You are also being
> inefficient.
> You should be able to get the answer in one query.
>

[Dan_Bracuk]

Without knowing your table structure, it's hard to give the exact answer. I thought your 2nd query would retun a list of projects. If you are storing the actual number instead of calculating it, you could have problems keeping it up to date.

In any event, the following thread shows how to do math by selecting from sub-queries. There are probably better ways for your particular situation, but it might give you some ideas.

http://www.adobe.com/cfusion/webforums/forum/messageview.cfm?catid=3&threadid=1174212&enterthread=y

[Newsgroup_User]

Ok, the only column I need from the 'members' table is 'projects' (which is
a number)
I then need to count the projects currently in the 'project' table based on
the column 'dirname' which equals #Session.MM_username#
I need to take the number from the 'project' column from the 'members' table
and subtract from it the total number from the count of all projects from
the 'project' table and output that number to variable which can be passed
on to the requesting page.

Does that help?