Cannot Connect to New Database Server using ADO

[frank_vanderstr]

I have created automation scripts that used an ADO command object to connect to our internal server's databases and make several select, update and insert statements, and that would run great.

We recently migrated to a new database server and now even with the proper edits to my connection string, I can't appear to connect to the database despite the connection string test being a success.

Here is an example of my code.

var Qry = ADO.CreateADOCommand();

var dbname = DatabaseName;
Qry["ConnectionString"] = "Provider=SQLOLEDB.1;Password=pw;Persist Security Info=True;User ID=SQLServerUsr;Initial Catalog=dbname;Data Source=Servername";
for(var i = 1; i <= rowcount; i++) {
                   var testcell = VarToStr(xlsheet.Cells(i+1, 61).Value);
                     Log.Message(testcell);
                   tags[i] = testcell;
                  Qry["CommandText"] = "SELECT catalogid FROM oproducts WHERE oproducts.cname = '" + prodnamesarray[i] + "'"
                 Qry.CommandType = cmdText;
               var catalognum = Qry["Execute"]();
              prodids[i] = catalognum.Fields.Item("catalogid").Value;

}

for(var j = 1; j < rowcount; j++) {
            if(tags[j] != "") {
                   Qry["CommandText"] = "INSERT INTO MenuTagProductMap (MenuTagID, MenuProductID, EnteredDate) VALUES('" + tags[j] + "', '" + prodids[j] + "', GETDATE())";
                  Qry.CommandType = cmdText;
}
}

I also made my connection string by using the stores object in testcomplete to connect to my database initially.

I was made aware of permission changes on the new server, but I have had my test user given the proper permissions, but it doesn't appear to be connecting still, anyone have any ideas as to why this may be?

[frank_vanderstr · Accepted solution]

The error occurs on the first instance of calling Qry[Execute], so the first loop where I call SELECT.

[tristaanogre]

Ok... just something I'm looking at here. In the examples that I'm sering for using the ADO command objects, before the execute, the ADO Connection needs to be created and assigned to the object. I'm not in front of a PC to try it out yet but check out the examples at https://msdn.microsoft.com/en-us/library/ms675065(v=vs.85).aspx and see if that helps out.

[frank_vanderstr]

Creating the ADO Connection object seems to be redundant code, as you are just setting the Command Objects Connection String to the Connection object, and calling Execute opens the connection with the connection string set in the Command Object.

[tristaanogre]

Totally agree with that assessment. But comparing what you have with Microsofts published example, that seems to be the only major difference. Id at least give it a try. But I'll keep researching in the meantime

[frank_vanderstr]

Afraid using the connection object caused a log in prompt to appear when trying to connect even with the user info in the connection string.

It also caused an error I could not exit from, as a pop up window saying the thread was not synchronized would not close.

[tristaanogre]

Hrm... ok, then... thanks for trying.

I'm not certain, at this point, what the next step is. It seems that something within the permissions are blowing up.

Do you have access to the server side logs? Is there a way you can find out whether or not the server is receiving the correct information. Also, try writing out the connection string to the test log to make sure that nothing is getting sent improperly.

Perhaps someone else may have another direction to try.

[frank_vanderstr · Accepted solution]

I was able to figure out the root cause of the issue with some help.

Turns out TestComplete likes to leave out "\" in strings, so putting \\ in my connection string got me connected properly.

[tristaanogre]

That would do it.

FYI, that leaving out of the \ I'd actually a function of the language you are using. In some of the languages supported by TestComplete, that symbol is used to mark a special character like \' to actually print the single quote rather than terminate a string. So, to tell TC to actually include the \ character you need to double it up. I should have caught that in your earlier example.

[eqrojas]

Just also make my connection string to '\\' and it works!