How can I get TC to run query against SQL database?

[whuang]

Hi everyone,

I have a test that I need to ask TC to run a query in SQL database to update some data, but I have no knowledge about database at all. I read about ADO Object and tried to do the same as showing in the example, but I was not sure what the value after ConnectionString was ("Provider=Microsoft.Jet.OLEDB.4.0;" + _"Data Source=C:\Users\Public\Documents\TestComplete 14 Samples\Desktop\Checkpoints\XML\DataGridViewSample\OrdersDB.mdb").

I asked our developer, and I was told that our database connecting string was something like "data source=xxx.xx.xxx.xxx;initial catalog=xxxx;integrated security=true;multipleactiveresultsets=true;app=xxxx", so is this the value I should place after AConnection.ConnectionString = ? I tried so, but I got a runtime error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." Not sure if it was because the connection code I executed was wrong or because the Microsoft Data Access Components was missing, because I tried to install the downloaded file ( v 2.8 SP1), but nothing happens after I double clicked on it.

I am totally lost, can someone please help me  to fix this?

Thanks!

[AlexKaras]

Hi,

> Multiple-step OLE DB operation generated errors.

This means that executed SQL contained more than one statement. ADO supports one statement per SQL, so instead of batch SQL execution you need to execute it line-by-line.

[whuang]

Hi @AlexKaras , I asked our database guy to take a look at my script below, and he doesn't know why it failed either. So my script is like this:

11 Sub try
12
13 Set AConnection = ADO.CreateConnection
14
15 AConnection.ConnectionString = "Provider=SQLNCLI11;data source=xxx-xxx-xxx.xx.xxxx.xxx;initial catalog=xxxxx;integrated security=true;multipleactiveresultsets=true;app=xxxxx;"
16
17 AConnection.Open
18
19
20 AConnection.Close
21
22 End Sub

Below is the error details returned:

VBScript runtime error.

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Error location:
Unit: "E-Academy\E-Academy\Script\WilliamTestScript"
Line: 17 Column: 3.

Looks like it was complainting about line 17 - AConnection.Open

Can you please give more instruction how to fix it? BTW, do I have to have SQL server 20xx installed on the machine first in order to run the script? because currently I don't have it nor the Native Client on the machine.

Thanks!

[AlexKaras]

Hi,

Does the same error occur if you remove 'multipleactiveresultsets=true;' from the connection string?

[whuang]

Yes, I removed 'multipleactiveresultsets=true;' from the connection string, and tried on both machines that have Native Client 11 and doesn't have Native Client 11.

It still returned the same VBScript runtime error "

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." on the machine that has Client 11, and it returned a different runtime error "

Provider cannot be found. It may not be properly installed.

" on the machine that doesn't have client 11.

[whuang]

Update, looks like the connection string I used was wrong. I take advantage of the wizard to build a connection string, and used it in the script, and it worked.

[whuang]

Hi @AlexKaras I think I still need your help regarding this. Now I can setup the SQL connection, but when I tried to execute the query below, it returned me another error: "Object doesn't support named arguments: DBConnection.CommandText".

Set DBConnection = ADO.CreateConnection
DBConnection.ConnectionString = "Provider=xxxx;Integrated Security=xxxx;Persist Security Info=False;User ID=xxxx;Initial Catalog=xxxx;Data Source=xxxx"
DBConnection.Open

DBConnection.CommandText = "My Query to Run"
DBConnection.Execute()

DBConnection.Close

[whuang · Accepted solution]

Nevermind, I figured out, it should be DBConnection.Execute(Query to run)