•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ColdFusion section within the Web Development category of DaniWeb, a massive community of 391,668 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,976 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ColdFusion advertiser:
Views: 5426 | Replies: 17
 |
•
•
Join Date: Apr 2007
Posts: 28
Reputation: 
Rep Power: 2
Solved Threads: 0
Light Poster
MS Access Error - sometimes 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!
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
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>
•
•
Join Date: Apr 2007
Posts: 28
Reputation:
Rep Power: 2
Solved Threads: 0
Light Poster
•
•
•
•
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>
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.
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
•
•
•
•
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.
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?
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
Sorry, I meant which "datasource" type 
Is it Access or Access with Unicode?
Is it Access or Access with Unicode? •
•
Join Date: Apr 2007
Posts: 28
Reputation:
Rep Power: 2
Solved Threads: 0
Light Poster
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.
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
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">
•
•
Join Date: Apr 2007
Posts: 28
Reputation:
Rep Power: 2
Solved Threads: 0
Light Poster
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?
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
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>
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
•
•
•
•
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?
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.
Last edited by cfAllie : Apr 11th, 2007 at 7:47 pm.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb ColdFusion Marketplace
Linear Mode
- 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
