Trouble with Update Query

[radiance]

Can someone tell me what is wrong with my update query?

I am trying to update 2 different records with the same CategoryID, but different PartsID and collectionID. I think I am on the right track with a loop, but I am not sure whether I have the right type of loop. Secondly, the records are updating, but they are inserting LISTS, which I don't want. How can I avoid the LIST?

So, record 1 is a yellow postcard. I need to update the collections that the postcard belongs to. I really need help on this because I have similar programs. But if I can't get this, then my other programs will fail when I need to update. ANY HELP IS GREATLY APPRECIATED.

Also, could someone recommend a site on looping logic?

Here's the query:

<cfif IsDefined ("Form.FieldNames")>

<cfloop index="i" list="#Form.FieldNames#" delimiters=",">
<cfif LEFT (i,4) IS "UPD_">
<cfset selectpartsID=RemoveChars(i,1,4)>
<cfset selectpartsID=#Evaluate(selectpartsID)#>
</cfif>

<cfquery name="Update" datasource="#request.dsn#">
UPDATE TESTmuseum_parts
SET
collectionID='#collectionID#'
WHERE PartsID=#selectpartsID#
</cfquery>
</cfloop>

 

[webmigit]

<cfif IsDefined ("Form.FieldNames")>
  <cfloop index="i" list="#Form.FieldNames#"> [blue]<!--- *4 --->[/blue]
    <cfif [red]left(i,4)[/red] IS "UPD_"> [blue]<!--- *3 --->[/blue]
      [red]<cfset selectpartsID=listdeleteat(i,1,"_")>[/red] [blue]<!--- *2 --->[/blue]
      [red]<cfset selectpartsID=FORM[selectpartsID]>[/red] [blue]<!--- *5 --->[/blue]
      <cfquery name="Update" datasource="#request.dsn#">
        UPDATE TESTmuseum_parts
          SET collectionID='#collectionID#'
        WHERE PartsID=#selectpartsID#    
      </cfquery>
    [red]</cfif>[/red] [blue]<!--- *1 --->[/blue]
  </cfloop>

*1 moved this cfif down here because I don't think the cfquery needs to run when its not the right type of field.

*2 You'll find that this code does the same thing as your ReplaceChars() version.

*3 I've never tried spaces with functions.. and since it appears to work, it may not be friendly to older versions of cf.

*4 You don't need to set comma as a delimiter, it is the default delimiter.

*5 I'm not sure of your entire structure so thsi line may not work, you may have to revert to the old version. But try it this way. This way is the preferred method, you should try not to use the Evaluate() function if you can avoid it.

Most of this was just improvements, your only syntax error was *1 (*3) might have been an issue.

I think this should fixeyour problems but you need to provide some sample values for the variables... examples of what they ARE and what they SHOULD BE.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[radiance]

Thank You webmigit. Here's a better example of what I am trying to do.

I have to update a record of a posting. The posting has its unique id. The posting may belong to 2 or more different collections.

