Multiple Record Insert

[schmedi]

Hey All!!

I'm kinda new to building web applications, and I need some much needed help
with a problem that I just can get around. Any assistance will be greatly
appreciated.

In my application I am trying have a user insert a rating (Yes, No, Not
Applicable) against list of Skills. The list of skills is produced dynaically
against the skills assigned to that user. So when the user looks at their
assessment sheet it lists all their relevant skills, and gives them the option
of selecting a rating against each skill. I passed the Skill_ID to a hidden
field and the UserRating is a List Menu with List Values.

When inserting their rating into the database I need to pass the Skill_ID and
corresponding UserRating into the database. I used a CFLOOP INDEX to loop
through all the UserRating form elements, producing the rating for each Skill.
No problem. However when trying to pass the corresponding Skill_ID, I get an
error which indicates that the value of the Skill_ID is in fact all of the
Skill_ID values in a comma seperated list.

I have used this INSERT query successfully before, however the Skill_ID always
remained constant.

<cfloop index="Insert_Multiple_Records" list="#Form.Userrating#">
<cfquery name="#Insert_Multiple_Records#" datasource="ttecdb">
INSERT INTO Assessment (Skill_ID, UserRating)
VALUES ('#Form.Skill_ID#', '#Insert_Multiple_Records#')
</cfquery>
</cfloop>

[cgsj_usa@yahoo.com]

I think that you should be looping over the skill set, not the user_rating. Just a thought. Hope that it helps. Thanks.

Chris

[schmedi]

Hi Chris,

Thank you for the response. I have switched the two form elements around, so
that the loop now runs over the Skill_ID and now it doesn't give an error
anymore, however the query inserts all of the user ratings against every skill.
The result looks like this:

Assessment_ID Skill_ID UserRating
85 10
Yes, Yes, Yes, Yes
86 22
Yes, Yes, Yes, Yes
87 31
Yes, Yes, Yes, Yes
88 5
Yes, Yes, Yes, Yes

The new code looks like this:

<cfloop index="Insert_Multiple_Records" list="#Form.Skill_ID#">
<cfquery name="#Insert_Multiple_Records#" datasource="ttecdb">
INSERT INTO Assessment (Skill_ID, Userrating)
VALUES ('#Insert_Multiple_Records#', '#Form.UserRating#')
</cfquery>
</cfloop>

[travelinrob]

I think you might need to look at how you name your form fields. Do they each
have a unique name? It doesn't appear that way as you write '#form.skil_id#'. I
would think that you would have fields like form.skil_id1, form.skil_id2, etc.

[schmedi]

Hi, any suggestions on how I can create the dynamic name for each of the form SELECT elements that are produced by the CFOUTPUT?

[travelinrob]

If you post a snipet of your HTML form it will be easier to assist you.

But, if you want to figure it out for yourself (which I am sure you can do),
just create a incremental loop around the inputs and use the index as part of
the form element name, ie. myfield#idx#. Then you only have to write your
skill/rating inputs once.

Rob

[schmedi]

Hi Rob,

Thank you for the help so far, much appreciated. Just one more hurdle and we
are there!

OK, this is what I have managed so far. My insert query uses the
Form.Skill_ID element as the index of the loop and inserts the values of the
Skill_ID and Userrating_ form elements on each pass. I concatenated the string
Userrating_ to the CurrentRow property of the query to get a different name for
each form element using a loop over query output, which gives me Userrating_1,
Userrating_2, Userrating_3 etc.

Here is the form code:
<form method="POST" name="form1" id="form1">
<cfloop query="User_Skills">
<select name="Userrating_<cfoutput>#User_Skills.CurrentRow #</cfoutput>"
class="TextBlack" id="Userrating_<cfoutput>#User_Skills.CurrentRow# </cfoutput>">
<option value="No" selected>No</option>
<option value="Yes">Yes</option>
<option value="NA">N/A</option>
</select>
<input name="Skill_ID" type="hidden" id="Skill_ID"
value="<cfoutput>#User_Skills.Skill_ID#</cfoutput>">
</cfloop>
<input name="Submit" type="submit" class="TextBlack" value="Submit">
</form>

Now for the insert query. As mentioned I now use the Skill_ID form element as
the index, so this form element name doesn't have to be unique. The
Userrating_xxx form element on the other hand has to be unique and now is,
because of the concatenation with the CurrentRow value. The challenge now is
how do you make the VALUE '#Form.Userrating_xxx#' in the query the correct
value for each pass of the query. Any suggestions?

