Updating Multiple Records in a duplicate table

[NickyJay]

Hi All,

Im posting again as a i have ANOTHER problem!

Scenario:
database holds leaflets - id, title, main text, date updated, published. A duplicate table also exists to copy the leaflets that are marked as published or finished. I have a page that pulls out all leaflets in a query that are marked as published (Y/N) = Y.

From this query, the leaflets are displayed back to the user in a form (dynamically created to output a row for each record retrieved), and the user can choose to remove any of the records from the query (this bit i have working fine).

The problem i have is where the user is happy with the records, and wants to go ahead and copy them to the other table - this table is bascially a store for only finished leaflets.

I attempted to use some code i found from the intenet that seemed to do what i wanted, however i have an error that i dont have a clue how to fix! Please can someone give me a clue on what i should be looking at!

my code is:
all_published.cfm (query results for those that are published leaflets)

<cfquery name="qryDateResults" datasource="NALDclips">
	SELECT LeafletID, SNIPIDFk, LeafletCode, DocumentName, LeafletTitle, Introduction, EnglishBody, ForFurtherInfo, DateCreated, CreatedBy, DateUpdated, UpdatedBy, RevisionDate, Publish, PublishDate
	FROM tblLeafletCopy 
	WHERE Publish = 'Y' 
	ORDER BY LeafletCode 
</cfquery>

<form method="POST" name="MultiForm" action="./thanks.cfm">
          <!--- Pass the number of records. --->
          <cfoutput> 
            <input type="hidden" name="NumRecords" value="#qryDateResults.RecordCount#">
          </cfoutput> 
          <table class="table-text">
            <tr> 
              <td bgcolor="#c0c0c0"><b>Code</b></td>
              <td bgcolor="#c0c0c0"><b>Title</b></td>
              <td bgcolor="#c0c0c0"><b>Introduction</b></td>
              <td bgcolor="#c0c0c0"><b>Body</b></td>
              <td bgcolor="#c0c0c0"><b>Further</b></td>
              <td bgcolor="#c0c0c0"><b>Updated By</b></td>
            </tr>
            <!--- Loop through the query and output the form fields. 
		Each form field will end in the row number of the record. --->
            <cfoutput query="qryDateResults"> 
              <input type="hidden" name="LeafletID#CurrentRow#" value="#LeafletID#">
              <tr>	
                <input type="hidden" name="SNIPIDFk#CurrentRow#" value="#SNIPIDFk#"size="8" />
                <td><input type="text" name="LeafletCode#CurrentRow#" value="#LeafletCode#" size="8" /></td>
                <input type="hidden" name="DocumentName#CurrentRow#" value="#DocumentName#" size="10" />
                <td><input type="text" name="LeafletTitle#CurrentRow#" value="#LeafletTitle#" size="20" /></td>
                <td><input type="text" name="Introduction#CurrentRow#" value="#Introduction#" size="20" /></td>
                <td><input type="text" name="EnglishBody#CurrentRow#" value="#EnglishBody#" size="20" /></td>
                <td><input type="text" name="ForFurtherInfo#CurrentRow#" value="#ForFurtherInfo#" size="20" /></td>
                <input type="hidden" name="DateUpdated#CurrentRow#" value="#DateUpdated#" size="10" />
                <td><input type="text" name="UpdatedBy#CurrentRow#" value="#UpdatedBy#" size="12" /></td>
                <input type="hidden" name="RevisionDate#CurrentRow#" value="#RevisionDate#" size="15" />
                <input type="hidden" name="Publish#CurrentRow#" value="#Publish#" size="2" />
                <input type="hidden" name="PublishDate#CurrentRow#" value="#PublishDate#" size="10" />
              </tr>
            </cfoutput> 
          </table>
          <p> 
            <input type="submit" name="action" value="copy to E-Clips" class="button" />
            &nbsp; 
            <input type="submit" name="action" value="reset all leaflets" class="button" />
          </p>
          <input type="hidden" name="MM_UpdateRecord" value="MultiForm">
        </form>

the next page, thanks.cfm is where the update should happen

<cfif IsDefined("FORM.action") AND #FORM.action# EQ "copy to E-Clips">
<!--- Loop through the form fields and update the table. --->
<cfloop from="1" to="#NumRecords#" index="ThisRow">
  <cfquery datasource="NALDclips">
	  UPDATE tblPublished
	  SET 
		  SNIPIDFk=#Evaluate("SNIPIDFk" & ThisRow)#,
		  LeafletCode='#Evaluate("LeafletCode" & ThisRow)#',
		  DocumentName='#Evaluate("DocumentName" & ThisRow)#',
		  LeafletTitle='#Evaluate("LeafletTitle" & ThisRow)#',
		  Introduction='#Evaluate("Introduction" & ThisRow)#',
		  EnglishBody='#Evaluate("EnglishBody" & ThisRow)#',
		  ForFurtherInfo='#Evaluate("ForFurtherInfo" & ThisRow)#,
		  DateUpdated='#Evaluate("DateUpdated" & ThisRow)#',
		  UpdatedBy='#Evaluate("UpdatedBy" & ThisRow)#',
		  RevisionDate='#Evaluate("RevisionDate" & ThisRow)#',
		  Publish='#Evaluate("Publish" & ThisRow)#',
		  PublishDate='#Evaluate("PublishDate" & ThisRow)#',
	  WHERE LeafletID=#Evaluate("LeafletID" & ThisRow)#
  </cfquery>
</cfloop>
	<cflocation url="./kcindex.cfm" addtoken="no" />
</cfif>

When i click on the button from all_published.cfm, i get the error:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

The error occurred in C:\InetPub\

http://WWWRoot\Nicola\keyclips\thanks.cfm:

line 33

31 : Publish='#Evaluate("Publish" & ThisRow)#',
32 : PublishDate='#Evaluate("PublishDate" & ThisRow)#',
33 : WHERE LeafletID=#Evaluate("LeafletID" & ThisRow)#
34 : </cfquery>
35 : </cfloop>

Please can someone shed some light on this for me! I'm on a deadline as i have to finish the rest of the site before i go on maternity leave - 2weeks time!

Thanks

Nicola

 

[peterswan]

Your code looks pretty good, it looks like a SQL syntax error. Make sure all of the columns in your table are named the same thing that you are referencing in your UPDATE statement.

The other thing it could be is your date fields, which can always be very tricky on how it's inserted or updated. Try using CreateODBCDateTime for the values you are updating. I'm not sure if this will need single quotes or not. Also, try using DateFormat() with the format you want. Also, do a View Source and check the format of the Date that populates the hidden fields on your form page (DateUpdated, RevisionDate, etc.). This format may need to be changed using DateFormat.

Also, the best way to know if it's the date fields, is to comment out the part of the SQL statement that updates the date fields. Just use two dashes next to each date field line in the SQL, which is SQL commenting in SQL Server. If everything works fine, then you'll know for sure it's the dates.

Peter

 

[NickyJay]

Hi,

thanks for posting!

I redid my page and tried the update a line at a time and managed to get it working - still not sure what this error was for but the line i took ou was

<input type="hidden" name="MM_UpdateRecord" value="MultiForm">

...not sure if this caused some conflict or other!

Nicola