troubleshooting Question

CFFORM/CFLOOP/UPDATE SQL Table

Avatar of Lee R Liddick Jr
Lee R Liddick JrFlag for United States of America asked on
Web Development Software
12 Comments2 Solutions852 ViewsLast Modified:
I have a loop query in a form that upon submit by the end user, it updates a SQL table.  The form basically has two fields...the ID field of the record that needs updated and a name field that gets replaced.  Here is the code of the form first and then the action page of what I have so far.  Thanks for the help!

<CFFORM action="../include/act_caseupdate.cfm?Act=Rsn" method="POST">
      <CFLOOP INDEX="GroupList"
      LIST="#qryGroups.strCaseGrpID#"                         
      DELIMITERS=",">
      <!-- Get all new cases assigned to all the groups the group user is assigned to -->
            
      <cfinclude template="../queries/qry_MyGroupsCasesQuery.cfm">

      <CFOUTPUT query="getAllMyGroupsCases" group="strCaseGrpName">
      <TR bgcolor="CDCFD0">
            <TD colspan="9"><font size="-2">&nbsp;&nbsp;&nbsp;Group: <b>#strCaseGrpName#</b></font></TD>
      </TR>
      <CFOUTPUT>            
      <tr class="itxtiny" bgcolor="F5F5F5" title="#strCaseDetail#">
            <td align="center"><font size="-2">#intCaseID#</font></td>
          <td align="center"><font size="-2">#ReqFullName#</font></td>            
          <td align="center"><font size="-2">#strCaseType#</font></td>
          <td align="center"><font size="-2">#strCaseProd#</font></td>
          <td align="center"><font size="-2">#strCaseCat#</font></td>
          <td align="center"><font size="-2">#strCaseCatItem#</font></td>            
          <td align="center"><font size="-2">#AsgFullName#</font></td>
          <td align="center" class="tiny"><font size="-2">
                  <CFSELECT name="selGroupMember"
                        query="getGroupMembers"
                        value="strGrpMbrID"
                        display="strGrpMbrFullName"
                        selected="#strGrpMbrID#"
                        width="150">
                  </CFSELECT></font>        
            </td>                  
          <td align="center"><font size="-2" color="C0C0C0">
                  <cfinput type="Checkbox" name="#intCaseID#">
                        &nbsp;Reassign</font>
            </td>
            </CFOUTPUT>
            </CFOUTPUT>
            </CFLOOP>
      </tr>
</table>
</CFFORM>

The action/update code:

<cfif Act EQ 'Rsn'>
      <cfloop list="#form.fieldnames#" index="i" delimiters=",">
      <cfif #isnumeric(i)#>
      <cfquery datasource="mydatasource" name="getGrpMbrID">      
            SELECT GAS.intCaseGrpMbrID
            FROM tbl_casegroupassignments GAS
            WHERE GAS.strGrpMbrID = '#form.selGroupMember#'
      </cfquery>
      <cfoutput><cfset GrpMbrID="#getGrpMbrID.intCaseGrpMbrID#"></cfoutput>
      <!-- BEGIN dbase Updates -->      
      <cfquery datasource="mydatasource" name="updatestatus">
            INSERT into tbl_casestatus
                  (intCaseID, intCaseStatID, intCaseActTypeID, strActivityDetail, dtiModified, strModifiedBy)
            VALUES
                  (#i#, '3', '4', 'Case owner has been Reassigned', '#DateFormat(Now(), 'mm/dd/yy')# #TimeFormat(Now(), 'hh:mm tt')#', '#session.mydata.CUID#')      
      </cfquery>
      <cfquery datasource="mydatasource" name="reassigncase">
          UPDATE tbl_casedata
            SET  intCaseGrpMbrID = #GrpMbrID#
            WHERE intcaseID = #i#
      </cfquery>
      <!-- END dbase Updates -->
      <!-- Send email notifying of case reassignment -->
      <cfinclude template="../email/snd_casereassign.cfm">
      </cfif>
      </cfloop>
</cfif>

Currently...the error I get upon submit is:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'WHERE'.  
 
The error occurred in act_caseupdate.cfm: line 78
 
76 :           UPDATE tbl_casedata
77 :             SET  intCaseGrpMbrID = #GrpMbrID#
78 :             WHERE intcaseID = #i#
79 :       </cfquery>
80 :       <!-- END dbase Updates -->
 --------------------------------------------------------------------------------
 SQL    UPDATE tbl_casedata SET intCaseGrpMbrID = WHERE intcaseID = 46  

So somehow it's not capturing the new Group Member ID (GrpMbrID)...and I'm a little lost after looking at this for a few hours.  Any assistance or guidance you could provide would be greatly appreciated.
 
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros