What's wrong with this ASP connection string?

Question

I'm at the end of my rope on this. It should be so simple. I just need to know what's wrong with this connection string:

dbc.open ("Driver={SQL Server}; Data Source = ServerName; Initial Catalog = InitialDB; " "User ID = Username; Password = Password;")

I get this error when running that line:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I know ServerName is up and accepting connections, I know InitialDB exists, I know User ID and Password are valid for the database. What am I missing?

Answer 1

Try this...

dbc.open ("Provider=SQLOLEDB; Driver={SQL Server}; Data Source = ServerName; Initial Catalog = InitialDB; User ID = Username; Password = Password; Network Library=dbmssocn;")

Answer 2

What's with the " " in the middle of the string?

Answer 3

Do you have visual studio?

Connect to the database server, and locate the database you want to connect to.

Right click, select properties. Your connection string to the db is right there. Copy to wherever you want. -- Should be in web config, but you can paste it directly into code if you so desire.

Answer 4

In notepad create file anyname.udl - let it be empty. After in explorer click it - you will get a dialog to create OLEDB connection string, select expected driver, and all connection param, ensure that "Allow saving password" = True. Press Ok. Then again open file with notepad. Content is valid connection string

Answer 5

As someone has already pointed out, udl is the best easiest way to create a conn string - here is a link that talks about it. https://web.archive.org/web/20210211044624/http://www.4guysfromrolla.com/webtech/070400-1.shtml

Answer 6

Your connection string appears to be mixing ODBC and OLEDB. I would suggest visiting http://www.connectionstrings.com/ and finding the correct syntax for the desired provider.

Yours:

"Driver={SQL Server}; Data Source = ServerName; Initial Catalog = InitialDB; " "User ID = Username; Password = Password;"

ODBC:

"Driver={SQL Server};Server=ServerName;Database=InitialDB;Uid=Username;Pwd=Password;"

OLEDB:

"Provider=sqloledb;Data Source=ServerName;Initial Catalog=InitialDB;User Id=Username;Password=Password;"

Answer 7

If you are using ADOdb you might want to try

"Provider=SQLNCLI10;Server=SERVER;Database=DATABASE;Uid=USERNAME;Pwd=PASSWORD"

for SQL Server 2008 Native Client or

"Provider=SQLNCLI;Server=SERVER;Database=DATABASE;Uid=USERNAME;Pwd=PASSWORD"

for SQL Server 2005 Native Client.

For ODBC, use

"Driver=SQL Server Native Client 10.0"

for SQL Server 2008 Native Client or

"Driver=SQL Native Client"

for SQL Server 2005 Native Client.