• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Update Logic- Not Updating

I have a simple update that i am trying to do, which will be used for my other applications.  But I can't seem to get the query right.  I posted this question on http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_21190373.html:, but never got did not get an answer after 15 days. could someone point me in the right direction?

I have 3 separate tables.  one of the tables is a collectionParts table, which has the fkeys from the other 2 tables:

Table 1
museum_parts:
PartsID [pkey, int], collectionID [int and fkey from collection table],  categoryID [int, pkey from main table]

Table 2
museum_collections
collectionID[pkey, int], collectionType

Table 3
museumPostings
categoryID [pkey, int], itemName, description

I am trying to update the collections that each item may belong in the museumParts table.  Some of the items may belong to 1 or several collections.  

So, here's a sample of what I need to do:
-Update PartsID RECORD X with COLLECTIONID B on Category Q
-Update PartsID RECORD A with COLLECTIONID V on Category Q

This postings is for the same item, but different COLLECTIONIDs as well as PartsID.  

I continue to get an error that PartsID can not be found or categoryID is unknown.

=================================
This is what I have on my actual update page:

<---BEGIN QUERIES--->
<cfquery name="showPartsDetails" datasource="#request.dsn#">
SELECT museum_parts.collectionID, museum_parts.categoryID, museum_parts.partsID
FROM museum_parts
INNER JOIN museum_collections
ON museum_parts.collectionID=museum_collections.collectionID
WHERE museum_parts.CategoryID=#CategoryID#
</cfquery>

<cfset partsList=ValueList(showPartsDetails.collectionID)>

<cfquery name="selectCategory" datasource="#request.dsn#">
select * from museum_collections
</cfquery>

<---BEGIN FORM--->
Collection:                  
<cfoutput><select name="showPartsDetails.collectionID" multiple size="5">
<option value="#showPartsDetails.collectionID#" selected>#partsList#</option>
<cfloop query="selectCategory">
<option value="#collectionID#">#collectionID#: #collection_type#</cfloop>
</option></select>
<input type="submit" name="SubmitButton" value="Make the changes">
<input type="Hidden" name="CollectionID" Value="#showPartsDetails.CategoryID#">      
<input type="Hidden" name="showPartsDetails.partsID" Value="#showPartsDetails.partsID#">
</cfoutput></cfform>
<---END FORM--->
=================================
This is what I have on the update action page (I have tried a few things, but this was my last option):  
<cfloop list="#form.collectionID#" index="FormcollectionID">
<cfquery name="updateParts" datasource="#request.dsn#">
UPDATE museum_parts
SET
collectionID='#lastID.collectionID#',
posting_date=GETDATE()
WHERE partsID = #partsID#
</cfquery>
</cfloop>


Why can I not update these fields??  



0
synergeticsoul
Asked:
synergeticsoul
  • 4
  • 2
1 Solution
 
danrosenthalCommented:
lastID.collectionID seems out of place.
0
 
thack111Commented:
You have a design problem in your tables:

you have
Table 1
museum_parts:
PartsID [pkey, int], collectionID [int and fkey from collection table],  categoryID [int, pkey from main table]

Table 2
museum_collections
collectionID[pkey, int], collectionType

Table 3
museumPostings
categoryID [pkey, int], itemName, description

This says that you can only have one record for each unique partID, and that record can only have 1 collectionID, and that collectionID must exist on Table2, Also you can have only 1 catagoryID, and it must exist on table3.

You say you want to have a part that can belong to mulitple collections.
You might try making another table to match the partID to it's multilpe CollectionIDs
PartsID [int fkey from table1], collectionID [int fkey from collection table]

this says you can have more than 1 of the same partsID and more that one collectionID.  You want to be carful because you can have more than one record the exact same.  Use your code in coldfusion to make sure that dosen't happen.

I would look at your table design closer

if you know that you will never need more that three collections per part try making more fields col1, col2, col3...

Hope that helps
0
 
synergeticsoulAuthor Commented:
Thank You. I will try modifying my table design since my data is still small.

However, how can I successfully update the loop.  

Per my original question, I have a drop down menu.  The user can successfully insert multiple collections per object, but I am stuck on properly updating.

