Using @@rowcount after update in ASP

Discussion in 'ASP General' started by FLORAL, Feb 5, 2004.

  1. FLORAL

    FLORAL Guest

    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.
     
    FLORAL, Feb 5, 2004
    #1
    1. Advertising

  2. FLORAL

    Bob Barrows Guest

    FLORAL wrote:
    > 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

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows, Feb 5, 2004
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. robert madrian

    ODS + TableAdapter + Rowcount

    robert madrian, Dec 19, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    398
    robert madrian
    Dec 19, 2005
  2. Replies:
    1
    Views:
    295
    =?ISO-8859-1?Q?Gerhard_H=E4ring?=
    Nov 14, 2005
  3. Jon Haakon Ariansen

    SqlDataReader - ReturnValue - rowcount

    Jon Haakon Ariansen, Nov 8, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    1,941
    bruce barker \(sqlwork.com\)
    Nov 8, 2006
  4. segue

    getting rowcount

    segue, Jan 23, 2008, in forum: ASP .Net
    Replies:
    4
    Views:
    771
    segue
    Jan 23, 2008
  5. Dev
    Replies:
    3
    Views:
    914
Loading...

Share This Page