I have a dynamic query that I was trying to use the <cfstoredproc> tag with,
but all I kept getting was the generic "Unknown data access" error. So I
re-wrote it with <cfquery> and it works fine, but I can't see any difference
in the two. Can anyone here see why the <cfstoredproc> version wouldn't
work?
<cfstoredproc> version (unknown data access error):
<cfstoredproc procedure="sp_web_cf_softrevs_insert_update"
datasource="#request.app_vars.warranty_dsn#">
<cfprocparam type="In" dbvarname="@row_id"
cfsqltype="CF_SQL_INTEGER" value="#form.row_id#"/>
<cfprocparam type="In" dbvarname="@mfg" cfsqltype="CF_SQL_VARCHAR"
value="#form.mfg#"/>
<cfprocparam type="In" dbvarname="@tech_type"
cfsqltype="CF_SQL_VARCHAR" value="#form.tech_type#"/>
<cfprocparam type="In" dbvarname="@begdate" cfsqltype="CF_SQL_DATE"
value="#form.begdate#"/>
<cfprocparam type="In" dbvarname="@enddate" cfsqltype="CF_SQL_DATE"
value="#form.enddate#"/>
<cfprocparam type="In" dbvarname="@notes" cfsqltype="CF_SQL_VARCHAR"
value="#form.notes#"/>
<cfoutput query="qColumns">
<cfprocparam
type="In"
dbvarname="@#col_sql_name#"
cfsqltype="#CFSQLType(col_type)#"
value="#StructFind(form, col_sql_name)#"
maxlength="#col_length#"
/>
</cfoutput>
</cfstoredproc>
<cfquery> version (works):
<cfquery name="qSoftrevInsertUpdate"
datasource="#request.app_vars.warranty_dsn#">
sp_web_cf_softrevs_insert_update
@row_id = <cfqueryparam cfsqltype="CF_SQL_INTEGER"
value="#form.row_id#"/>,
@mfg = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.mfg#"/>,
@tech_type = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.tech_type#"/>,
@begdate = <cfqueryparam cfsqltype="CF_SQL_DATE"
value="#form.begdate#"/>,
@enddate = <cfqueryparam cfsqltype="CF_SQL_DATE"
value="#form.enddate#"/>,
@notes = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.notes#"/>,
<cfloop query="qColumns">
<cfoutput>@#col_sql_name#=</cfoutput>
<cfqueryparam
cfsqltype="#CFSQLType(col_type)#"
maxlength="#col_length#"
value="#StructFind(form, col_sql_name)#"
/>
<cfif CompareNoCase(qColumns.CurrentRow,
qColumns.RecordCount)>,</cfif>
</cfloop>
</cfquery>
The qColumns query contains a list of dynamic columns that appear or
dissapear on the form based on mfg. CFSQLType is a udf I wrote that returns
the cold fusion cfsqltype given a SQL Server data type.
This is running on Win2k, CF5, SQL Server 2000 with all of the latest
patches.
Thanks in advance,
Andy
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
