This content has been marked as final. Show 14 replies
Okay, by changing the ColdFusion "MM_InsertRecord..." to an actual form element, it looks like it tried to process, but now I get an error "Invalid data for CFSQLTYPE CF_SQL_DOUBLE." I don't even have any SQL type DOUBLEs.
1. dump your form values and check that each one being passed to your stored procedure is a valid value for the associated parameter's data type.
2. You may wish to use SCOPE_IDENTITY() instead of @@IDENTITY in your query. SCOPE_IDENTITY() will retrieve the newest identity value set within the calling stored procedure.
3. The dbvarname attribute should be omitted. It is ignored in CF versions 6.0 and higher. There was a hotfix to enable this feature for CF 7 but as far as I know it is not available for CF8
.. Also check your cfsqltypes. cf_sql_money and cf_sql_date are not listed as valid types for MS SQL. The matrix mapping is:1 person found this helpful
MS SQL type "money" -> CF_SQL_DECIMAL
MS SQL type "datetime" -> CF_SQL_TIMESTAMP
Okay, I changed the "money" to "decimal" and "date" to "timestamp". Then when I ran it had an error about undefined NULL values, so I added the following code as well as removing the dbvarname and switching to SCOPE_IDENTITY:
Try the null attribute of cfprocparam and the YesNoFormat function to handle passing nulls to your stored procedure.
See attached code. (not tested)
Here is another sample, using cfparam to supply a default form value.
Blue Chrome wrote:1 person found this helpful
> Then when I ran it had an error about undefined NULL values,
What was the exact error message?
> And now I'm getting an "HTTP 500 Internal Server Error: There is a problem with the page you
> are trying to reach and it cannot be displayed."
That is a generic browser error which hides the real ColdFusion error message. What error message do you see after disabling "friendly http error messages" ?
Helpful little trick, thanks. Now the error replies:
Null Null Null Null Null
Please post your updated code and the full error message. Also, are you getting a CF error page or is "Null Null Null Null Null" being output to the browser?
Here is my cf code:
Okay, I moved the cfprocresult tag and IT WORKS! Now how do I get it to accept null values? If I leave anything null I get the error that reads (in a new window) "Null Null Null Null" and inside the error box says "[Macromedia][SQLServer JDBC Driver][SQLServer]Implicit conversion from data type datetime to decimal is not allowed. Use the CONVERT function to run this query."
Use the null attribute:-
<cfprocparam cfsqltype="cf_sql_varchar" value="#Form.Interest_Type#" null="#NOT len(trim(Form.Interest_Type))#>
This will use the value of Form.Interest_Type unless it is an empty string in which case it will pass a null to the stored procedure!
<cfif isdefined ("Form.Amt_Principle") and Form.Amt_Principle NEQ "">
<cfprocparam cfsqltype="cf_sql_decimal" value="#Form.Amt_Principle#">
I'd provide each form variable with a default value so that a value will always exist for each variable.
<cfparam name="form.Amt_Principle" default="" />
Use the null attribute, if the value is 'yes' the value NULL will be passed to the stored procedure parameter, else the contents of the value attribute will be used
<cfprocparam cfsqltype="cf_sql_decimal" value="#Form.Amt_Principle#" null="#YesNoFormat(not(IsNumeric(form.Amt_Principle))#">
Thanks Bob, cfSearching and Simon, you guys ROCK.