 |  |  |  |  |  |  |  |
MS Access Error - sometimes
Please support our ColdFusion advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
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!
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/knowled...fm?id=tn_18952
You might also consider converting the cfinsert to a regular insert statement
<cfquery ...>
INSERT INTO YourTable (Columns..)
VALUES (....)
</cfquery>
You might also consider converting the cfinsert to a regular insert statement
<cfquery ...>
INSERT INTO YourTable (Columns..)
VALUES (....)
</cfquery>
•
•
•
•
Originally Posted by cfAllie 
I don't know what version you're using but you might look at http://www.adobe.com/cfusion/knowled...fm?id=tn_18952
You might also consider converting the cfinsert to a regular insert statement
<cfquery ...>
INSERT INTO YourTable (Columns..)
VALUES (....)
</cfquery>
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.
•
•
•
•
Originally Posted by lafalot
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.
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?
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">
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?
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>
<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>
•
•
•
•
Originally Posted by lafalot
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?
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.
Last edited by cfAllie; Apr 11th, 2007 at 8:47 pm.
|
Similar Threads
- Access error 3111 (Visual Basic 4 / 5 / 6)
- VB6 Access error Please Please Help !!! (Visual Basic 4 / 5 / 6)
- VAIO Recovery ERROR, operating system error, disk access error (Troubleshooting Dead Machines)
- "Error on page" (Web Browsers)
- No License Error Message when trying to open Access (MS Access and FileMaker Pro)
Other Threads in the ColdFusion Forum
- Previous Thread: Navigate between next/prev within the same category
- Next Thread: CF Running, but no Connection
| Thread Tools | Search this Thread |
You need to join our community in order to build your list of favorite forums. You can visit the forum index for a listing of all forums.
Latest ColdFusion Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
