Problem inserting fields of datatype Money 2

[dnayana]

First, thanks in advance to anyone who may be able to assist me.

I haven't worked with CF for 2+ years and am trying to get back to re-acquiring my skills in this language. Needless to say, I'm having a rough time.

Within a table, I have three fields that are of datatype "money". (I'm using MS SQL Server 2000; existing records were imported from MS Access)

Sample (money) field of cfform:

<cfinput type="Text"
	label="Sign On"
	name="curSignOn" 
	required="no"
	message="Sign On Bonus must be a valid numeric amount!"
	validate="numeric"
	validateAt="onSubmit,onServer"
	size="10"
	bind="{qryCandidateList.selectedItem.curSignOn}"/>

Within the action page, I define default values if no value was entered by user.

<cfparam name="Form.curSalary" default="0">
<cfparam name="Form.curSignOn" default="0">
<cfparam name="Form.curDeclineSalaryAmnt" default="0">

When trying to add a record, I continue to receive errors. I've made NUMEROUS attempts (these are just a few) to no avail.

//1st Attempt
Tried this within action, and received the following error "Invalid data '' for CFSQLTYPE CF_SQL_DOUBLE."

Values( ...
<cfqueryparam value="#Form.curSalary#" cfsqltype="CF_SQL_MONEY ">,
<cfqueryparam value="#Form.curSignOn#" cfsqltype="CF_SQL_MONEY ">, 
<cfqueryparam value="#Form.curDeclineSalaryAmnt#" cfsqltype="CF_SQL_MONEY ">, ...)

//2nd & 3rd Attempt
... I tried this, and received the following error: "Invalid data CONVERT(money, ) for CFSQLTYPE CF_SQL_DOUBLE. " I then put quotes around the Form fields (i.e. '#Form.curSalary#', and received the same only with "CONVERT(money, '')"

Values( ...
<cfqueryparam value="CONVERT(money,  #Form.curSalary#)" cfsqltype="CF_SQL_MONEY ">, 
<cfqueryparam value="CONVERT(money,  #Form.curSignOn#)" cfsqltype="CF_SQL_MONEY ">, 
<cfqueryparam value="CONVERT(money, #Form.curDeclineSalaryAmnt#)" cfsqltype="CF_SQL_MONEY ">, ...)

//4th Attempt

<!--- I commented out the cfparam code for those fields --->

... I tried entering in values within those fields to see what would happen and I recevied the following error: Optional feature not implemented

I can edit a record with no problem. I even have the
following within the update portion: SET ... curSignOn=CONVERT(money,'#Form.curSignOn#') and the record is successfully updated. (However, I'm not sure if this is correct or if it will lead to future problems -- please let me know if this correct)

I don't want to change my fields to that of varchar. The field is a currency value and as such, I want its datatype to reflect it. I'm at a straight loss at what I could be doing wrong.

Any assistance is GREATLY APPRECIATED!!


Thanks,
Nicole

 

[r937]

<voice type="homer">
stupid sql server money datatype!!
</voice>

have you tried inserting with cfsqltype="CF_SQL_DECIMAL"

r937.com | rudy.ca

 

[dnayana]

Hi r937!

<voice type="marge">
[italic] You telling me!!![/italic]
</voice>

LOL

First thank you for taking time out to respond.

I left my cfparam values in place and changed the cfsqltype as you indicated ... I then received the following error:

Invalid data '' for CFSQLTYPE CF_SQL_DECIMAL.

 

[r937]

and what was the value that it was barfing on?

r937.com | rudy.ca

 

[dnayana]

The debugger informs me that the error occured on the line beginning the insert action. I can see the form values entered via the cfdump. Since I didn't enter a value for that field (i.e. curSalary), I'm making my assumption on that it is barfing on this

<cfparam name="Form.curSalary" default="0">

While I can see my values in the cfdump, the debugger section (i.e. the blue box) doesn't display any values.

This is what the debugger window informs me ...
Invalid data '' for CFSQLTYPE CF_SQL_DECIMAL. The error occurred in C:\ColdFusion8 \

http://wwwroot\NGAMATCandidate\actions\candidateaction.cfm:

line 85 ...

Well, line 85 is this ...

<cfquery datasource="#application.DSN#">
      INSERT INTO tblCandidate(txtLastName, txtFirstName, dteInterview, intStatus, intHiringManager, dteStart, curSalary, intLevel, memStatusNotes, intHomeTeam, intPeerSponsor, intRecruiter, txtInterviewer, dteRecruited, dteAssigned, txtRecruitEvent, intPrimeLCSJobRole, intDegree, intEdLevel, intYearsExp, intClearance, dteProjectStart, intSecondLCSJobRole, intCompany, curSignOn, intDegreePrgm, memComments, intTargetProject, txtOffice, txtReqNo, txtTitle, intDeclineReason, curDeclineSalaryAmnt, txtProjectStatus, intHighLvlEduc)
      VALUES(
<cfqueryparam value="#Form.txtLastName#" cfsqltype="CF_SQL_VARCHAR">, 
<cfqueryparam value="#Form.txtFirstName#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#Form.dteInterview#" cfsqltype="CF_SQL_DATE ">, 
<cfqueryparam value="#Int(Val(Form.intStatus))#" cfsqltype="CF_SQL_INTEGER ">, 
<cfqueryparam value="#Int(Val(Form.intHiringManager))#" cfsqltype="CF_SQL_INTEGER ">, 
<cfqueryparam value="#Form.dteStart#" cfsqltype="CF_SQL_DATE ">,
<cfqueryparam value="#Form.curSalary#" cfsqltype="CF_SQL_DECIMAL">, 
<cfqueryparam value="#Int(Val(Form.intLevel))#" cfsqltype="CF_SQL_INTEGER ">, 
<cfqueryparam value="#Form.memStatusNotes#" cfsqltype="CF_SQL_VARCHAR">, 
<cfqueryparam value="#Int(Val(Form.intHomeTeam))#" cfsqltype="CF_SQL_INTEGER ">, ....

BTW, I have

...value="#Int(Val(Form.intStatus))#"

because I was first receiving this error: Invalid data for CFSQLTYPE CF_SQL_INTEGER. I was able to find a resolution for that problem. Even though, in my prior CF experience, just putting something like this

...value="#Form.intStatus#" cfsqltype = "CF_SQL_INTEGER"

worked with no problem.

 

[r937]

a simple 0 should not cause that error

at worst, you could try this --

<cfparam name="Form.curSalary" default="0.0">

r937.com | rudy.ca

 

[dnayana]

r937,

I tried that too. I even removed the quotes and still received the same error.

I've been seeing red for the past week+ on this error.

I just don't know what it could be ... SMS (Shrugging My Shoulders).

I'm determined to resolve this problem w/o changing the datatype.

Again, thank you for your assistance. I greatly appreciated you taking time out to respond.

 

[dnayana]

r937 and Members,

If my statement

I'm determined to resolve this problem w/o changing the datatype.

came off as being rude or anything, I didn't mean it to be. Just a little frustrated with this error ... And I really don't want to change the datatype to varchar as (IMO) that's poor db design.

I still welcome any resolutions to this problem.

I also welcome any recommendations for any ActionScript books or sites too!


Thanks,
Nicole

 

[r937]

didn't bother me, in fact, i understand feelings of frustration only too well myself



r937.com | rudy.ca

 

[dnayana]

I'm even more puzzled ...

As indicated in prev post, I have cfparam values for various fields.

<cfparam name="Form.curSalary" default="0">

So, I decided to add the following to the page to see the actual output since non-entered values weren't displaying in my cfdump.

<b>Salary =<cfoutput>#Form.curSalary#</cfoutput></b>

And I receive the following: Salary =

No value is displayed.. Originally, I assumed it wasn't displaying within the cfdump because I didn't actually enter data within the fields.

But, if I defined the value within the cfparam, shouldn't the output be "Salary = 0"???

Ughhh!!! If the above is correct, then none of my <cfparam> values will be inserted in the database. Am I doing something wrong??? I have 14 other forms that I can add and edit with no problem. And they don't even have cfparam values defined within them. This is the MAIN entry form that the users will be using and its not working.

Please somebody, tell me what I'm doing wrong. Because I just can't see it at this point.

 

[cfSearching]

dnayana,

As r937 said, the correct cfsqltype for ms sql money fields is cf_sql_decimal. There should be no problem inserting valid "money" values like 0 or 0.0.

I suspect the problem is that you are misunderstanding how cfparam works. The "default" value is only used when the form field does not exist. That is not the same as saying "this default value will be used when the form field is blank". Text fields always exist on the action page. If they are left blank, their value is an empty string. So because your text fields exist, the cfparam default values are never applied.









----------------------------------

http://cfsearching.blogspot.com/

 

[dnayana]

Thank you cfsearching for responding!

I removed the <cfparam> and left the cfsqltype as cf_sql_decimal intact as you and r937 suggested. But, I'm still receiving the same error: Invalid data '' for CFSQLTYPE CF_SQL_DECIMAL.

I have checked and triple checked to ensure the values correspond with the corresponding fields of the insert statement and each field line up correctly.

 

[dnayana]

BTW,

I took it a step further, for testing purposes and I entered values in those fields.

And receive the following error:


Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Optional feature not implemented

 

[cfSearching]

But, I'm still receiving the same error: Invalid data '' for CFSQLTYPE CF_SQL_DECIMAL.

Yes. You can only insert numeric values into a money column. If you leave the field blank its value is an empty string "". Obviously "" is not a numeric value, so an error occurs.

You must verify the value is numeric before attempting to insert it. Another option is to use the val() function to convert the value to a number, or 0 if it cannot be converted.

----------------------------------

http://cfsearching.blogspot.com/

 

[cfSearching]

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]Optional feature not implemented

Out of curiousity, why are using ODBC Socket instead of JDBC?

----------------------------------

http://cfsearching.blogspot.com/

 

[dnayana]

cfSearching,

How do I go about using JDBC???

Connecting the DSN via ODBC is how I was taught (via former mentors, not formal training), so that's what I've been accomstomed(sp) to. (I don't have a more logical reason for using ODBC). But if there is a another and more efficient way, then I'm all for learning and implementing it.

I removed the original DSN and changed the "select valid driver" to both SQL Server and SQL Native Client and no success.

I have a feeling that what you're mentioning is what needs to be done (I just don't know how to implement it), because I just tried doing

<cfqueryparam value="#Val(Form.curSalary)#" cfsqltype="CF_SQL_DECIMAL">,[code] and still received the "Optional feature not implemented" error.

BTW, thank you for the previous explanations.  I understood it.

 

[dnayana]

Thank you r937 & cfSearching for your assistance!

cfSearching, I did as you instructed re: JDBC Driver (obviously what I did in prev response was still within the ODBC -- lack of sleep is to blame! LOL)... I removed the old DSN and re-created it using Microsoft SQL Server as the driver. (I restarted the CF service to ensure changes took place).

I set up various IsNumeric functions on the appropriate fields within the Insert query code and whalla! It worked!

So, THANK YOU, THANK YOU, THANK YOU, TOO THE BOTH OF YOU