Update multiple records with one submit?

[lovewebdev]

I can't for the life of me figure out how to do this. I have checkboxes with primary key id's listed as the value in a repeat region. I want to perform an update on 2 columns for all id's checked.

SUDEO CODE

<form>

<cfoutput query ....

other textfields...

           <input name="checkbox" type="checkbox" id="checkbox" value="#table.id#" />

</cfoutput>

SUBMIT BUTTON

</form>

My update statement:

UPDATE table
SET column1 = 1, column2 ='whatever'
WHERE id IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#TRIM(FORM.checkbox)#">)
</cfquery>

It only updates if I check one box, does nothing when more than one is selected.

I also tried looping over the update statement but nothing seems to work.

[Dan_Bracuk · Accepted solution]

You need to use the list attribute of cfqueryparam.

[mack_]

You were missing the list attribute to cfqueryparam (you can also

replace the cfif with a null attribute on the cfqueryparam but it has

the disadvantage of sending the query to the db: null="#Not

Len(Trim(form.checkbox))#").

<cfif Len(Trim(form.checkbox))>
<cfquery ...>
UPDATE table
SET column1 = 1, column2 = 'whatever'
WHERE id IN (<cfqueryparam cfsqltype="cf_sql_integer"
value="#form.checkbox#" list="true">)
</cfquery>
</cfif>

Mack

[lovewebdev]

Oh man! Thanks so much I was debuging this for 2 hours and  going nuts over that one missed attibute!

What's sad is that I used that attribute before, but not often enough I guess...

Why is it that all results in google searches kept saying to loop over the query!! Sometimes I feel like Coldfusion doesnt have enough good representation, enough good practice examples, on the web.

[lovewebdev]

Oops. Wait anothe rissue.

I tIt worked when I ticked all the checkboxes but now I tried ticking only one and it didnt update anything?

When I submit one checkbox, the error says that checkbox isn't defined?? How could it be defined when more than one are submitted, but not when 1 is submitted?

I'm testing all this locally with direct access to the db.

[lovewebdev]

Ok sorry. No I nested the <cflocation> I wanted to happen after the update before the ending </cfquery> tag accidentally. good to go again.

[mack_]

cfdump the value of form.checkbox before doing the update to see what

it contains.

Mack