Stored Procedure to Add Record not adding record

[Blue Chrome]

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:

[Blue Chrome]

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.

[JR__Bob__Dobbs-qSBHQ2]

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__-]

.. 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

[Blue Chrome]

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:

[JR__Bob__Dobbs-qSBHQ2]

Try the null attribute of cfprocparam and the YesNoFormat function to handle passing nulls to your stored procedure.

See attached code. (not tested)

[JR__Bob__Dobbs-qSBHQ2]

Here is another sample, using cfparam to supply a default form value.

[-__cfSearching__-]

Blue Chrome wrote:
> 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" ?

http://technet.microsoft.com/en-us/library/cc778248.aspx

[Blue Chrome]

Helpful little trick, thanks. Now the error replies:

Null Null Null Null Null

[JR__Bob__Dobbs-qSBHQ2]

Blue Chrome,

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?

[Blue Chrome]

Here is my cf code:

[Blue Chrome]

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."

[Stressed_Simon]

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!

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

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

[Blue Chrome]

SUCCESS!!!

Thanks Bob, cfSearching and Simon, you guys ROCK.