Using @@rowcount after update in ASP

[FLORAL]

One Update statement in my following function
UpdateSuccess_Order goes like this:

Function UpdateSuccess_Order(ByVal Ord_No, ByVal
Ord_NoSuccess, ByRef strError)
UpdateSuccess_PAL = "ERROR"
Dim SQL, ITZ_RESULT, zRS
Dim Tmp, cnt
cnt=-999

SQL = "UPDATE tabOrders SET SUCCESS = " & Ord_NoSuccess
SQL = SQL & " WHERE SUCCESS <> " & Ord_NoSuccess &
SQL = SQL & " and ORDERid = " & Ord_No & ""
SQL = SQL & " select @@rowcount"

ITZ_RESULT = RunSQLQuery(SQL, zRS)

' RunSQLQuery is a function written my me, its purpose is
obvious , takes sql
'statement and recordset, returns RESULTS if it is success
'in case of error ITZ_DataBaseErrorString gets populated
with error

If ITZ_RESULT <> "RESULTS" Then
strError = ITZ_DataBaseErrorString
Exit Function
End If

' NOW CHECK IF UPDATE HAS ACTUALLY HAPPENED

SQL = ""
ITZ_RESULT = ""
Do While (Not zrs Is Nothing)
If zrs.State = adStateClosed Then Exit Do
Set zrs = zrs.NextRecordset
EXIT DO
LOOP

If zrs.State <> adStateClosed Then cnt = zrs(0)
if cnt >0 then IntimateAccountDept(Ord_No)
..
..
..
..
..
But even if an update actually happens the statement:
If zrs.State = adStateClosed Then Exit Do
get fired
and the value of cnt is alaways -999 as intialized by me.
Plz throw some light as i only want to call
IntimateAccountDept function if update actually happens.

 

[Bob Barrows]

One Update statement in my following function
UpdateSuccess_Order goes like this:

Function UpdateSuccess_Order(ByVal Ord_No, ByVal
Ord_NoSuccess, ByRef strError)
UpdateSuccess_PAL = "ERROR"
Dim SQL, ITZ_RESULT, zRS
Dim Tmp, cnt
cnt=-999

SQL = "UPDATE tabOrders SET SUCCESS = " & Ord_NoSuccess
SQL = SQL & " WHERE SUCCESS <> " & Ord_NoSuccess &
SQL = SQL & " and ORDERid = " & Ord_No & ""
SQL = SQL & " select @@rowcount"

ITZ_RESULT = RunSQLQuery(SQL, zRS)

You should really encapsulate this in a stored procedure, but, for now, make
the following modification:

SQL = "SET NOCOUNT ON;"
SQL = SQL & "UPDATE tabOrders SET SUCCESS = " & Ord_NoSuccess
etc.

This will prevent the "x records affected" messages from being sent to the
calling app in the form of extra recordsets. It will not affect the
@@ROWCOUNT variable which will still work correctly.

HTH,
Bob Barrows