I have a form on our Web site where visitors enter data. I use cfinsert to insert this data into an MS access table.
Most of the time, this form works. However, I occassionally get the following error: Error Executing Database Query. Application uses a value of the wrong type for the current operation.
I am unable to duplicate the error myself, and have no idea why I only get this error sporadically.
Any ideas?
Thank you!
I don't know what version you're using but you might look at http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18952
You might also consider converting the cfinsert to a regular insert statement
<cfquery ...>
INSERT INTO YourTable (Columns..)
VALUES (....)
</cfquery>
I don't know what version you're using but you might look at http://www.adobe.com/cfusion/knowledgebase/index.cfm?id=tn_18952
You might also consider converting the cfinsert to a regular insert statement
<cfquery ...>
INSERT INTO YourTable (Columns..)
VALUES (....)
</cfquery>
Thank you, cfAllie. I'm using MX7.
I'm not sure if I want to use the insert statement, because none of my form fields are required, and if they are not filled out, I get Element formfield is undefined in FORM errors.
There are a lot of fields on the form, so cfinsert just seemed like the better solution.
Thank you, cfAllie. I'm using MX7.
I'm not sure if I want to use the insert statement, because none of my form fields are required, and if they are not filled out, I get Element formfield is undefined in FORM errors.
There are a lot of fields on the form, so cfinsert just seemed like the better solution.
I understand. The problem with cfinsert is that when it works, it works well.. but when it doesn't work its a lot tougher to debug. I use regular inserts with optional fields all the time. I prefer it.
Is it possible the problem is related to the size of the values inserted? That might explain the sporadic nature of the error. If possible can you post the table structure and database type?
Sorry, I meant which "datasource" type :) Is it Access or Access with Unicode?
It's MS Access with Unicode. The table is huge, so I don't know if I'll post the structure. However, I did put a MAXLENGTH on all form fields to make sure that they couldn't enter too much data. I also have some hidden form fields that include a date, but since it works so much of the time, I don't see how that could be the problem.
Do you have any memo fields? I was able to reproduce the error using a simple table with a memo field like below. I don't remember the limits for the unicode driver.
Table1
ID Autonumber
Name Text 50
Notes Memo
Datasource
Microsoft Access with Unicode
CLOB (Checked)
Long Text Buffer (64000)
<cfset form.name = "Alan">
<cfset form.notes = "">
<cfloop from="1" to="640" index="x">
<cfset form.notes = listAppend(form.notes, "0123456789")>
</cfloop>
<cfoutput>#Len(form.notes)#</cfoutput>
<cfinsert datasource="AccessUnicodeDSN" tablename="Table1" formfields="Name,Notes">
Thank you so much for trying to help me!
Nope, no memo fields. And I've tried the form myself, entering all kinds of values of varying sizes, and everything works. Is there a way to get the name of the field that is causing the error?
Strangely the same values work with a regular insert.
<cfquery name="InsertRow" datasource="AccessUnicodeDSN">
INSERT INTO Table1 (Name, Notes)
VALUES
(
<cfqueryparam value="#form.Name#" cfsqltype="cf_sql_varchar" null="#not len(form.Name)#">,
<cfqueryparam value="#form.Notes#" cfsqltype="cf_sql_clob" null="#not len(form.notes)#">
)
</cfquery>
Thank you so much for trying to help me!
Nope, no memo fields. And I've tried the form myself, entering all kinds of values of varying sizes, and everything works. Is there a way to get the name of the field that is causing the error?
Unfortunately, I've never heard of that error occuring except with memo fields.
I don't know. I have full debugging turned on and I can't see the sql statement sent to the database. Do you check your logs to see if they contain any more information?
You probably tried this but ... did you use values that you know are invalid for certain field types. Like if the max length is 50 did you try entering a value that is 100 characters long? Have you tried switching to the regular Access driver? Assuming you don't need Unicode support.
I have tried entering long values, but since I've set the MAXLENGTH, it stops me. What I've decided to do is have the form fields emailed to me before the form is submitted, so that mayble I can find which field is causing the error. Thank you so much for all your help!
I meant before the form was inserted in the database, not before it was submitted!
I meant before the form was inserted in the database, not before it was submitted!
I understood what you meant :) That sounds like a good plan. Once you have identified the problematic values you can determine whether the cfinsert can be fixed or if you need to convert it to a regular insert. I'm curious what the problem is so when you figure it out please post back. Good Luck!
Hi CfAllie! The mystery is solved, sort of. I found the offending form submission. Have you ever heard of autohitsite.info? Somehow, they are filling out my form with a bunch of weird data.
Additionally, they have answers for select box fields that are not part of the select box answers.
Also, the URL that they are coming from is not the one that calls our form.
I'm relieved that my form works, but not sure how they are submitting this strange data.
Thank you for all your help!
Hi CfAllie! The mystery is solved, sort of. I found the offending form submission. Have you ever heard of autohitsite.info? Somehow, they are filling out my form with a bunch of weird data.
Additionally, they have answers for select box fields that are not part of the select box answers.
Also, the URL that they are coming from is not the one that calls our form.
I'm relieved that my form works, but not sure how they are submitting this strange data.
Thank you for all your help!
Never heard of it. Is it possible you're being spammed?
Yes! I'm being spammed. Any solutions to stopping spam during form submission?
Try adding a captcha image to the form submission. They're not fool-proof but they help minimize spam.
This is sort of related: So if you use Access w/unicode then memo field length is limited?
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.