update multiple records from single from

[smack1]

I need to update multiple records in a table from a form. When I pass the data to the action page it tries to put all of the information in to the fields in the first record. When I look in the debugger it shows all of the different fields in list format.
Parameters
RecordID=10,11
Icode=998260,998245
qty=48,48

The error says the form entries are incomplete or Invalid.

Is there a way to loop through the cfupdate one record at a time.

I know the cfupdate isn’t the method of choice, I will use an alternative method if there are suggestions.

I would rather pass all of the form info at one time as opposed to making the user update one record at a time.

Thanks in Advance,
Matt

 

[CFDude]

Matt,

The easiest way to do it is to name each of the form fields with the record ID and pass the list of record ID's to update. Then loop through the list of ID's passed and update the database. I don't use cfupdate and I usually recommend not using it. I don't think you can use it in this instance anway.
e.g on your form:


<form action=&quot;page2.cfm&quot; method=&quot;post&quot;>
<input type=&quot;Hidden&quot; name=&quot;lstRecordIDs&quot; value=&quot;10,11&quot;>

<input type=&quot;Text&quot; name=&quot;Icode_10&quot;><br>
<input type=&quot;Text&quot; name=&quot;qty_10&quot;><br>
<br>

<input type=&quot;Text&quot; name=&quot;Icode_11&quot;><br>
<input type=&quot;Text&quot; name=&quot;qty_11&quot;><br>
<br>


</form>


Page 2.cfm

<cfloop list=&quot;#form.lstRecordIDs#&quot; index=&quot;idx&quot;>

<cfquery name=&quot;qryUpdateRec&quot; datasource=&quot;myDsn&quot;>
Update myTable
Set ICode = #Evaluate(&quot;form.Icode_#idx#&quot#,
Qty = #Evaluate(&quot;form.qty_#idx#&quot#
</cfquery>

</cfloop>
I use this when I dynamically build the form of records to update.
The Evaluate Function will find the value of the form field that corresponds to the Record Number. If you need more assistance in setting the sql query, let me know.

 

[smack1]

Thanks, I'll try that tomorrow and let you know how it comes out.

Matt

 

[smack1]

I'm afraid I have made a mess of it. I get an OBDC Error when I run it. I have included the form information, the action page with the update query and the error. The data looks like it is being passed and processed ok. (my non-expert opinion.)

<form action=&quot;updatebuyerspromo.cfm&quot; method=&quot;post&quot;>
<CFoutput query=&quot;bull_promoitems&quot;>

<Input type=&quot;Text&quot; name=&quot;desc#itemid#&quot; value=&quot;#desc#&quot; size=&quot;50&quot;><br>

<Input type=&quot;Text&quot; name=&quot;icode#itemid#&quot; value=&quot;#icode#&quot;><br>

<Input type=&quot;Text&quot; name=&quot;pk_sz#itemid#&quot; value=&quot;#pk_sz#&quot;>

<Input type=&quot;Text&quot; name=&quot;RegCost#itemid#&quot; value=&quot;#numberformat(RegCost,999.99)#&quot;><br>

<Input type=&quot;Text&quot; name=&quot;DealCost#itemid#&quot; value=&quot;#numberformat(DealCost,999.99)#&quot;><br>

<Input type=&quot;Text&quot; name=&quot;unitcost#itemid#&quot; value=&quot;#numberformat(unitcost,999.99)#&quot;><br>

<Input type=&quot;Text&quot; name=&quot;Allowance#itemid#&quot; value=&quot;#numberformat(Allowance,999.99)#&quot;><br>

<input type=&quot;Hidden&quot; name=&quot;itemid&quot; value=&quot;#itemid#&quot;><!--- key field in db table --->

<input type=&quot;Hidden&quot; name=&quot;itemid#itemid#&quot; value=&quot;#itemid#&quot;>

</cfoutput>
<input type=&quot;Submit&quot;>


This is the action page query:

<cfloop list=&quot;#form.itemid#&quot; index=&quot;idx&quot;>
<CFQUERY NAME=&quot;pormoitmupd&quot; DATASOURCE=&quot;dsn&quot;>
Update gen_BuyersPromoitems
Set icode = '#Evaluate(&quot;form.icode#idx#&quot#',
pk_sz = '#Evaluate(&quot;form.pk_sz#idx#&quot#',
desc = '#Evaluate(&quot;form.desc#idx#&quot#',
RegCost = #Evaluate(&quot;form.RegCost#idx#&quot#,
Allowance = #Evaluate(&quot;form.Allowance#idx#&quot#,
DealCost = #Evaluate(&quot;form.DealCost#idx#&quot#,
unitcost = #Evaluate(&quot;form.unitcost#idx#&quot#&quot;
</cfquery>
</cfloop>

I have tried it with and with out a &quot;where&quot; statement. itemid is the Key field in the insert table.

This is the error I get:

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

