Help with CFSTOREDPROC

[Team_Source]

I am trying to use a CFSTOREDPROC but am getting an error I believe on the last query. We are pulling data from an MS Access db. Basically we are figuring a percentage.

The Access query is as follows: DailyProductionPercentage: ([NewTeamValueSummary]![TransTotal]/[NewTeamValueSummary]![WeeklyHoursWorked])*0.08

I have attached the ColdFusion code. I believe the error has something to do with the CFSQLType that I am using but I have tried several and get the same error on everything. What am I doing wrong?

Thanks.

[JR__Bob__Dobbs-qSBHQ2]

Try using CF_SQL_TIMESTAMP as the cfsqltype for your datetime fields.

[JR__Bob__Dobbs-qSBHQ2]

Limit the number of cfprocresult tags to one unless you expect many resultsets to be returned. In any case you have several that all have resultset="1".

[Team_Source]

Please forgive my ignorance but I am confused about the resultset. This query in Access brings back several results, therefore I assumed with each Procresult I needed a resultset, is that not the case?

[Wolfman_AZ]

You should number the resultsets sequentially, in your case:
<cfprocresult name="S_date" resultSet="1" maxrows="-1">
<cfprocresult name="E_date" resultSet="2" maxrows="-1">
etc.

I'm not sure why the maxrows is '-1'?

[Team_Source]

Thank you both for your assistance. After making the changes that were suggested, I am still getting the same error. Do these coding issue cause the initial error of Optional Feature not implemented?

[paross1]

quote:

---

This query in Access brings back several results

---

If you mean that your query in Access brings back many columns of data, but there is only one query, then you would only need one CFPROCRESULT. Since you have no IN parameters, then you would not need ANY CFPROCPARAM tags either. In other words, if this "stored procedure" is nothing more than a query stored in Access, then your stored proc would look like this:

<cfstoredproc procedure="Qry_finalTVS" dataSource="NBProdReports">
<cfprocresult name="yourQuery" resultSet="1" maxrows="-1">
</cfstoredproc>

You would then have a query named yourQuery, just as if you had performed a CFQUERY, and all of your columns would be available to ColdFusion by appending the query name, such as yourQuery.S_Date, yourQuery.E_Date, yourQuery.Manager_Name, etc., etc.In other words, treat yourQuery as if you had done a CFQUERY named yourQuery.

If your "stored procedure" is made up of multiple queries, then you would have multiple CFPROCRESULT tags, but no CFPROCPARAM tags.

The only time that you would ever need CFPROCPARAM OUT-type tags is if you actually had a stored procedure that returned individual values, and I'm not aware of a way that you could do that with Access. (Unlike Oracle or SQL Server).

Phil