Results 1 to 7 of 7

Thread: RecordsAffected Always Returns 1 After Update

  1. #1

    Thread Starter
    New Member
    Join Date
    Dec 2022
    Posts
    4

    RecordsAffected Always Returns 1 After Update

    Hello,

    I'm trying to update the SQL provider in a VB6 program that uses ADODB, so that we can use TLS 1.2 encryption in the connection. I've tried SQL Native Client 11 and MS OLE DB Driver. However, both of them always return RecordsAffected = 1 after an Update statement, regardless of how many records were actually affected. It works fine with the outdated SQLOLEDB as the provider. I have tried various options in the connection strings, but I haven't found anything that changes this behavior.

    The original working code is as follows:
    Code:
    Public Function init_db_connection(ByVal SQLServerName As String, ByVal Catalog As String) As ADODB.Connection
        Dim cnn As ADODB.Connection
        
        Set cnn = New ADODB.Connection
        
        With cnn
    
            .ConnectionTimeout = 2
            .CommandTimeout = 5
            .Provider = "SQLOLEDB"
            
            .ConnectionString = "Driver={SQL Native Client};Server=" & SQLServerName & ";Database=" & Catalog & ";UID=" & getUsr & ";PWD=" & getPwd & ";"
        End With
        
        Set init_db_connection = cnn
        
    End Function
    I've tried these connection strings, removing/commenting the line .Provider = "SQLOLEDB", with the same results. I've tried with and without the encryption options.
    Code:
    "Provider=SQLNCLI11;DataTypeComptibility=80;Server=" & SQLServerName & ";Database=" & Catalog & ";UID=" & getUsr & ";PWD=" & getPwd & ";Use Encryption for Data=True;Trust Server Certificate=True;"
    Code:
    "Provider=MSOLEDBSQL;DataTypeComptibility=80;Server=" & SQLServerName & ";Database=" & Catalog & ";UID=" & getUsr & ";PWD=" & getPwd & ";Use Encryption for Data=True;Trust Server Certificate=True;"
    The line that's returning the incorrect value is using connection.Execute.
    Code:
    cnn.Execute sql, recsAffected

    How do I get the correct value for RecordsAffected? Is there something I've missed? I haven't found anyone with this same issue. The complaints I've found from web searches involve a -1 return value for Select statements, but that's not the problem here.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,554

    Re: RecordsAffected Always Returns 1 After Update

    https://www.sqlshack.com/working-wit...rver-rowcount/
    have you tried to SELECT @@ROWCOUNT immediately after your Insert/Update/Delete and check that value?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    New Member
    Join Date
    Dec 2022
    Posts
    4

    Re: RecordsAffected Always Returns 1 After Update

    Quote Originally Posted by Zvoni View Post
    https://www.sqlshack.com/working-wit...rver-rowcount/
    have you tried to SELECT @@ROWCOUNT immediately after your Insert/Update/Delete and check that value?
    Thank you for the workaround. That should work and I will consider using it, but I am really hoping to get the correct behavior from ADO's connection.Execute function.

  4. #4
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    4,439

    Re: RecordsAffected Always Returns 1 After Update

    If you execute something like "SET NOCOUNT ON; UPDATE ..." then you won't get anything in records affected so the variable will probably remain unchanged i.e. if it's value was 1 it will remain 1.

    Keep in mind that SET NOCOUNT ON is a connection setting i.e. once set it keeps applying for each subsequent UPDATE or SELECT statement.

    Also note that each multi-statement stored procedure *has* to be prefixed with SET NOCOUNT ON or OLEDB provider gets "stressed" by the steam of "records affected" coming from the stored procedure.

    The connection on which the stored procedure is execute should *not* have SET NOCOUNT ON so that SQLOLEDB can count the records of the returned one or several recordsets from the stored procedure.

    cheers,
    </wqw>

  5. #5

    Thread Starter
    New Member
    Join Date
    Dec 2022
    Posts
    4

    Re: RecordsAffected Always Returns 1 After Update

    Quote Originally Posted by wqweto View Post
    Keep in mind that SET NOCOUNT ON is a connection setting i.e. once set it keeps applying for each subsequent UPDATE or SELECT statement.
    I was hoping this would lead me somewhere, but I haven't had any luck with it. The UPDATE statement is part of a connection transaction that runs multiple statements/queries. However, even after opening a new connection strictly for the UPDATE, I still get the same results. Setting the NOCOUNT value hasn't had any effect either unfortunately.

    Thanks for the answers. I have to backburner this problem to work on other projects currently.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,064

    Re: RecordsAffected Always Returns 1 After Update

    When you run a multiple statement query - either directly or through a stored proc - you get a response for each statement executed. so if you have:
    Code:
    Select fild1, fld2, fld3 from someTable into anotherTable;
    Update anotherTable set fld4 = fld1+ fld3;
    You get two responses back:
    2 row(s) affected
    10 row(s) affected

    This can confuse things ... and what you get on the recieving end in VB6 are two recordsets, each with one record... the text "2 row(s) affected" and "10 row(s) affected" ...

    If you add SET NOCOUNT ON thusly:
    Code:
    SET NOCOUNT ON;
    Select fild1, fld2, fld3 from someTable into anotherTable;
    Update anotherTable set fld4 = fld1+ fld3;
    you will get back nothing - unless you do an actual select:
    Code:
    SET NOCOUNT ON;
    Select fild1, fld2, fld3 from someTable into anotherTable;
    Update anotherTable set fld4 = fld1+ fld3;
    select * from anotherTable;
    If you don't use the nocount, then try looking at the first field of the returned recordset. IT should be a single field, single row with the text in it. "2 row(s) affected" ... to get to the second one, you need to move to the .NextRecordset which will then also be a single field single row with the "10 row(s) affected" in it ...

    The alternative is to set NOCOUNT ON, then at the end manualy select either the rows edited and return them, or get the count and return that... which you'll then need to inspect manually ... .Execute won't be able to return it for you.

    At least this is how I remember it all being.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    New Member
    Join Date
    Dec 2022
    Posts
    4

    Re: RecordsAffected Always Returns 1 After Update

    Hi techgnome,

    Good to know, but that's not exactly what I meant by multiple queries. In this case, I've got a connection that runs multiple queries, but they're all run one at a time.

    Pseudo code
    Code:
    cnn.BeginTrans
    sql = "Insert..."
    cnn.Execute sql, recsAffected
    If <error> Then
     cnn.RollbackTrans
    End If
    sql = "Update..."
    cnn.Execute sql, recsAffected
    .
    .
    cnn.CommitTrans
    There's an integrity check after each query is run and one of those is checking for the value of recsAffected. The complicated part is that if any of the queries return errors, then ALL of them have to be rolled back. Hopefully I can get what I need from @@ROWCOUNT, but I haven't had a chance to make that change yet.

Posting Permissions

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



Click Here to Expand Forum to Full Width