Currently, the update is shown in a drop-down list, with the current collections highlighted.  I want the user to be able to select several collections if necessary for the update.

So, ie.  Object 41 may be a part of collection A and collection B.  In the drop down, due to the follwoing query and LIST option (I could not get all of the collections for Object 41 or others to show, so I created a list):  

<cfquery name="showPartsDetails" datasource="#request.dsn#">
SELECT museum_parts.collectionID, museum_parts.categoryID, museum_parts.partsID
FROM museum_parts
INNER JOIN museum_collections
ON museum_parts.collectionID=museum_collections.collectionID
WHERE museum_parts.CategoryID=#CategoryID#
</cfquery>

<cfset partsList=ValueList(showPartsDetails.collectionID)>
<cfset partsID=ValueList(showPartsDetails.PartsID)>

I am showing the current collections for the object as highlighted in the drop down list (which also feature the hidden id, but, how can I successfully update?  Bear in mind, I am going to make my table modifications, but I will face this again until I can figure out how to successfully accomplish a multiple update throuugh a drop down.

Please advise.

Thanks.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
thack111Commented:
If you got with a extra table to use as a cross referance try these changes:

<---BEGIN QUERIES--->
<cfquery name ="showPartsDetails" datasource="#request.dsn#">
      SELECT collectionID, categoryID, partsID
      FROM museum_parts
      WHERE partsID = #partsID#
</cfquery>

<cfquery name = 'getcollections' datasource=#request.dsn#>
      SELECT collectionID
      FROM   partscrossreferenc
      where  partID = #partID#
</cfquery>
<cfloop query='getcollections'>
      <cfset partsList = ListAppend(partsList, #collectionID#)>
</cfloop>

<cfquery name="selectCollections" datasource="#request.dsn#">
      select collectionID, collection_type
      from museum_collections
</cfquery>

<---BEGIN FORM--->
<form action='update.cfm' method='post'>
Collection:              
      <select name="collectionIDs" multiple size="5">
      <cfoutput query = 'selectCollections'>
            <option value="#collectionID#"<cfif ListContains(partsList, #collectionID#) neq '0'> selected</cfif>>#collectionID#: #collection_type#</option>
      </cfoutput>
      </select>
<input type="submit" name="SubmitButton" value="Make the changes">  
<cfoutput>
<input type="Hidden" name="partsID" Value="#showPartsDetails.partsID#">
<input type="Hidden" name="partsList" Value="#partsList#">
</cfoutput>
</form>
<---END FORM--->
=================================

<cfloop list="#form.collectionID#" index="FormcollectionID">
<cfif ListContains(partsList, #FormcollectionID#) eq '0'>  
      <cfquery name="updateParts" datasource="#request.dsn#">
            INSERT INTO partscrossreferenc(partID, collectionID, posting_date)
            VALUES(#partID#, #FormcollectionID#, getdate())
                  UPDATE museum_parts
      </cfquery>
      <cfquery name="updateParts" datasource="#request.dsn#">
            DELETE FROM partscrossreferenc
            WHERE partID = #partID# AND
                  collectionID NOT IN(#form.collectionID#)
      </cfquery>
</cfloop>

Cheers
0
 
synergeticsoulAuthor Commented:
Thank you THACK111!

I am going to try modifying the tables first and then working on the query logic.
0
 
synergeticsoulAuthor Commented:
In the original form listing, should I be passing the PartsID form variable?  Or is it best to pass based on the item, which is CategoryID? Otherwise, I am still getting an error, when trying to view the categoryID with records 1 and 4 (both having separate collections)???

Thanks for your patience.
0
 
synergeticsoulAuthor Commented:
NOTHING WORKS and I am ready to scream because I don't know where I am going wrong.  I have tried everything and get errors.  I have revised my tables, queries, tried subqueries, everything.  

I want to show ALL collections for a particular record.  Ie, Record AAA may belong to 2 collections.  RECORD AAA has parts in the corresponding parts table and now the parts table has values in the reference table.  

I want to be able to show and update RECORD AAA  (or any other record).  I have increased the points.  

Please advise.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now