Skip to main content
D
dwright65
Known Participant
September 23, 2010
Question

CFUPDATE problem with field name beginning with a number

  • September 23, 2010
  • 4 replies
  • 1238 views

I have a form (actually several) that is gathering data for a survey. I tried to use cfupdate to update the data elements and got a string index out of range error. I tested again without the form fields that began with numbers and it worked just fine. I then tried to enclose the offending field names in () and []. Both times I got same error. Does anyone have a work around for this? The code and error are below.

Changing the field names is not really an option. This survey has almost 400 data points and most of the fields begin with the question number.

Thanks

DW

<cfupdate datasource="#application.fdp_datasource#" tablename="arra_survey1" dbtype="ODBC" formfields = "entry_no, 2_a_fy09, '2_a_fy10', '2_a_fy11', '2_a_fy12', '2_b_fy09', '2_b_fy10', '2_b_fy11', '2_b_fy12', '2_c_fy09', '2_c_fy10', '2_c_fy11', '2_c_fy12', '2_d_fy09', '2_d_fy10', '2_d_fy11', '2_d_fy12', '2_e_fy09', '2_e_fy10', '2_e_fy11', '2_e_fy12', '2_f_fy09', '2_f_fy10', '2_f_fy11', '2_f_fy12', '2_g_fy09', '2_g_fy10', '2_g_fy11', '2_g_fy12', '2_h_fy09', '2_h_fy10', '2_h_fy11', '2_h_fy12', '2_i_fy09', '2_i_fy10', '2_i_fy11', '2_i_fy12', '2_j_fy09', '2_j_fy10', '2_j_fy11', '2_j_fy12', '2_k_fy09', '2_k_fy10', '2_k_fy11', '2_k_fy12', '2_l_fy09', '2_l_fy10', '2_l_fy11', '2_l_fy12', central_office, dept_office, incl_other, 3_a_fy09, 3_a_fy10, 3_a_fy11, 3_a_fy12, 3_b_fy09, 3_b_fy10, 3_b_fy11, 3_b_fy12, 3_c_fy09, 3_c_fy10, 3_c_fy11, 3_c_fy12, 3_d_fy09, 3_d_fy10 3_d_fy11, 3_d_fy12, 3_e_fy09, 3_e_fy10, 3_e_fy11, 3_e_fy12, 3_f_fy09, 3_f_fy10, 3_f_fy11, 3_f_fy12, 3_g_fy09, 3_g_fy10, 3_g_fy11, 3_g_fy12, 3_h_fy09, 3_h_fy10, 3_h_fy11, 3_h_fy12, 3_i_fy09, 3_i_fy10, 3_i_fy11, 3_i_fy12, 3_j_fy09, 3_j_fy10, 3_j_fy11, 3_j_fy12, 3_k_fy09, 3_k_fy10, 3_k_fy11, 3_k_fy12, 3_l_fy09, 3_l_fy10, 3_l_fy11, 3_l_fy12">

String index out of range: -2

The error occurred in C:\Inetpub\wwwroot\directory\process_arra_suvey_2.cfm: line 12

12 : <cfupdate datasource="#application.fdp_datasource#" tablename="arra_survey1" dbtype="ODBC" formfields = "entry_no, 2_a_fy09, '2_a_fy10', '2_a_fy11', ...

This topic has been closed for replies.

4 replies

S
Steve Sommers
Legend
September 23, 2010

My suggestion is to never use CFUPDATE or CFINSERT - there are too many limitations and handcuffs associated with these tags. Using CFQUERY gets you closer to where the rubber meets the road.

-
-__cfSearching__-
Inspiring
September 23, 2010

Coincidentally, there was a thread about cfinsert/cfupdate on houseoffusion.com today. Though I am not a fan, there was one interesting comparison between cfinsert/cfupdate and ORM.

http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61975

-
-__cfSearching__-
Inspiring
September 23, 2010

I think you have run one of CFUPDATE's limitations.  I am not 100% certain, but I do not think cfupdate provides a way to escape invalid column names like yours.  In which case you may need to do a regular UPDATE within a  cfquery instead... or rename your columns.

fields that began with numbers

Most databases discourage creating column names that start with a number (or other restricted characters).  While many databases will allow you to escape invalid names, it is simpler all around to avoid them altogether.  Otherwise, you may have to escape the names in every single query. The recommendations vary, but usually object names that begin with a  letter, and contain only letters, numbers and underscores are considered  safe.

This survey has almost 400 data points and most of the fields begin with the question number.

Four-hundred (400) is a lot of columns for a single table.  The fact that they all them seem to contain the same type of information (ie response to a question) is an indication that data should probably be stored in a separate table, as rows, not columns.  For example questions could be stored in one table, possible answers in another, and the results of an individual test/survey stored in a third table.  A structure like that is much easier to query, can easily accommodate changes (add/remove questions, responses, etcetera) without having modify the table every time.

A
Adam Cameron.
Inspiring
September 23, 2010

Probably take the seingle quotes off your column names.

And for all the typing you're doing, why not ditch <cfupdate> and just use <cfquery> so you can use decent SQL rather than the shite CF generates for you?

--
Adam

J
JR__Bob__Dobbs
Inspiring
September 23, 2010

I would replace the use of CFUPDATE with a CFQUERY containing a SQL update statement.

A
Adam Cameron.
Inspiring
September 23, 2010

Hehheh.  Snap.

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices