943,931 Members | Top Members by Rank

Web Development > ColdFusion > MS Access Error - sometimes
Ad:
  • ColdFusion Discussion Thread
  • Unsolved
  • Views: 8523
  • ColdFusion RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Apr 11th, 2007
0

MS Access Error - sometimes

Expand Post »
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!
Similar Threads
Reputation Points: 10
Solved Threads: 0
Light Poster
lafalot is offline Offline
35 posts
since Apr 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

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>
Reputation Points: 10
Solved Threads: 0
Unverified User
cfAllie is offline Offline
28 posts
since Mar 2007
Permalink
Apr 11th, 2007
0

lafalot

Click to Expand / Collapse  Quote 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.
Reputation Points: 10
Solved Threads: 0
Light Poster
lafalot is offline Offline
35 posts
since Apr 2007
Permalink
Apr 11th, 2007
0

Re: lafalot

Click to Expand / Collapse  Quote 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?
Reputation Points: 10
Solved Threads: 0
Unverified User
cfAllie is offline Offline
28 posts
since Mar 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

Sorry, I meant which "datasource" type Is it Access or Access with Unicode?
Reputation Points: 10
Solved Threads: 0
Unverified User
cfAllie is offline Offline
28 posts
since Mar 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

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.
Reputation Points: 10
Solved Threads: 0
Light Poster
lafalot is offline Offline
35 posts
since Apr 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

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">
Reputation Points: 10
Solved Threads: 0
Unverified User
cfAllie is offline Offline
28 posts
since Mar 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

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?
Reputation Points: 10
Solved Threads: 0
Light Poster
lafalot is offline Offline
35 posts
since Apr 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

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>
Reputation Points: 10
Solved Threads: 0
Unverified User
cfAllie is offline Offline
28 posts
since Mar 2007
Permalink
Apr 11th, 2007
0

Re: MS Access Error - sometimes

Click to Expand / Collapse  Quote 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 8:47 pm.
Reputation Points: 10
Solved Threads: 0
Unverified User
cfAllie is offline Offline
28 posts
since Mar 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in ColdFusion Forum Timeline: Navigate between next/prev within the same category
Next Thread in ColdFusion Forum Timeline: CF Running, but no Connection





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC