Forum Discussion

Colin_McCrae's avatar
Community Hero
9 years ago

ADODB connection ..... Rows Affected count?

Been playing around with this today and am struggling to get back the info I want.


I'm using C# Script (so JScript rules probably apply).


Up to now, I've been using a connection object, and a recordset object and running SQL with is all stuff that returns recordsets. All fine. No problems.


I now want to add a function which will execute non-recordset returning SQL.


This is using the "Execute" method of the connection object.


It runs fine. The SQL is executed and the DB (it's doing an update) is altered correctly. I can manually check all that in SQL Management Studio.


However, I can't find any way to get the number of rows affected by the Execute method? It can supposedly be applied as a parameter within the Execute which should be populated when the Execute runs. But it doesn't.


We're starting to think this is a limitation of the ADODB connection and C# Script/Jscript inability to accept passed in non-object by ref?


Any ideas?


I catch it if the connection fails.


I catch it is the SQL is invalid and generates an error.


But I want to report out if valid sequel is run and some/none rows are affected.


Any ideas?


(I'm considering shipping the execute out to a script extension which I can write in VB Script which doesn't have the same by ref limitations but this seems like a long way for a short cut ....)

5 Replies

    • Colin_McCrae's avatar
      Community Hero

      Hi Helen


      Thanks for that. That extension is pretty much what I was suggesting I would write. So saved me the trouble. :)


      However, I had all sorts of problems getting an array into it (which contains the parameters) from C# Script. To get it in the right format (as far as I can tell) I would have had to use a scripting object to generate a safe array/list, then convert that to a VBArray.


      Which seemed like a lot of extra hassle.


      So I modified the extension script so that it took in a delimited string (using "|" as the separator as this is seldom used in SQL). Then simply used a "Split" inside the script extension to create the VB type array within the extension, rather than having to pass it in. Much easier to create a delimited string in C# Script (and thus Jscript) than a VB type array!


      Works great now. Thanks.


      Here is the modified extension script code:


      Function CallObjectMethod(obj, method, paramIN)
        Dim params, paramStr, i, result
        params = Split(paramIN, "|")
        paramStr = ""
        For i = 0 To UBound(params)
          paramStr = paramStr & "params(" & i & ")"
          If i < UBound(params) Then paramStr = paramStr & ", "
        Execute("Call obj." & method & "(" & paramStr & ")")
        CallObjectMethod = params
      End Function
      • HKosova's avatar
        SmartBear Alumni (Retired)

        Great! Thanks for sharing your solution.