Why does this OdbcConnection throw me a System.InvalidOperationException?

Question

Hi community,

I am building a Winforms C# 2.0 application.

I have successfully been able to connect to my SLQ Server database using the following: m_connexion = new SqlConnection("server=192.168.xxx.xxx;uid=...;pwd=...;database=...");

Because my company wanted to be able to use any database, I went on to use the Odbc driver and my commands went on like this:

m_connexion = new OdbcConnection("server=192.168.xxx.xxx;uid=...;pwd=...;database=...");

However, this throws out a System.InvalidOperationException. Any idea why?

I'm also trying to use a DSN, but the commend OdbcConnection connection = new OdbcConnection("DSN=MyDataSourceName"); suggested here but it likewise throws my a System.InvalidOperationException

Answer 1

The connection string needs a Provider= so that the ODBC drivers know which server you're connecting to. In this case Provider=SQLSERVER I believe.

UPDATE: Should have been Provider=SQLOLEDB

Answer 2

I think you need to specify a driver. Look here for details: http://connectionstrings.com/sql-server-2005#21

Answer 3

If you specify a DSN, you have to configure the DSN using the ODBC control panel. It's called "Set up data sources (ODBC)" under Administrative Tools. The panel also has a "test" button, which might tell you more about what's going wrong.

P.S. Being "database independent" is much more work than using ODBC connection, command and datareader. You'd have to make sure your queries run on each target database, which you will not be able to do if you don't have a test server of each. So if I were you, I'd code it up using SqlConnection, since you already got that working.