Forum Discussion

Colin_McCrae's avatar
Colin_McCrae
Community Hero
10 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 ....)

    • Colin_McCrae's avatar
      Colin_McCrae
      Community Hero
      10 years ago

      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 & ", "
  Next

  Execute("Call obj." & method & "(" & paramStr & ")")
  CallObjectMethod = params
End Function
      • HKosova's avatar
        HKosova
        SmartBear Alumni (Retired)
        10 years ago

        Great! Thanks for sharing your solution.