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?
Solved! Go to Solution.
> 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.
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
13 Set AConnection = ADO.CreateConnection
15 AConnection.ConnectionString = "Provider=SQLNCLI11;data source=xxx-xxx-xxx.xx.xxxx.xxx;initial catalog=xxxxx;integrated security=true;multipleactiveresultsets=true;app=xxxxx;"
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.
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.
Does the same error occur if you remove 'multipleactiveresultsets=true;' from the connection string?
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 "
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.
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.CommandText = "My Query to Run"