New to ADODB

Targets Windows
1

Working on some code for a project and I am just not seeing wtf I’ve done wrong with my ADODB connection string

Any hints would be appreciated

Oh and if I change the user to “IEUser” it wont connect either

yet management studio seems to be connected fine with that user id & password

confused

2

perhaps stating the obvious, but did you enable the communication protocols in the SQL Server configuration tool? It normally is accessible either in the sql server application menu, or in the management console.

3

also (still in the obvious department): what version of the adodb connector do you have installed? recent ones expect a .net application.

if the connector is the native client 2012, then try with this: Provider=SQLNCLI11

4

ah yes I did turn tcp ip on

thanks for the hints

I’ll give those a whirl

5

nuts … this is so frustratingly obtuse

6

Just taking a quick glance at some examples, none of them seem to have a leading spaces:

Provider=sqloledb;Data Source=MyServerName;Initial Catalog=MyDatabaseName;User Id=MyUsername;Password=MyPassword;

vs.

Provider=sqloledb; Data Source=MyServerName; Initial Catalog=MyDatabaseName; User Id=MyUsername; Password=MyPassword;

100% guessing, but I could see this being an issue?

7

a couple more:

  • did you look whether the firewall is either off or allows the sql server ports?
  • is the Express instance really named “SQLEXPRESS”? it can be changed during install.

This is my connexion string that worked with a express server. I changed the tcp address etc. with generic stuff:

ConStr = "Provider=SQLNCLI11; Data Source = MyServer\\SQLEXPRESS; Initial Catalog = mydatabase; Uid=myUser; Pwd=mypassword; DataTypeCompatibility=80;" Note that I am using UID instead of User Id. But i believe both are accepted.

I had some issues connecting after a windows 10 update (was it the spring 2018 update?). It was later resolved by another update. Check whether one or the other computers needs updates.

8

lord knows
I get an exception of some kind but the IDE just shows up with nothing in the debugger
<https://xojo.com/issue/55901>
if i then hit resume there’s a message dialog that just say “connection failed” more or less

9

Restart VM
Restart Xojo
Changed connection string to
connectionString = “Provider=SQLOLEDB; Data Source=MSEDGEWIN10\SQLEXPRESS; Initial Catalog=Testing; User Id=IEUser; Password=Passw0rd!; Integrated Security=SSPI”

OR
connectionString = “Provider=SQLNCLI11;Data Source=MSEDGEWIN10\SQLEXPRESS; Initial Catalog=Testing; User Id=IEUser; Password=Passw0rd!; Integrated Security=SSPI”

and both now work

Go figure