Query Code:
<cfloop index="Insert_Multiple_Records" list="#Form.Skill_ID#">
<cfquery name="#Insert_Multiple_Records#" datasource="xxx">
INSERT INTO Assessment
Skill_ID, UserRating)
VALUES
'#Insert_Multiple_Records#', '#Form.Userrating_xxx#')
</cfquery>
</cfloop>

[travelinrob]

I personally would just use a counter variable, but using currentrow, just add
currentrow to the skill_ID.

<CFOUTPUT><input name="Skill_ID_#User_Skills.CurrentRow#" type="hidden"
id="Skill_ID_#User_Skills.CurrentRow#"
value="#User_Skills.Skill_ID#"></CFOUTPUT>

Then, userrating_1 relates to skill_id_1.

[travelinrob]

What am I saying? Sorry, I'm trying to get out of work. Forget my last message.

Get rid of the skill_id input and name the userrating select userrating_#skill_id#.

[schmedi]

Hi Rob,

I have done some extra work on this problem and have taken a slightly
different route which so far seems like it could be the solution. However I
have a syntax problem in my INSERT SQL query.

I used the currentRow property of my output query (User_Skills) and
concatenated that to the string Userrating_ to create a unique name for each of
my select tags that increment with every output (Userrating_1, Userrating_2,
Userrating_3). I added the hidden field again for the Skill_ID, so that the
query can loop over this field as the index and store in the Userrating_x field
against the Skill_ID value. My form code looks like this.

<form method="POST" name="form1" id="form1">
<cfloop query="User_Skills">
<select name="Userrating_<cfoutput>#User_Skills.CurrentRow #</cfoutput>"
class="TextBlack" id="Userrating_<cfoutput>#User_Skills.CurrentRow# </cfoutput>">
<option value="No" selected>No</option>
<option value="Yes">Yes</option>
<option value="NA">N/A</option>
</select>
<input name="Skill_ID" type="hidden" id="Skill_ID"
value="<cfoutput>#User_Skills.Skill_ID#</cfoutput>">
</cfloop>

The problem now lies with the query, which as I said loops over the Skill_ID
element of the form. For each pass it concantenates two variables urname
(Form.Userrating_) and urcount (numeric value starting at 1) to form the same
name as the form select items.
The query code is as follows.

<cfset urname = 'Form.Userrating_'>
<cfset urcount = 1>
<cfloop index="Insert_Multiple_Records" list="#Form.Skill_ID#">
<cfquery name="#Insert_Multiple_Records#" datasource="ttecdb">
INSERT INTO Assessment
(Skill_ID, UserRating)
VALUES
('#Insert_Multiple_Records#', '#variables.urname & variables.urcount#')
</cfquery>
<cfset urcount = urcount + 1>
</cfloop>

At present the above query runs successfully, but inserts the text string
rather than the select tag value. See below.

Skill_ID | Userrating
22 | Form.Userrating_1
13 | Form.Userrating_2
35 | Form.Userrating_3

The problem seems to be that the I can't preserve the pound signs after the
concantenation (urname & urcount) has been done in the query. I have tried
many different formats without any success. Do you have any suggestions?

[travelinrob]

Two things.

1. On your display page, I would use the skill_id instead of the currentrow
and I would move the skill_id input outside the loop and use valuelist. That
would be much cleaner.

2. You need to use EVALUATE() around your concatenated expression on the back
side.

[schmedi]

Hi Rob,

Thank for the advice. It's working now!! Hee haaa!!! Although I didn't use
Valuelist, so what I've done is to just use the Evaluate() function, to pass
the variable into my query. I created another variable where the other two
variables (string and counter) are concantenated, moved this inside my cfloop
query, and then passed the new value to the Evaluate() function without the
quotes. The form code remains the same and the new query code is as follows:

<cfset urcount = 1>
<cfloop index="Insert_Multiple_Records" list="#Form.Skill_ID#">
<cfset urname = 'Form.Userrating_'>
<cfset urcode = urname & urcount>
<cfquery name="#Insert_Multiple_Records#" datasource="ttecdb">
INSERT INTO Assessment
(Skill_ID, UserRating)
VALUES
('#Insert_Multiple_Records#', '#Evaluate(urcode)#')
</cfquery>
<cfset urcount = urcount + 1>
</cfloop>

Thank you so much for all the help. I will do a bit of studying on Valuelists
and will see if I can use this as the prefferred method. Thanks for the help!