SQL = &quot;Update gen_BuyersPromoitems Set icode = '998260', pk_sz = '48', desc = 'FunPack Camera Display 62251', RegCost = 180.00, Allowance = 10.00, DealCost = 171.00, unitcost = 4.00&quot;&quot;

Data Source = &quot;dsn&quot;

The error occurred while processing an element with a gneral identifier of (CFQUERY), occupying document position (7:2) to (7:58) in the template file c:\inetpub\

http://wwwroot\bulletins\updatebuyerspromo.cfm.

I hope I have included all the needed info. Any assistance is appreciated.
Thanks in advance,
Matt

 

[CFDude]

Matt,
A couple of things -- you have an extra double quotes at the end of the query
unitcost = #Evaluate(&quot;form.unitcost#idx#&quot#&quot; <===

That is causing the error.

Also, you do not have a where clause, which will update everything to the last value on the list.

Try:
<CFQUERY NAME=&quot;pormoitmupd&quot; DATASOURCE=&quot;dsn&quot;>
Update gen_BuyersPromoitems
Set icode = '#Evaluate(&quot;form.icode#idx#&quot#',
pk_sz = '#Evaluate(&quot;form.pk_sz#idx#&quot#',
desc = '#Evaluate(&quot;form.desc#idx#&quot#',
RegCost = #Evaluate(&quot;form.RegCost#idx#&quot#,
Allowance = #Evaluate(&quot;form.Allowance#idx#&quot#,
DealCost = #Evaluate(&quot;form.DealCost#idx#&quot#,
unitcost = #Evaluate(&quot;form.unitcost#idx#&quot#
Where itemid = #idx#
</cfquery>

 

[smack1]

Sorry, I haven't responded sooner... But, you know how it goes when other projects get bumped ahead of the one you are working on. So, of course now, this one is High Priority because it should already be done.

Oh well, now that I got my rant out of the way...

I have the above SQL update working, except for this line

desc = '#Evaluate(&quot;form.desc#idx#&quot#',

it bombs and gives me the old error.

[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.


SQL = &quot;Update gen_BuyersPromoitems Set icode = '998260', pk_sz = '48', desc = 'FunPack Camera Display 62251', RegCost = 180.00, Allowance = 10.00, DealCost = 171.00, unitcost = 4.00 where itemid = 10&quot;

If I comment this line out it works fine. All records are updated.

The Desc field is a text data type on an Access 2000 data base. The form is populated by a query to the table and the data may or may not be changes when submitted to this page.

This is the whole query:

<cfloop list=&quot;#form.itemid#&quot; index=&quot;idx&quot;>
<CFQUERY NAME=&quot;pormoitmupd&quot; DATASOURCE=&quot;cd_webdata_test&quot;>
Update gen_BuyersPromoitems
Set icode = '#Evaluate(&quot;form.icode#idx#&quot#',
pk_sz = '#Evaluate(&quot;form.pk_sz#idx#&quot#',
<!--- The problem is here --->
desc = '#Evaluate(&quot;form.desc#idx#&quot#',
<!--- To here --->
RegCost = #Evaluate(&quot;form.RegCost#idx#&quot#,
Allowance = #Evaluate(&quot;form.Allowance#idx#&quot#,
DealCost = #Evaluate(&quot;form.DealCost#idx#&quot#,
unitcost = #Evaluate(&quot;form.unitcost#idx#&quot#
where itemid = #idx#
</cfquery>
</cfloop>

I hope I have included all the needed info.
Your assistance is appreciated.
Thanks in advance,
Matt

 

[CFDude]

Matt,
That is strange. The only thing that I can think of is maybe &quot;desc&quot; is not the correct name of the field name in the db or are you putting any single quotes in the text field?

Just to trouble shoot, create a test page with a cfquery with the information &quot;hard coded&quot; so to speak and see if it inserts there.

 

[smack1]

Thanks again for the responce.

The name is correct and the data is straight text.

I haven't tried hard coding it. I'll give it a shot.

We'll see...

 

[CFDude]

Just a thought -- check the database -- is the size correct for the amount of text you are inserting? (e.g, you may have it set to 25 characters, but you are inserting more than that)
HTH,
Tim P.

 

[smack1]

No, the field size in the db is set to 50 and the most I think I tried to update is about 33 char.

This is the longest enrty:

FunPack Camera Gravity Feed 32150

So far I have just been letting it update itself with out changes.

Oh, well I will try your suggestion later or tomorrow.

Thanks

 

[smack1]

Well call me Stupid! I tried everything we said earlier. None of it worked. I even recreated that field in the db. So, I went through the db and templates and changed the name of the db field from &quot;desc&quot; to &quot;description&quot;. It worked. I really don't understand. &quot;desc&quot; was spelled consistently through out when I did the find and replace. I dragged and dropped the names into the templates.

Well, that problem is out of the way anyway. Thanks for your help on the whole looping through query solution. I know we all benefit from your input in this forum.

Thanks again,
Matt