what's wrong with this ADODB connection string to sql server 2005

Question

I'm working on populating a combobox in visual studio 2005 using vb and I'm stuck on the connection string part. I should mention i'm connecting to a SQL Server 2005 instance. Here is what i have:

Dim gDBA As ADODB.Connection
Dim records As ADODB.Recordset

gDBA = New ADODB.Connection 
gDBA.Open("Server=e-13;Database=subscribers;User ID=KViews;Password=Solution;Trusted_Connection=False;", "KViews", "Solution")

I got the connection string from http://www.connectionstrings.com/sql-server-2005#p1

When I click 'run', i get a COMException was unhandled message : "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified."

I'm guessing the connection string is looking for a System ODBC driver name, however, i'm not sure where to put this. any ideas on this or what else I might be doing wrong?

thanks in advance

Answer 1

You're probably missing "Provider=SQLNCLI" or "Provider=SQLOLEDB" or "Driver={SQL Native Client}" from the conn string. The article you quoted shows 2 of these, but without SQL Native client installed you can rely on SQLOLEDB

This specifies the driver, otherwise it it derived from a DSN set via control panel. This explains the error.

Answer 2

What about using "Data Source" and "Initial Catalog" instead of "Server" and "Database"?

Answer 3

If you're connecting to a SQL server try using the System.Data.SqlClient namespace.

Dim cn As New SqlClient.SqlConnection("User ID=KViews;Password=Solution;Initial Catalog=subscribers;Data Source=e-13")
cn.Open()
Dim cmd As New SqlClient.SqlCommand("Select * from tabel")
cmd.Connection = cn
Dim r As SqlClient.SqlDataReader = cmd.ExecuteReader

(SqlClient is managed code - ADODB native)

Answer 4

This connection string is currently in use on an asp app connecting to Sql Server 2008 Express.

"Driver={SQL Native Client};Server=serverName;Database=databaseName;Uid=userId;pwd=password;connect timeout=60;"