Updating multiple records

[trojnfn]

I have this query :
<cfquery name="qry" datasource="dsname">
select line_item, part_no, qty
from tblName
where ref_no = '#form.ref_no#'
</cfquery>

Lets say that I return five records. I then display them in a table, in form mode, for editing

<form action="form_action.cfm" method="post">

<table>
<cfoutput query="qry">
<tr>
<td><input type="hidden" name="line_item" value="#qry.line_item#"></td>
<td><input type="text" name="part_no" value="#qry.part_no#"></td>
<td><input type="text" name="qty" value="#qry.qty#"></td>
</tr>
</table>

<input type="hidden" name="ref_no" value="#form.ref_no#">

<input type="submit" value="submit">

</form>

All five records are displayed and any column can be changed. When I go to the action page to update my table, how would I do that ?

Would I loop thru the update process five times, with where ref_no = '#form.ref_no#' and line_item = '#form.line_item#", or is there another way to do this. I have just updated single records, but not multiples like this.

Thanks for any suggestions.

[Kapitaine]

It's never a good idea to say, I'll loop through this form five times...because what if one day you want more fields...or less? You'd have to be changing code. It's always a good idea in situations like this to keep it as flexible and generic as possible.

Work with the idea that CF is ignorent and won't know much at all.

With this in mind, I would suggest looking at the form scope variables. For example, try this code and see what you get.

<cfif cgi.request_method eq "post">
Form Scope<br /><cfdump var="#form#" /><br />
Form.Fieldnames<br /><cfdump var="#form.fieldnames#" /><br />
</cfif>

Put that code at the top of your page and submit the form (you don't need your query yet to do this. See what CF gives you and choose what best to do with that data.

In an application of mine I loop through form.fieldnames (because it comes through quite conveniently as a list).

<cfloop index="i" list="#form.fieldnames#" delimiters=",">
<cfoutput>
Field = #i#<br />
</cfoutput>
</cfloop>

Good luck,
Mikey.

[The_ScareCrow]

I'm not sure if Kapitaine understood your question, or maybe I don't.

But the answer will depend upon the database your using.

Eg

In ms sql server you can pass an xml document and just have the one call to cfquery
In ms access and ms sql server you can have multiple insert statements in the one cfquery.

Ken

[-__cfSearching__-]

The ScareCrow wrote:
> In ms sql server you can pass an xml document and just have the one call to cfquery

I do not want to hijack the thread, but that sounds interesting. I am not familiar with it. Would you mind giving a brief description or posting a link Ken?

[The_ScareCrow]

It does sound insteresting and tempting doesn't it !!

The sql server BOL have some examples, here is just one

Enjoy

Ken

[-__cfSearching__-]

That is cool! I never knew about OpenXML. What is really interesting is that it automatically inserts null for any omitted attributes. Assuming the columns allow nulls.

Thanks! I definitely have to read more about this one.

[Newsgroup_User]

I'm not sure if Kapitaine understood your question, or maybe I don't.

But the answer will depend upon the database your using.

Eg

In ms sql server you can pass an xml document and just have the one call to
cfquery
In ms access and ms sql server you can have multiple insert statements in the
one cfquery.

Ken