Skip to main content
A
Anonymous
October 14, 2008
Answered

Stored Procedure to Add Record not adding record

  • October 14, 2008
  • 3 replies
  • 2028 views
I have never successfully added a record via a stored procedure, but I have been successful with adding records via server behaviors and using recordsets with stored procedures, so I know the connections work. I have the data in MS SQL Server and the web page is ColdFusion. When I enter the data and click "Submit", absolutely nothing happens. When I check the SQL table, no record has been added. Any help would be greatly appreciated. Here is my Stored Procedure:


This topic has been closed for replies.
Correct answer JR__Bob__Dobbs-qSBHQ2
Replace:

<cfif isdefined ("Form.Amt_Principle") and Form.Amt_Principle NEQ "">
<cfprocparam cfsqltype="cf_sql_decimal" value="#Form.Amt_Principle#">
<cfelse>Null</cfif>


With:

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))#">


http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_14.html#1102102

3 replies

A
Anonymous
October 15, 2008
SUCCESS!!!

Thanks Bob, cfSearching and Simon, you guys ROCK.
A
Anonymous
October 15, 2008
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."
S
Stressed_Simon
Inspiring
October 15, 2008
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!
A
Anonymous
October 14, 2008
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.
J
JR__Bob__Dobbs-qSBHQ2
Inspiring
October 14, 2008
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.
http://msdn.microsoft.com/en-us/library/ms190315(SQL.90).aspx

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
-
-__cfSearching__-
Inspiring
October 14, 2008
.. Also check your cfsqltypes. cf_sql_money and cf_sql_date are not listed as valid types for MS SQL. The matrix mapping is:

MS SQL type "money" -> CF_SQL_DECIMAL
MS SQL type "datetime" -> CF_SQL_TIMESTAMP

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices