Multiple Table Updates

rita1
rita1 used Ask the Experts™
on
How do you do mutliple table updates?

Here is what I have..


here is what I have in my updateform:

<cfquery name="UpdateDeptInfo" datasource="DepartmentInfo">
SELECT      *
FROM      DeptInfo, Users
WHERE     Users.UserID = #Session.UserID#
AND Users.DeptID=DeptInfo.ID
</cfquery>

<cfquery name="UpdateDeptContact" datasource="DepartmentInfo">
SELECT      *
FROM      ContactType, DeptContact
WHERE     DeptContact.DeptID=#UpdateDeptInfo.DeptID#
AND          ContactType.ID=DeptContact.ContactTypeID

</cfquery>

<cfform action="updatepage.cfm" method="POST">
<cfoutput query="UpdateDeptInfo">
<input type="Hidden" name="ID" value="#ID#">
..............................
</cfoutput>

<cfoutput query="UpdateDeptContact">
<input type="Hidden" name="ID" value="#ID#">
..............................
</cfoutput>

<input type="Submit" value="Update Information">
</cfform>

Here is what I have in my updatepage:

<cfquery name="upd_DeptInfo" datasource="DepartmentInfo">

UPDATE DeptInfo
SET
Address= '#Form.Address#',
City='#Form.City#',
Province='#Form.Province#',
PostalCode='#Form.PostalCode#',
ExecSummary='#Form.ExecSummary#'
WHERE ID=#Form.ID#;
</cfquery>
<cfquery name="upd_DeptContact" datasource="DepartmentInfo">
UPDATE DeptContact
SET
LastName= '#Form.LastName#',
FirstName='#Form.FirstName#'
WHERE ID=#Form.ID#;
</cfquery>

It is still givin me the following error...
ODBC Error Code = 37000 (Syntax error or access violation)
[Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access violation: near 'WHERE' in ...[WHERE] ID=1;
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (3:1) to (3:55).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You have to put a seperate CFQUERY tag for each table update, like this:

<cfquery name="upd_DeptInfo" datasource="DepartmentInfo">
UPDATE DeptInfo
SET
Address= '#Form.Address#',
City='#Form.City#',
Province='#Form.Province#',
PostalCode='#Form.PostalCode#',
ExecSummary='#Form.ExecSummary#'
WHERE ID=#Form.ID#;
</cfquery>

<cfquery name="upd_DeptContact" datasource="DepartmentInfo">
UPDATE DeptContact
SET
LastName= '#Form.LastName#',
FirstName='#Form.FirstName#',
WHERE ID=#Form.ID#;
</cfquery>
Plus, you have an extra comma after the FirstName column:

FirstName='#Form.FirstName#',

Author

Commented:
IT still gives me the same error
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Output the SQL statements like this:

<cfoutput>
UPDATE DeptInfo
SET
Address= '#Form.Address#',
City='#Form.City#',
Province='#Form.Province#',
PostalCode='#Form.PostalCode#',
ExecSummary='#Form.ExecSummary#'
WHERE ID=#Form.ID#;
</cfoutput>

Paste the results here for the query both queries, and I'll take a look at them.

Author

Commented:
The seconde table DeptContact could have many contact for each dept....therefore, when I perform the update, it is putting all the contact names in one record

ex: Main Contact: Joe Doe.
Tech Contact John Blee

After performing the update it gives me:

Main Contact: Joe,Joh, Doe, Blee
Tech Contact:  Joe,Joh, Doe, Blee
So the update is executing without error though?

I think you're problem may be related to your fields. You have multiple fields on the previous page that have the name ID,FirstName, and LastName?

Author

Commented:
Yes, how do fix the problem:

Yes is the updateform of the query having problem with:

<cfquery name="UpdateDeptContact" datasource="DepartmentInfo">
SELECT      *
FROM      ContactType, DeptContact
WHERE     DeptContact.DeptID=#UpdateDeptInfo.DeptID#
AND     ContactType.ID=DeptContact.ContactTypeID

</cfquery>

<cfoutput query="UpdateDeptContact">
<input type="Hidden" name="DeptID" value="#DeptID#">
<input type="Hidden" name="ContactTypeID" value="#ContactTypeID#">
<br><table width="450">
<tr>
<th id="header1"></th>
<th id="header2"></th>
<th id="header3"></th>
</tr>
<tr>
<td headers="header1" width="180">#UpdateDeptContact.ContactType# Contact </td>
<td headers="header2"><input type="Text" name="FirstName" value="#FirstName#"></td>
<td headers="header3"><input type="Text" name="LastName" value="#LastName#"></td>
</tr>
</table>
</cfoutput>

---------------------------
and this is the updatepage:

UPDATE DeptContact
SET
LastName= '#Form.LastName#',
FirstName='#Form.FirstName#'
WHERE DeptID=#Form.ID# and ContactTypeID=#Form.ContactTypeID#
You should NEVER have 2 inputs within a form that have the same name. JavaScript will die if you attmept to validate these values, plus it will make for a rather hard time updating them.

What you will need to do is rename your inputs using a sequence number like so:

<cfoutput query="UpdateDeptContact">
<input type="Hidden" name="DeptID" value="#DeptID#">
<input type="Hidden" name="ContactTypeID" value="#ContactTypeID#">
<br><table width="450">
<tr>
<th id="header1"></th>
<th id="header2"></th>
<th id="header3"></th>
</tr>
<tr>
<td headers="header1" width="180">#UpdateDeptContact.ContactType# Contact </td>
<td headers="header2"><input type="Text" name="FirstName_#CurrentRow#" value="#FirstName#"></td>
<td headers="header3"><input type="Text" name="LastName_#CurrentRow#" value="#LastName#"></td>
</tr>
</table>
</cfoutput>

*********************************************************

Now, on the processing page, you'll need to do the following:

<cfset contactCount = 1>
<cfloop condition="IsDefined("form.LastName_#contactCount#")">

UPDATE DeptContact
SET
LastName= '#Evaluate("Form.LastName_#contactCount#")#',
FirstName='#Evaluate("Form.FirstName_#contactCount#")#',
WHERE ID=#Form.ID#;

<cfset contactCount = contactCount + 1>
</cfloop>

Commented:
still i unable to understand the what is the reqirement. pls. explain briefly.
Well first we'll need to know one more piece of information about the DeptContact table. There has to be an ID in that table that is going to allow us to uniquely identify each of the contacts for a specific department ID. What is the name of this field?

Also, how are these contacts getting into the system to begin with? Are there always like 3 or something? Does this page need to be able to handle inserts as well as updates? Or just updates?

Author

Commented:
The DeptContact table contains the following:

DeptContact: ID, DeptId, ContactId, LastN, FirstN.....
ContactType: ID, ContactType
DeptInfo:ID, DeptName......

So I need to update the DeptInfo and DeptContact tables for the user that logs in.  

At this time, we are asuming that the data is already in the DB.  I have not created the insert pages yet for new Dept.  
I tried your code but it is given me an error:

Just in time compilation error

Invalid token found on line 17 at position 37. ColdFusion was looking at the following text:

.
Invalid expression element. The usual cause of this error is a misspelling in the expression text.
The last successfully parsed CFML construct was a CFLOOP tag occupying document position (17:1) to (17:7).
=================

and this is the code you gave me:

<cfquery name="upd_DeptContact" datasource="DepartmentInfo">
<cfset contactCount = 1>
<cfloop condition = "IsDefined("Form.LastName_#contactCount#")">
UPDATE DeptContact
SET
LastName= '#Evaluate("Form.LastName_#contactCount#")#',
FirstName='#Evaluate("Form.FirstName_#contactCount#")#',
WHERE DeptID=#Form.DeptID#;

<cfset contactCount=contactCount + 1>
</cfloop>
</cfquery>
OK, so what you want to do is like this:

When you loop through on the page that lets you edit the FIrst and Last name of the contacts, you need to add the contactID to the fieldName so that you can differentiate the names later, like this:

<input type="Text" name="FirstName_#contactID#" value="#FirstName#">
<input type="Text" name="LastName_#contactID#" value="#LastName#">

So what you'll end up with is a bunch of inputs with similar names as the following:

FirstName_1
LastName_1
FirstName_2
LastName_2
FirstName_3
...

Where the number on the end of the field is the contactID. This way, we know exactly which first/last name pair belongs to each contact ID.

***********************************************************

OK, so then we get to the processing page, and you've got a bunch of fields sent through the form with names like we have above. We won't know the exact names of the fields since they are dynamic based on the department that they are editing. So we'll need to select the contactID from teh DB associated with the department they're editing, like this:

<cfquery name="qContactID">
  SELECT DISTINCT contactID
  FROM DeptContact
  WHERE DeptId = #form.DeptId#
</cfquery>

Now this gives us a list of the contactIDs that we strapped onto the end of the First and Last name fields, we'll loop through these ID's as follows to get those IDs:

<cfloop query="qContactID">
  <cfif IsDefined("form.FirstName_#contactID#")>
    <cfquery name="updateContact">
      UPDATE DeptContact
      SET
      LastName='#Evaluate("Form.LastName_#contactCount#")#',
      FirstName='#Evaluate("Form.FirstName_#contactCount#")#'
      WHERE DeptId = #form.DeptId#
      AND contactID = #contactID#
    </cfquery>
  </cfif>
</cfloop>

**************************************************

Give this a try. Let m eknow if you understand the concept of putting the contactID on the end of thefield name to help uniquely identify the field, and if you understand the loop process to get these field names dynamically on the processing page?

If you get an error, please let me know not only what line it's on, but what's on that line cause I don't know what line 17 is  =)

Author

Commented:
It works!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial