ODBC string in ADO.NET

[Alex K.]

Hi all

I just noticed that OdbcConnection (.NET 2.0 SP1) does not accept a
connection string that works file in ADO 2.8. For example, following string
gives me an error "Login failed ... cannot open database "AdventureWorks"":

Provider=MSDASQL.1;Password=...;Persist Security Info=True;User
ID=...;Extended Properties="Description=Adventure;DRIVER=SQL
Server;SERVER=...\sqlexpress;UID=...;PWD=...;DATABASE=AdventureWorks"

I figured out that last quotation mark causes the problem: if I remove it,
it works fine - but then it does not work in ADO 2.8!

Can anybody confirm this is a known problem with .NET 2.0 ODBC?
(BTW I tried both drivers SQL Server and SQL Native Client with the same
results)

Thank you
Alex

 

[Alex K.]

In my post, it should read ADO 2.6 instead of ADO 2.8.
It's just a typo. Thank you.

 

[Nicholas Paldino [.NET/C# MVP]]

Alex,

Why should it work? You are using an Ole DB connection string with the
ODBC provider classes. If you want to use the same connection string that
you use with ADO then you should be using the classes in the System.OleDb
namespace.

 

[christery]

Hi all

I just noticed that OdbcConnection (.NET 2.0 SP1) does not accept a
connection string that works file in ADO 2.8. For example, following string
gives me an error "Login failed ... cannot open database "AdventureWorks"":

Provider=MSDASQL.1;Password=...;Persist Security Info=True;User
ID=...;Extended Properties="Description=Adventure;DRIVER=SQL
Server;SERVER=...\sqlexpress;UID=...;PWD=...;DATABASE=AdventureWorks"

I figured out that last quotation mark causes the problem: if I remove it,
it works fine - but then it does not work in ADO 2.8!

Can anybody confirm this is a known problem with .NET 2.0 ODBC?
(BTW I tried both drivers SQL Server and SQL Native Client with the same
results)

Thank you
Alex

The /cannot open database "AdventureWorks"": / with double citation
looks strange as you indicate, not anything else changed? its often
the small things/changes that will tip the whole carrage...

Thinking along why they /maybe/ change the connectionstring handler...
and didnt realize its not working... nah... dont think so...

and as I know its up to 3.5 for the .net so upgrading a bit just to
try is a way to go...

does the mdac keep up with this(as in updated that too), Im just
guessing, as usual...

for 2.8 you should be at win2003S, yes I read that you misspelled, but
2.6 is not shipped, thease are as I know

Windows ME ADO 2.5
Windows 2000 ADO 2.5
Windows XP ADO 2.7
Windows 2003 Server ADO 2.8


//CY

 

[Alex K.]

Alex,

Why should it work? You are using an Ole DB connection string with the
ODBC provider classes. If you want to use the same connection string that
you use with ADO then you should be using the classes in the System.OleDb
namespace.

....and that's exactly what I did! When trying for the first time, I used
OledbConnection and got the error:

The .Net Framework Data Provider for ELEDB (System.Data.OleDb) does not
support the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL). Use the
..Net Framework Data Provider for ODBC (System.Data.Odbc).

after getting this error I started to use OdbcConnection from
System.Data.Odbc, and I got the posted error. BTW, the ODBC connection string
was created using Microsoft Data Link Properties wizard. Same string works
fine in ADO, but it does not work in .Net Odbc. As I pointed out in my post,
a quotation mark that closes Extended Properties substring is what is causing
the problem - which is very strange!

I always used to generate ODBC strings using a certain set of rules and
until now it worked pretty well. Now when I am migrating my VB6 + ADO 2.6
application to .Net 2.0 I have to double check (and most likely change) all
my ODBC connection strings! And I cannot use Data Link wizard dll anymore,
because connection string produced may not work.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hi all

I just noticed that OdbcConnection (.NET 2.0 SP1) does not accept a
connection string that works file in ADO 2.8. For example, following
string
gives me an error "Login failed ... cannot open database
"AdventureWorks"":

Provider=MSDASQL.1;Password=...;Persist Security Info=True;User
ID=...;Extended Properties="Description=Adventure;DRIVER=SQL
Server;SERVER=...\sqlexpress;UID=...;PWD=...;DATABASE=AdventureWorks"

I figured out that last quotation mark causes the problem: if I remove it,
it works fine - but then it does not work in ADO 2.8!

Can anybody confirm this is a known problem with .NET 2.0 ODBC?
(BTW I tried both drivers SQL Server and SQL Native Client with the same
results)

Thank you
Alex