Inserting multiple collections for a record is fine (they are inserted into another table (the parts table) as separate records. The problem is UPDATING those records in the parts table.

So, if Posting Record 163, belongs to collections with CollectionIDs of 5, 6, and 12. Inside of the parts table, the Posting Record 163, has 3 different partsIDs:

Parts TABLE
PartsID CollectionID PostingRecordID
130 5 163
131 6 163
132 12 163

I need to UPDATE Posting Record 163 (with a variety of combinations):

Parts TABLE
PartsID CollectionID PostingRecordID
130 9 163
131 11 163
132 12 163

OR
Parts TABLE
PartsID CollectionID PostingRecordID
130 9 163
131 6 163
132 12 163

The problem is that with my current update, all three of the records are updating, but with a list (which is not what I want):

Parts TABLE
PartsID CollectionID PostingRecordID
130 9,6,12 163
131 9,6,12 163
132 9,6,12 163

I can not have a list. How can I update and get past this problem? I need to understand the logic because I also have other applications that rely on this type of structure.

 

[webmigit]

<cfif IsDefined ("Form.FieldNames")>
  [red]<cfset ihold=0>[/red]
  <cfloop index="i" list="#Form.FieldNames#">
    [red]<cfset ihold = i + 1>[/red]
    <cfif left(i,4) IS "UPD_">
      <cfset selectpartsID=listdeleteat(i,1,"_")>
      <cfset selectpartsID=FORM[selectpartsID]>
      <cfquery name="Update" datasource="#request.dsn#">
        UPDATE TESTmuseum_parts
          SET collectionID='[red]#listgetat(collectionID)#[/red]'
        WHERE PartsID=#selectpartsID#    
      </cfquery>
    </cfif>
  </cfloop>

The changes in red may be enough. Try this.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[webmigit]

OOPS try this... forgot to specify the list element in listgetat

<cfif IsDefined ("Form.FieldNames")>
  [red]<cfset ihold=0>[/red]
  <cfloop index="i" list="#Form.FieldNames#">
    [red]<cfset ihold = i + 1>[/red]
    <cfif left(i,4) IS "UPD_">
      <cfset selectpartsID=listdeleteat(i,1,"_")>
      <cfset selectpartsID=FORM[selectpartsID]>
      <cfquery name="Update" datasource="#request.dsn#">
        UPDATE TESTmuseum_parts
          SET collectionID='[red]#listgetat(collectionID,[b][blue]ihold[/blue][/b])#[/red]'
        WHERE PartsID=#selectpartsID#    
      </cfquery>
    </cfif>
  </cfloop>

The changes in red may be enough. Try this.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[radiance]

Do I still need list as a part of the loop even though I don't want a list? I think this is what I am confused about the most.

 

[webmigit]

list is just an attribute. And your using the most common method of ascertaining variable names that you're not quite certain of, its fine.

list is just an attribute of the loop... you're not telling it to output differently.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[radiance]

I get an error about selectpartsID or "can not insert value for collectionID, must be a number (I changed collectionID to int in my table)

Here's the form page:
<cfoutput query="ItemList">
<tr><tr><td colspan="2"><hr width="100%"></td></tr> <tr><td class="boldtext">Currently in Collection:</td>
<td class="boldtext"><input type="checkbox" name="#PartsID#" value="#CollectionID#" checked>#CollectionID#</td>
</tr>

<cfloop query="showAllCollections">
<tr>
<td class="boldtext"><input type="checkbox" name="CollectionID" value="#CollectionID#">#CollectionID#: #Collection_type#</td></tr>
</cfloop>
</cfoutput>

<tr><td colspan="2" align="center">
<input type="submit" value="Update"></td><br><br>

</tr>

 

[radiance]

Sorry..

I get this error as well:

ihold = i + 1


what is the hold for?

 

[webmigit]

oops, change the i + 1 to ihold + 1

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[radiance]

Thanks for your help. sorry to be a pest. I am a bit confused, the update works, but it is not inputting the other values.

For example, this is what I am getting now:

PartsID CollectionID PostingID
130 5 163
138 5 163

This is what i should be getting:

PartsID CollectionID PostingID

130 5 163
138 7 163

What am I doing wrong?

Also, what is the purpose of "hold"

2, if I need to update the given records and add additional ones, can that be done?

 

[radiance]

Actually,

I made a change (the loop did not work without the evaluate):
<cfif IsDefined ("Form.FieldNames")>
<cfset ihold=0>
<cfloop index="i" list="#Form.FieldNames#">
<cfset ihold = ihold + 1>
<cfif left(i,4) IS "UPD_">
<cfset selectpartsID=listdeleteat(i,1,"_")>
<cfset selectpartsID=#Evaluate(selectpartsID)#>
<cfquery name="Update" datasource="#request.dsn#">
UPDATE TESTmuseum_parts
SET collectionID='#listgetat(collectionID,ihold)#',
modify_date=GETDATE()
WHERE PartsID=#selectpartsID#
</cfquery>
</cfif>
</cfloop>


and it's updating the separate records, but I get the following error:

An ERROR occurred while evaluating the expression:


#listgetat(collectionID,ihold)#



Error near line 36, column 30.
--------------------------------------------------------------------------------

In function ListGetAt(list, index [, delimiters]) the value of index, which is 3, is not a valid index for the list given as a the first argument (this list has 2 elements). Valid indexes are in the range 1 through the number of elements in the list


The error occurred while processing an element with a general identifier of (#listgetat(collectionID,ihold)#), occupying document position (36:29) to (36:59).


Do i need to put another variable within the listgetat function?