• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

How to do it with CFLoop


I have been using a Form to add data using Cold Fusion into the Access (2000) Database. I am giving the full details as follow:

I have been using two tables:

1. RecipeData
- Structure of the Table: RecipeID (AutoNumber), RecipeName (Text), Preparation (Text)

2. RecipeIngredients
- Structure of the Table: RecipeID (Number), Ingredients (Text)

I have prepared a Form for entering data into the database. In the form there are 2 text fields; one for RecipeName and another for Preparation + 15 Text fields for Entering Ingredients (name Ingredients1, Ingredients2... Ingredients15).

Can anybody help me writing script to add Data using following Rules:

1. In the RecipeID: It should pick the last record and Add 1 to auto generate the RecipeID.
2. Add the Data into the RecipeData Table According to the Form.
3. Add the data into the RecipeIngredients Table (using CFLoop Tag) (the Autogenerated RecipeID + 15 Ingredients)

Please help me out.

Thanks and regards,
Rajesh Kumar
  • 3
  • 3
1 Solution
Hello Rajesh,

This sounds very much like a school project to me, so I will be very general. Forgive me if I am wrong about this.

First off, I would use an autonumber to take care of that unique recipeID.

I prefer to use an entry form with an action page. I would give all the ingredients form fields the default value "__NULL__".

If you don't want to use an autonumber, you can select max(RecipeID) as MaxID from RecipeData. Pop it into a variable (NewID) and add 1 to it. Now you can use NewID in all your insert queries.

In the action page, grab your form.RecipeName and form.Preparation and insert them into your RecipeData table with NewID.

Now, the ingredients. I would suggest creating a list object. You can write a cfloop (idx from 1 to 15) that looks at the form parameters Ingredient + #idx# (to save all the code of checking each field). If the value of the form field is not "__NULL__" then append the value to the list.

Now cfloop the list you have created. Inside the loop, use a cfquery to insert the ingredient entries. You still have your NewID from above to use.

Hope this helps,


Change the Names of the Ingredients to

So now you should have in your Form

These Fields

Some Code

<!--- This lock the transaction in case someone else is trying to insert at the same time. --->

<!--- Insert the Recipe's --->
<cfquery datasource="YourDataSource" name="AddRecipes">
Insert into YourRecipetable

<!--- Get the Max Recipe Which should be the one you just inserted.
<cfquery datasource="YourDataSource" name="GetMax">
Select Max(RecipeID) as MaxRec
From YourRecipeTable

<!--- Loop thru the Form Fields and get the ones that have a '_' in it. and Insert them into a xref table for ingredients.
Link them together with 'RecipeID'. --->

<CFLOOP index="Form_element" list="#FORM.fieldnames#">
<CFSET Ingredients = #GetToken("#Form_Element#", 1,"_")#>
<CFSET IngredientsNum = #GetToken("#Form_Element#", 2,"_")#>
<cfquery datasource="YourDataSource" name="AddRecipes">
Insert into  YourIngredientstable

Jdev, Is your way a better way of doing it?  I have used this way in to different companies but I am not sure about your way?
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.


I'm not sure about better, but I have used code like this with no trouble before (keep in mind - this is off the top of my head, the syntax might not be exactly right!)

<cfloop index="I" from=1 to=15 step=1>
  <cfset tmp="">
  <cfset SetVariable(tmp,"Ingredients_#I#")>
  <cfif tmp is not "__NULL__">
  <cfquery name="insert1" datasource="ds">
  insert into mytable (f1,f2) values (#v1#, '#tmp#')
correction - line 3 should read

<cfset SetVariable(tmp,"form.Ingredients_#I#")>

(forgot the "form.")
I guess my program was in case he doesn't know how many ingredients would be processed.  There might be 15 for one recipe and 20 for the next and 5 for the next.

I like to see as many ways to do something as I can so I can get better ideas.  So thanks for showing us.
rkchopraAuthor Commented:
Thanks everybody helping in solving the prob. I am now able to add the data into the database.

Thanks and regards,

p.s. I have increased the nos. from 50 to 100.

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now