How to do it with CFLoop

Hi,

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
rkchopraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jdev011599Commented:
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,

jdev





0
Nathan Stanford SrSenior ProgrammerCommented:
Change the Names of the Ingredients to
"Ingredients_1,Ingredients_2,Ingredients_3,..."


So now you should have in your Form

These Fields
============
RecipeName
Preparation
Ingredients_1
Ingredients_2
Ingredients_3
etc

Some Code
================================


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

<!--- Insert the Recipe's --->
<cfquery datasource="YourDataSource" name="AddRecipes">
Insert into YourRecipetable
(RecipeName,
Preparation)
Values
('#recipevariable#',
'#preparationvariable#'
)
</cfquery>

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


<!--- 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
(Ingredients,
 IngredientsNum,
 RecipeID
 )
 VALUES
 (
  '#Ingredients#',
      #IngredientsNum#,
      #GetMax.MaxRec#
      )
</cfquery>
</cfloop>
</cftransaction>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nathan Stanford SrSenior ProgrammerCommented:
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?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

jdev011599Commented:
nathans,

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#')
  </cfif>
</cfloop>
0
jdev011599Commented:
correction - line 3 should read

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

(forgot the "form.")
0
Nathan Stanford SrSenior ProgrammerCommented:
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.
0
rkchopraAuthor Commented:
Thanks everybody helping in solving the prob. I am now able to add the data into the database.

Thanks and regards,
Rajesh

p.s. I have increased the nos. from 50 to 100.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.