Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2002
    Location
    London
    Posts
    8

    Angry Unanswered: ODBC ASE Driver v4.1 and RowsAffected / NOCOUNT query.. Help...

    Hi there, just joined the forum so if I should be posting this in another area then please excuse me and do let me know where....

    I have an issue concerning the ASE ODBC DriversV4.1 on a Sybase 11.9.2 / 12 dB.

    When using the drivers through a connection object created in VB I am unable to obtain the RowsAffected on any tables that have Insert/Update triggers working on. I have tried setting the NOCUNT property but have realised that the Update/insert succeeds but the trigger itself does not seem to have fired for those update/inserts.

    Has anybody come across this or know where I can look to resolve this... If anyone would like to look at the trigger then 'm happy to forward it on.

    Any help/Advice would be very appreciated.

    Kind regards,

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Are you looking to return the number of affected rows ? Does sybase support @@ROWCOUNT ?

  3. #3
    Join Date
    Sep 2002
    Location
    London
    Posts
    8

    it does but...

    Originally posted by rnealejr
    Are you looking to return the number of affected rows ? Does sybase support @@ROWCOUNT ?
    Hi, but the ASE V4.1 drivers do not return the rowsaffected which @@ ROWCOUNT would pass on.

    [or have I completely lost the plot... would it be easier for me to send the trigger, It's been working fine but the issue I believe lies in the Drivers, but I'm having trouble finding out why]

    Thanks,
    rocks

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Show me the snippet of code that is connecting and retrieving the results from sybase. What sql statements are you passing - are you using a stored procedure ?

  5. #5
    Join Date
    Sep 2002
    Location
    London
    Posts
    8
    Originally posted by rnealejr
    Show me the snippet of code that is connecting and retrieving the results from sybase. What sql statements are you passing - are you using a stored procedure ?
    The VB code is below [althou i don't think the issue lies in here], a copy of the trigger is also attached.

    Once again for your time in looking at this.

    Public Function ExecuteSQL(strSQL As String, ErrOb As ADODB.Errors, RowsAff As Long) As Boolean

    On Error GoTo ErrHandler

    ADOcon.Execute strSQL, RowsAff

    ExecuteSQL = True

    Exit Function

    ErrHandler:
    Set ErrOb = ADOcon.Errors
    ExecuteSQL = False

    End Function
    Attached Files Attached Files

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    Have you been successful before using this driver and returning row affected (exactly like you are doing now) ? Have you tested creating a database/table without triggers to see if it works ?

    Let me know - I might have a solution.

  7. #7
    Join Date
    Sep 2002
    Location
    London
    Posts
    8
    Originally posted by rnealejr
    Have you been successful before using this driver and returning row affected (exactly like you are doing now) ? Have you tested creating a database/table without triggers to see if it works ?

    Let me know - I might have a solution.
    Thanks for your reply, I've not been successful at at with getting back the RowsAff with this driver connecting to tables with triggers.
    Tables without triggers are fine, they return the Rowsaff as expected.

    What have you in mind..?

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Add select @@rowcount at the end of your query. In your code, create a recordset object. When you run this query, 2 recordsets will be returned - the 2nd being the value you are looking for. Move to the 2nd recordset and use the rs(0) to return the value. Let me know how it goes.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    Do the following:

    select * from table;select @@rowcount

    I am not sure about sybase's character but try the semi-colon between the 2 statements.

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    Any luck? Did you try rs.nextrecordset ?

  11. #11
    Join Date
    Oct 2002
    Posts
    5

    Thumbs up rnealejr

    rnealjr... thanks for the info. I ran into the same problem and your solution worked fine. Thanks

  12. #12
    Join Date
    Feb 2002
    Posts
    2,232
    PremierJP,

    Happy to help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •