Record Count with Update query 1

[DugsDMan]

Is there an easy way to get the number of rows updated by a query?

I'm trying to update a password table, and should only get one, but would think this would be useful in other areas as well.

I'm working on SQL Server and CF 5 (patiently waiting to go to MX)

Thanks!

 

[ecobb]

In SQL Server (2000), you can use @@RowCount to return the number of rows affected by the last statement. This variable is set to 0 by any statement that does not return rows.

Not sure how this will help if you're using a regular update statement, but if you're using a Stored Procedure you do something like:

UPDATE Table
SET name = 'John Doe'
WHERE id = 1

Set @Records = @@RowCount (Make sure you have @Records declared in your procedure)

And then just return the variable @Records to CF from your procedure.


Hope This Helps!

Ecobb

"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[DugsDMan]

Thanks, I'm going to try to make it a Stored Proc and see how it goes. I'll let you know.

Thanks!

 

[DugsDMan]

Ok, I now have a stored proc that I can run from CF,, using the CFSTOREDPROC tag. How do I get the proc to return the @RowsAffected value? I set the value in the proc, but can't seem to return it.

Here's my code:

	<CFSTOREDPROC DATASOURCE=&quot;#SQLDSN#&quot; USERNAME=&quot;#SQLUSER#&quot; PASSWORD=&quot;#SQLPSWD#&quot; PROCEDURE=&quot;BARS_Update_PW&quot;>
		<CFPROCPARAM Type=&quot;IN&quot; DBVARNAME=@UserID VALUE=&quot;#CLIENT.BARSUID#&quot; cfsqltype=&quot;cf_sql_varchar&quot;>
		<CFPROCPARAM Type=&quot;IN&quot; DBVARNAME=@CurrentPassword VALUE=&quot;#FORM.CurrentPassword#&quot; cfsqltype=&quot;cf_sql_varchar&quot;>
		<CFPROCPARAM Type=&quot;IN&quot; DBVARNAME=@NewPassword VALUE=&quot;#FORM.NewPassword#&quot; cfsqltype=&quot;cf_sql_varchar&quot;>
		<CFPROCPARAM TYPE=&quot;OUT&quot; DBVARNAME=@RecordsAffected cfsqltype=&quot;cf_sql_integer&quot; VARIABLE=#RecordsAffected#>
	</CFSTOREDPROC>

When I don't use the "out" param, it runs fine, but I don't get anything back. When I do use it, I get this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function BARS_Update_PW has too many arguments specified.

Any ideas?

 

[ecobb]

Try this:

<CFPROCPARAM TYPE=&quot;OUT&quot; DBVARNAME=@RecordsAffected cfsqltype=&quot;cf_sql_integer&quot; VARIABLE=&quot;RecordsAffected&quot;>

You don't need the pound signs around &quot;RecordsAffected&quot; in your procparam tag. This is what you are naming the variable that you want to output later. So you can call it buy using <cfoutput>#RecordsAffected#</cfoutput>.



Hope This Helps!

Ecobb

&quot;Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer.&quot; - Homer Simpson

 

[DugsDMan]

Awesome, that did it!!

Thanks!

(And for you a big star!)