Get the output of one iteration of the insert statement by outputting the end result of the SQL. Take the SQL statement and execute it directly against your DB. If the insert works fine, it may be how you handle different data types.
If it continues to elude you and you are comfortable with stored procedures and your database supports them, abstract the insert and see if you can create a generic insert that reduce your CF considerations for the data types by using stored proc params to format your data types. ----- Original Message ----- From: "Daniel Kessler" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Thursday, May 24, 2007 10:40 AM Subject: insert error > I've been at this for awhile and I just don't see the error. The > error is: > ORA-00936: missing expression which leads me to believe I have the > wrong number of values, but in checking and rechecking and > rechecking, I don't see the problem. I even gave up on the loop and > wrote it all out. > > here's the code: > > <cfset field_list = listToArray > ("meal_plan,written_budget,check_food,read_ads,grocery_list,unit_pricing > ,use_coupons,compare_prices,buy_quantity,generic_brands,community_resour > ces,food_eaten,budget_knowledge,budget_run_out,budget_money,budget_time, > budget_options,budget_no_problems,budget_prob_oth,budget_prob_oth_text,i > nterests_planning,interests_comparing,interests_pricing,interests_money, > interests_healthyBudget,interests_budget,budg_int_other,budg_int_other_t > ext")> > > <!--- tmp_line is read in else where, then in the following lines > made into an array ---> > <cfset tmp_line = "12/2/2004|20:38:25|[EMAIL PROTECTED]|annteeka| > gilmore|2|2|1|2|2|2|3|1|2|1|3|2||1|1|1||||||||1|1||||"> > <cfset tmp_line = replace(tmp_line,"||","| |","ALL")> > <cfset tmp_line = replace(tmp_line,"||","| |","ALL")> > <cfset tmp_trn_ar = listToArray(tmp_line,"|")> > > <!--- make a date ---> > <cfset date_ar = listtoArray(tmp_trn_ar[1],"/")> > <cfset time_ar = listtoArray(tmp_trn_ar[2],":")> > <cfset the_date_added = createDateTime(date_ar[3],date_ar[1],date_ar > [2],time_ar[1],time_ar[2],time_ar[3])> > > > <CFQUERY NAME="addItem" DATASOURCE="eatart"> > INSERT INTO fsnep_ha_budgeting > ( > budgeting_id, > login_id,stage, > <cfloop from="1" to="#arrayLen(field_list)#" index="ii"> > #field_list[ii]#, > </cfloop> > > date_added > ) > VALUES > ( > unique_ha_budgeting_s.NEXTVAL, > <cfqueryparam value="#getID.login_id[1]#" > cfsqltype="CF_SQL_NUMERIC">,<cfqueryparam value="1" > cfsqltype="cf_sql_varchar">, > <!--- skips date, time, email, > first name, last name ---> > <cfloop from="6" to="#arrayLen(tmp_trn_ar)#" index="ll"> > #trim(tmp_trn_ar[ll])#, > </cfloop> > > #the_date_added# > ) > </CFQUERY> > > > -- > > Daniel Kessler > > College of Health and Human Performance > University of Maryland > Suite 2387 Valley Drive > College Park, MD 20742-2611 > Phone: 301-405-2545 > http://hhp.umd.edu > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create Web Applications With ColdFusion MX7 & Flex 2. Build powerful, scalable RIAs. Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJS Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:279069 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
