Help needed with Cold Fusion code

[jtracking]

Can someone tell me what the heck I am doing wrong with this code.

Thee is no reason for it to work! or is there?

<CFSET email = "">
<CFSET first_name = "">
<CFSET RecordCount = 0>

<cfquery name="RemoveRecs" datasource="#request.dsn#">
		DELETE FROM contacts WHERE email = 'wendyb@mydomain.com' and first_name <> 'Wendy'; 

	</cfquery>

<cfif RemoveRecs.RecordCount NEQ 0>
<table border="1" cellpadding="7px">  
  <tr>     
    <th>Email Address</th>   
    <th>First Name</th>   
  </tr>  
  <cfoutput query="remove_recs">
    <tr>       
      <td>#RemoveRecs.email#</td>
      <td>#RemoveRecs.first_name#</td>
    </tr>
  </cfoutput>
</table>
<cfelse>
No Records to delete!
</cfif>

DONE!

Any assistance will be appreciated. I have searched the net extensively for help with this.

[Shift4SMS]

Can someone tell me what the heck I am doing wrong with this code.

Thee is no reason for it to work! or is there?

...

Any assistance will be appreciated. I have searched the net extensively for help with this.

I'm not sure exactly what you are trying to do but I see two issues right off the bat:

1) I'm not sure what flavor of database you are talking to but the semi-colon on the end of the delete statement will most likely throw an error.

2) A SQL delete statement does not return a result set so RemoveRecs.RecordCount will always be zero and RemoveRecs.email & RemoveRecs.first_name will never exist.

If you are using MSSQL, you can do the following to get the number of rows deleted:

<cfquery name="RemoveRecs" datasource="#request.dsn#"> 
DELETE FROM contacts WHERE email = 'wendyb@mydomain.com' and first_name <> 'Wendy'
select @@RowCount as [RowCount]
   </cfquery>

The you can use RemoveRecs.RowCount as the number of rows deleted. You will need to do a separate select to get your email and first_name values though.

[Shift4SMS]

In rereading your code, I think what you want is this:

<cftransaction>
  <cfquery name="RemoveRecs" datasource="#request.dsn#"> 
    SELECT email, first_name FROM contacts WHERE email = 'wendyb@mydomain.com' and first_name <> 'Wendy' 
  </cfquery> 

  <cfquery "RemoveRecsCount" datasource="#request.dsn#"> 
    DELETE FROM contacts WHERE email = 'wendyb@mydomain.com' and first_name <> 'Wendy' 
    select @@RowCount as [RowCount] 
  </cfquery> 
</cftransaction>

Now RemoveRecs contains the the result set of the records that were deleted and RemoveRecsCount.RowCount contains the actual number of rows deleted -- which RemoveRecsCount.RowCount "SHOULD" = RemoveRecs.RecordCount

[jtracking]

Well thank you, I will try that and get back to you. Thank you very much !

[jtracking]

Ok so, will that display the records that have been deleted? I don't want to run the script until I'm half sure.

[Shift4SMS]

Yes, the first cfquery returns a result set with all the records that will match your deletion criteria. The second cfquery actually does the deletion. The cftransaction binds the two queries together so the results of query 1 should match the records deleted in query 2.

BTW, this really should have been a separate thread... ;)

[jawn_tech]

BTW, this really should have been a separate thread... ;)

Affirmative. It is now a separate thread.

:)

[jtracking]

In rereading your code, I think what you want is this:

<cftransaction>
  <cfquery name="RemoveRecs" datasource="#request.dsn#"> 
    SELECT email, first_name FROM contacts WHERE email = 'wendyb@mydomain.com' and first_name <> 'Wendy' 
  </cfquery> 

  <cfquery "RemoveRecsCount" datasource="#request.dsn#"> 
    DELETE FROM contacts WHERE email = 'wendyb@mydomain.com' and first_name <> 'Wendy' 
    select @@RowCount as [RowCount] 
  </cfquery> 
</cftransaction>

Now RemoveRecs contains the the result set of the records that were deleted and RemoveRecsCount.RowCount contains the actual number of rows deleted -- which RemoveRecsCount.RowCount "SHOULD" = RemoveRecs.RecordCount

ok so does '@@RowCount' actually mean RemoveRecsCount.RowCount?

[Shift4SMS]

@@RowCount is a SQL server variable that contains the number of rows affected by the last SQL statement. In this query, it follows the delete statement so it should reflect the number of rows deleted and it is included in a select statement to return a result set to CF.

I think the short answer to your question is yes, @@RowCount is RemoveRecsCount.RowCount.