ADODB connection ..... Rows Affected count?

Question

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 ....)

hkosova · Accepted Answer

Hi Colin,
&nbsp;
See if this helps:
http://community.smartbear.com/t5/Functional-Web-Testing/Using-ROWCOUNT-SQL-query-in-TestComplete/td-p/90325

colin_mccrae · Answer

Hi Helen&nbsp;Thanks for that. That extension is pretty much what I was suggesting I would write. So saved me the trouble. :)&nbsp;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.&nbsp;Which seemed like a lot of extra hassle.&nbsp;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!&nbsp;Works great now. Thanks.&nbsp;Here is the modified extension script code:&nbsp;Function CallObjectMethod(obj, method, paramIN)
  Dim params, paramStr, i, result
  params = Split(paramIN, "|")
  paramStr = ""
  For i = 0 To UBound(params)
    paramStr = paramStr &amp; "params(" &amp; i &amp; ")"
    If i &lt; UBound(params) Then paramStr = paramStr &amp; ", "
  Next

Execute("Call obj." &amp; method &amp; "(" &amp; paramStr &amp; ")")
  CallObjectMethod = params
End Function

colin_mccrae · Answer

Further modified extension code, but with simple error handling now wrapped around the command that makes the execute call.&nbsp;So, any failure will not crash the extension. The code has been modified slightly as I use my own framework for logging and results tracking. I've replaced that with a couple of "pseudo-lines" (if such things exists?) instead.&nbsp;In my case, it's the second out parameter I'm interested in. It will always be zero or greater. So where the pseudo-lines are in the attached, in my case I set "params(1) = -1" (so the calling function knows there was a failure) and a custom log message. However, this is specific to how I'm using it to make specific SQL calls. Same method won't work if using it in other scenarios. A generic error log/return would though.&nbsp;Function CallObjectMethod(obj, method, paramIN)
  Dim params, paramStr, i, result
  params = Split(paramIN, "|")
  paramStr = ""
  For i = 0 To UBound(params)
    paramStr = paramStr &amp; "params(" &amp; i &amp; ")"
    If i &lt; UBound(params) Then paramStr = paramStr &amp; ", "
  Next

On Error Resume Next
  Err.Number = 0

Execute("Call obj." &amp; method &amp; "(" &amp; paramStr &amp; ")")

If Err.Number &lt;&gt; 0 Then
    ' LOG ERROR
    ' OR PASS BACK SOME SORT OF MEANINGFUL RETURN CODE FOR FAILURE
  End If

On Error Goto 0

CallObjectMethod = params

End Function

hkosova · Answer

Great! Thanks&nbsp;for sharing your solution.

colin_mccrae · Answer

Slight drawback discovered.&nbsp;If I give it invalid SQL, it crashes within the extension script and the entire project is halted as a result.&nbsp;This will be driven by user populated data, so I have to be able to handle it if they populate it with bad SQL.&nbsp;*sigh*&nbsp;So looks like I'll need to extend the extension further to include some error handling. I can't have it crashing mid-suite ....

Forum Discussion

ADODB connection ..... Rows Affected count?

5 Replies

Related Content

Is Alertsite affected by the Log4J vulnerability?

Fetch the row count in DDT driver

Is readyapi 3.10.1 affected by log4j security vulnerability issues?

subitem count

Connection reset

Recent Discussions

Dynamic Object

Pulling in Dynamic Links from a web page

Multiple execution of a test plan with different values from an Excel table

Your thoughts about a REST API in TestComplete

Keyword Test - Click - use -1, -1 by default?