Cannot display ODBC login prompt - want to connect without DSN

[John Brown]

Hello All,

In ADO, I can do this

'VBScript

Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "{SQL Server}"
cnn.Properties("Prompt") = 2 'Prompt if needed, I think
cnn.Open

The SQL Server ODBC login box will appear and the user can then fill in
whatever information is needed: Windows or SQL Server authentication, server
name, etc.

I am trying to write a program that will save a connection string for any
ODBC database.


I cannot find the equivalent of 'cnn.Properties("Prompt") = 2' in ADO.Net. I
am using Visual Basic 2008 Express with the .NET Framework 3.5 SP1.

'Visual Basic .NET

Dim cnn as New OdbcConnection
cnn.ConnectionString = "{SQL Server}"
cnn.Open 'fails because connection string is incomplete

Questions:

1) How can I make the ODBC login box appear in an ADO.Net app, other than
falling back to ADO (or horror of horrors, calling the ODBC API directly)?

2) More generally, is there an equivalent to the
ADODB.Connection:roperties collection in ADO.Net?

Regards,
John Brown.

 

[John Brown]

More pertinently, why on earth are you using ODBC...?

And what, pray tell, should I be using?

 

[Scott M.]

ODBC was effecitvely replaced by OLE DB Providers about 10 years ago and
these providers are available in .NET.

Depending on the database you are using, you'll need a specific provider
with a specific connection string to match it. If you want the user to be
able to supply their user ID and password, that would be a simple matter of
providing a logon form and then take the data from that form and make it
part of the connection string.

For SQL Server, you'd have something similar to this:

Dim con As New System.Data.SqlClient.SqlConnection(connectionStringHere)

Try
con.Open
'use connection here
Catch e As Exception
'handle exceptions here
Finally
con.Close()
con.Dispose()
End Try

Various configurations of connection strings can be found here:

http://ConnectionStrings.com

-Scott

 

[John Brown]

ODBC was superseded thirteen years ago by OleDb:
http://msdn.microsoft.com/en-us/library/ms810892.aspx
http://database.ittoolbox.com/documents/odbc-vs-oledb-18150

I knew about OleDB. Wasn't ADO built open OleDB as a way of shielding VB
programmers from the complexity of OleDB? The real reason I have been ODBC
with ADO is that I am lazy. I cannot remember the names of the providers in
which I am interested. I have to look them up each time.

In this particular case, my program will conect to an Oracle database, but I
don't have Oracle here. I'm too lazy to make a trip just so that I can run my
program (to convert an XML file into records in a table) against an Oracle
database. There does not seem to be a .NET provider for MS Access, and even
if there were, I would have to write different code depending on the database
that I was connecting to:

dim cnn as JetConnection 'or something like that
and change it to
dim cnn as OracleClientConnection

You haven't answered the original question. Can you try this one instead:

If I use the ADO.Net provider for OleDB, and I allow the user to select an
OleDB provider from a list of providers installed on his PC , will I be able
to display that provider's login box and let the user connect, and then save
the resulting ConnectionString to be re-used in the future?

These days, there are native .NET data providers for all major databases,
and these run rings round OleDb in terms of performance and manageability.

I would certainly hope so, but to be honest, I have never seen Microsoft
release a new product that was more efficient than the previous one. Anyway,
I will take your word for it.

Generally speaking, there is no good reason for using ODBC if OleDb is
available, and no good reason for using OleDb if a native .NET data provider
is available.

Especially with SQL Server...

Your point is well taken, but as I said earlier, my app needs to work with 2
databases.

Regards,
John Brown.

 

[John Brown]

Depending on the database you are using, you'll need a specific provider
with a specific connection string to match it.

This is what I am trying to avoid. I want to write *one* program that will
work with *two* databases (MS Access and Oracle) with *no* code changes
whatsoever. Even though I know exactly which databases my progam is expected
to work with, so I don't have to support an arbitrary unknown database, I
still want to be able to use the program with a database other than Access or
Oracle. If the OleDB provider has its own login dialog box, I would rather
display that one than make my own form to do the same thing.

With ODBC, this is quite simple. I can present a list of installed ODBC
drivers. When the user selects one, I just need to:

'VBScript
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Driver={" selectedDriver & "}"
cnn.Properties("Prompt") = 2
cnn.Open

If I call OleDBConnection::Open with an incomplete ConnectionString (just
the provider name) can I make the provider display a login dialog box?

Regards,
John Brown.

 

[Scott M.]

This is what I am trying to avoid. I want to write *one* program that will
work with *two* databases (MS Access and Oracle) with *no* code changes
whatsoever.

Well, think about what you just said. There will objviously have to be code
set up to accomodate the two possible database types, but there's no reason
you can use the DBProvider factory classes to accomodate either database
being used.

http://www.davidhayden.com/blog/dav...singDbProviderFactoriesDbProviderFactory.aspx

Even though I know exactly which databases my progam is expected
to work with, so I don't have to support an arbitrary unknown database, I
still want to be able to use the program with a database other than Access
or
Oracle. If the OleDB provider has its own login dialog box, I would rather
display that one than make my own form to do the same thing.

There are no "login boxes" for OleDB. As I said, if you want a user to
supply their credentials, you can create your own login form for them to
supply the information to you and you can inject those credentials into the
connection string.

With ODBC, this is quite simple. I can present a list of installed ODBC
drivers. When the user selects one, I just need to:

'VBScript
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Driver={" selectedDriver & "}"
cnn.Properties("Prompt") = 2
cnn.Open

If I call OleDBConnection::Open with an incomplete ConnectionString (just
the provider name) can I make the provider display a login dialog box?

Again, no. the login box which you are talking about was provided to ODBC by
the Windows Operating System via the ODBC Managaer. Taking ODBC out of the
picture (which you should) means that the dialogs are gone too.

-Scott

 

[Scott M.]

I knew about OleDB. Wasn't ADO built open OleDB as a way of shielding VB
programmers from the complexity of OleDB? The real reason I have been ODBC
with ADO is that I am lazy. I cannot remember the names of the providers
in
which I am interested. I have to look them up each time.

The ActiveX Data Objects (ADO) are simply API's to the particular OleDB
Provider. They give a developer a common way to work with different
providers.

In this particular case, my program will conect to an Oracle database, but
I
don't have Oracle here. I'm too lazy to make a trip just so that I can run
my
program (to convert an XML file into records in a table) against an Oracle
database. There does not seem to be a .NET provider for MS Access, and
even
if there were, I would have to write different code depending on the
database
that I was connecting to:

dim cnn as JetConnection 'or something like that
and change it to
dim cnn as OracleClientConnection

You're not quite correct here. It's much easier than you percieve it to be.
To utilizie the provider for Access just use the types in System.Data.OleDB
namepace (the OleDbConnection, OleDbCommand, etc.). Here's a link to get
the Oracle provider:
http://www.oracle.com/technology/tech/windows/odpnet/index.html.

You can absolutley create one set of code that will handle either database
being used and it's called creating a Database Provider Facotry. I've
posted a link in my other comment about doing it.

You haven't answered the original question. Can you try this one instead:

If I use the ADO.Net provider for OleDB, and I allow the user to select an
OleDB provider from a list of providers installed on his PC , will I be
able
to display that provider's login box and let the user connect, and then
save
the resulting ConnectionString to be re-used in the future?

Again, OleDbProviders do not have a user interface. There is no such thing
as this login box that you are asking about.

What you've got to understand is that Open DataBase Connectivity was a
WINDOWS feature that allowed a developer to create a Data Source Name (DSN)
that was specific to a particular SYSTEM. To aid the developer, Microsoft
created a UI for creating these DSN's, wich was the ODBC Manager, available
via Control Panel. The problem with this approach was twofold...First, the
DSN's are machine-specific and need to be set up identically on each and
every machine that the program was to run on. Second, the whole ODBC
architecture was an extra layer built on top of the database driver that
actually was performing the work. This was like having a middle-man talk to
another middle-man to talk to your database. It made ODBC versitile, but
also slow.

Object Linking & Embedding DataBase (OLEDB) Providers replaced ODBC and
solved both those problems. When using OLEDB, there is no UI to create the
DSN to talk to the database driver. And that means there's no more DSN's
either. And that means you don't have to run around and set this stuff up
on each pc which will run the application. With OLEDB, your programming
code talks directly to the database provider, cutting out the extra middle
man and making the process much more streamlined, which results in superior
performance.

Because OLEDB Providers are "talked" to exclusively through your program's
code, there is no system "login" boxes or any other UI to get in the way.
You simply code your application to use a particular provider and that's
it.

You have mentioned that your application will make use of Access and Oracle
and want a coding solution that will accomodate both. That's not a problem.
DataBase Provider Factories are a relatively straight-forward way to solve
that issue. If you need the end user to supply their credentials, then just
prompt them for that informaiton and use it in your connection strings.

One piece of advice, and don't take this as an insult because it's truly not
meant that way....To work with .NET effectively, you've got to understand
that VBScript and many of the programming paradigms that came before it are
dead, as it relates to .NET. Forget VBScript, it 100% irrelevant to how VB
..NET is written and functions. Forget ODBC, it's archaic and doesn't
perform nearly as well as OleDb.

Good luck!

-Scott

 

[Scott M.]

That should be "no reason you CAN'T use the DBProvider factory classes..."

-Scott

 

[John Brown]

Well, think about what you just said. There will objviously have to be code
set up to accomodate the two possible database types,

Not necessarily. My app is not going to create the database or the tables.
It simply loads an XML files into two tables with no columns with exotic data
types. There is exactly *one* Access-specific SQL statement in there, but I
am going to get rid of it.

but there's no reason
you can use the DBProvider factory classes to accomodate either database
being used.

http://www.davidhayden.com/blog/dav...singDbProviderFactoriesDbProviderFactory.aspx

I have not read this thoroughly yet. It looks promising, even though it
seems that it still relies on pre-existing knowledge of the full connection
string.

There are no "login boxes" for OleDB. As I said, if you want a user to
supply their credentials, you can create your own login form for them to
supply the information to you and you can inject those credentials into the
connection string.

So you're saying that if I wanted to write a report writing tool using
OleDB, which is supposed to work with just about any database that exists now
or may be developed in the future, I would have to display a dialog box
asking for the usual suspects (user name, password, database, server) and
another text box for extra connection parameters, because I hav no way to
discover them at run time.

My users, who may not even know what an OleDB connection string is, are now
supposed to look in the programmer's documentation for their database, where
they might never think of looking because they are not programmers, so that
they can use my fantastic reporting tool?

That does not look like progress to me, but maybe the DBProvider business
above solves the problem.

Again, no. the login box which you are talking about was provided to ODBC by
the Windows Operating System via the ODBC Managaer. Taking ODBC out of the
picture (which you should) means that the dialogs are gone too.

From the description of SQLDriverConnect in the ODBC API reference at
http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx

"Based on the value of DriverCompletion, the driver prompts the user for
connection information, such as the user ID and password, and connects to the
data source:

SQL_DRIVER_PROMPT: *** The driver displays a dialog box ***,
============================
using the values from the connection string and system information (if any)
as initial values. When the user exits the dialog box, the driver connects to
the data source. It also constructs a connection string from the value of the
DSN or DRIVER keyword in *InConnectionString and the information returned
from the dialog box. It places this connection string in the
*OutConnectionString buffer."

Even without this documentation, it is obvious that each ODBC driver *must*
export a function to do this so that the ODBC Driver Manager can call it when
required. Otherwise the Driver Manager would have to magically know all the
required and optional values that can be used to make a connection for maybe
dozens or hundreds of drivers, and then construct a dialog box at run time,
complete with tabs, checkboxes and what not.


Regards,
John Brown.

 

[Scott M.]

Not necessarily. My app is not going to create the database or the tables.
It simply loads an XML files into two tables with no columns with exotic
data
types. There is exactly *one* Access-specific SQL statement in there, but
I
am going to get rid of it.

Yes necessarially. If you are going to possibly deal with two different
databases (regardless of what you intend to do with those databases), your
application must be prepared to connect to the two different databases.
There's no getting around that.

I have not read this thoroughly yet. It looks promising, even though it
seems that it still relies on pre-existing knowledge of the full
connection
string.

Well, you need to know the basics of the connection string and can populate
the unknowns at runtime with data from the client. If you want to connect
to Access, you must know how to do that. If you want to connect to Oracle,
you must know how to do that as well. But, we're just talking about the
connection string here. The beauty of the ADO .NET objects is that once
you've made your connection, you use the objects the same way.

I may be wrong, but I'm getting the impression that you belive you can (or
should be able to) code a database application that *may* need to connect to
one of two database types without knowing how to code or having to code that
functionality.

So you're saying that if I wanted to write a report writing tool using
OleDB, which is supposed to work with just about any database that exists
now
or may be developed in the future, I would have to display a dialog box
asking for the usual suspects (user name, password, database, server) and
another text box for extra connection parameters, because I hav no way to
discover them at run time.

Well, if you don't know the details of the connections you need to make at
design-time, then when else would you get them but run-time? And if you
don't have the information and only the end user does, then who else can you
ask to supply the detials? You should only need to ask them for their user
name, password, and database.

My users, who may not even know what an OleDB connection string is, are
now
supposed to look in the programmer's documentation for their database,
where
they might never think of looking because they are not programmers, so
that
they can use my fantastic reporting tool?

Your users don't need to even hear the words "connection string" or "OleDb",
but yes, shouldn't they know where their own data is and their own
credentials to access it?

That does not look like progress to me, but maybe the DBProvider business
above solves the problem.

The DBProvider Factory will only help you code one pattern that can work for
the two different databases, it's not going to help you figure out your
connection strings. Honestly, (and I may be missing something here), but I
am a bit dumbfounded that you have resistence to the idea that a user would
need to supply credentials to access a secured resource. You seemed to be
ok with the ODBC login dialog popping up to promt the user to fill in the
details, why are you having trouble with having them do the exact same
thing, but with a dialog that you create?

From the description of SQLDriverConnect in the ODBC API reference at
http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx

"Based on the value of DriverCompletion, the driver prompts the user for
connection information, such as the user ID and password, and connects to
the
data source:

SQL_DRIVER_PROMPT: *** The driver displays a dialog box ***,
============================
using the values from the connection string and system information (if
any)
as initial values. When the user exits the dialog box, the driver connects
to
the data source. It also constructs a connection string from the value of
the
DSN or DRIVER keyword in *InConnectionString and the information returned
from the dialog box. It places this connection string in the
*OutConnectionString buffer."

Even without this documentation, it is obvious that each ODBC driver
*must*
export a function to do this so that the ODBC Driver Manager can call it
when
required. Otherwise the Driver Manager would have to magically know all
the
required and optional values that can be used to make a connection for
maybe
dozens or hundreds of drivers, and then construct a dialog box at run
time,
complete with tabs, checkboxes and what not.

I'm not sure what your point is here. OleDb is NOT ODBC. Comparing the two
architectures for similarities is futile.

-Scott

 

[John Brown]

??? Where on earth did you get that from...

From the ActiveX Data Objects Reference:
http://msdn.microsoft.com/en-us/library/ms678262(VS.85).aspx

ADO Introduction


"ADO provides a layer of abstraction between your client or middle-tier
application and the low-level OLE DB interfaces. ADO uses a small set of
Automation objects to provide a simple and efficient interface to OLE DB.
This interface makes ADO a good choice for developers in higher level
languages, such as Visual Basic and VBScript, who want to access data without
having to learn the intricacies of COM and OLE DB
There is always the risk of something being lost in translation when you
try to condense a paragraph into a single line, but what is the difference
between this paragraph and what I said?

As for the "complexity" of OleDb, you're joking, right...?

Well, they said "intricacies" and I said "complexity". If you don't like
what they write in MSDN, you may want to take it up with Bill, but I believe
that they are talking about the difficulty of using OleDB in VB, and not
OleDB's feature set. I think that the problem is that OleDB API uses a lot
of pointers, but don't quote me. I don't have a reference for that.

There does not seem to be a .NET provider for [MS Access] Jet,

Indeed. As mentioned, that's when you use OleDb:
http://www.connectionstrings.com/access
http://www.connectionstrings.com/access-2007

and even if there were, I would have to write different code depending on
the database
that I was connecting to:

Nope - that's what a DAL based on a factory pattern is for:
http://msdn.microsoft.com/en-us/magazine/cc163766.aspx#S5
http://www.microsoft.com/downloads/...8B-2986-47F7-B529-3E41584B6CE5&displaylang=en

After a multitude of posts with much information that is completely
irrelevant to my simple and clearly-stated problem, we're finally getting
somewhere. I have not gone through it yet, but it seems to be a more
complicated and less general technique than the DBProvider class mentioned by
the other poster.

Yes.

The other poster who has replied says that you can't. In particular, he says
that OleDB providers do not have their own login dialog box so I must supply
my own. Of course, I would rather agree with you.

See above...
My DAL works with SQL Server, SqlCe, Oracle, MySql and Jet (which some
people incorrectly refer to as Access), and can be dropped *completely*
unmodified into any WinForms or WebForms project. If I ever need to support
another RDBMS, it would be a simple matter of adding it to the factory
pattern.

So you support several databases with the same code, but to add another, you
would have to make a small change. This will work for me, but it is not the
same as my program being able to connect to an arbitrary database without my
knowledge or permission.

Regards,
John Brown.

 

[John Brown]

Yes necessarially.

No, not necessarily.

If you are going to possibly deal with two different
databases (regardless of what you intend to do with those databases), your
application must be prepared to connect to the two different databases.
There's no getting around that.

Please forget the .NET evangelism for a minute and concentrate on what I am
saying. Very early in the piece, I posted ADO code that could connect to any
database without knowing anything beforehand except the driver name. If you
have a generic API that will handle specific objects of different types, it
is wise to have ways of querying the objects to find out their capabilities,
ways to tell them to configure themselves, etc.

What you are telling me is that if I am writing a word processor and my app
wants to print, it should display a standard print dialog box, and if my
users want to use some printer-specific feature that I didn't think of, then
too bad. Fortunately it doesn't worklike that. The printer driver knows how
to configure the printer, so neither my app nor GDI needs to know what the
printer is capable of. However, ny GDI code to print would be exactly the
same, regardless of the printer. I do not consider this to be a bogus concept.

Well, you need to know the basics of the connection string and can populate
the unknowns at runtime with data from the client. If you want to connect
to Access, you must know how to do that. If you want to connect to Oracle,
you must know how to do that as well. But, we're just talking about the
connection string here. The beauty of the ADO .NET objects is that once
you've made your connection, you use the objects the same way.

I may be wrong, but I'm getting the impression that you belive you can (or
should be able to) code a database application that *may* need to connect to
one of two database types without knowing how to code or having to code that
functionality.


Well, if you don't know the details of the connections you need to make at
design-time, then when else would you get them but run-time? And if you
don't have the information and only the end user does, then who else can you
ask to supply the detials? You should only need to ask them for their user
name, password, and database.

Now I understand. You are assuming that connecting to a database is just a
matter of supplying credentials. (By the way, you left out server). Even
supplying credentials can be tricky. Consider SQL Server, but remember that
my app has no built-in knowledge of SQL Server. I present a list box with the
names of all OleDB providers on your computer. You select the one for SQL
Server. I display my generic box to get your user name, password, database
and server. I Build my ConnectionString: Provider=whatever;Data Source=User
Id=johnbrown;Password=password;I

Problem: Your environment uses Windows Authentication.

Problem: Even if you leave the fields blank, you still cannot connect,
because I need to specify the keyword to turn on Windows authentication.

Problem: the program doesn't know that.

There are more problems, such as:

Problem: How do I specify the database? Sometimes, the keyword is "Initial
Catalog", sometimes it is "Database"; there may be others. There are endless
keywords. Some are necessary, some are optional. For example, maybe system
tables are hidden by default (SQL Anywhere), but now I need to see them. How
would I know how to make system tables visible?

You can connect to all sorts of databases, and things that are not really
databases, such as a SharePoint site, an MS Exchange Server, etc. A generic
dialog box will not work for all data sources.

Your users don't need to even hear the words "connection string" or "OleDb",
but yes, shouldn't they know where their own data is and their own
credentials to access it?

It's not all about credentials. See above.

The DBProvider Factory will only help you code one pattern that can work for
the two different databases, it's not going to help you figure out your
connection strings. Honestly, (and I may be missing something here),

Yes, you certainly are, but I hope that my latest explanation clears things
up.

but I
am a bit dumbfounded that you have resistence to the idea that a user would
need to supply credentials to access a secured resource.

I have not the slightest idea what you are talking about. I am resisting the
idea that with backward, "dead" technology, I could write a program to
connect to a database and execute a few inserts and selects without knowing
or caring (much) about the database,in the same way that a program can print
regardless of the printer, or send emails without knowing which email program
is installed. Now I have to care, because I can't connect unless I specify a
correct connection string.

You seemed to be
ok with the ODBC login dialog popping up to promt the user to fill in the
details, why are you having trouble with having them do the exact same
thing, but with a dialog that you create?

It's not all about credentials. You are telling me that if I want to write
an app that will work with any database (let's say any database that has a
certain minimum capability), then I would have to write a login dialog box
for each one. This is not practical. Anyway, I have not investigated
DBProvider yet.

I'm not sure what your point is here. OleDb is NOT ODBC. Comparing the two
architectures for similarities is futile.

I am saying that the ability to connect to a database without knowing the
details is not magic; it was provided by each ODBC driver. If OleDB providers
do not have this capability, then it is a backward step.

Regards,
John Brown.

 

[John Brown]

:

Forget ODBC, it's archaic and doesn't
perform nearly as well as OleDb.

Good luck!

Why do you think that they have released a new ODBC version (3.8) for
Windows 7?

Regards,
John Brown.

 

[John Brown]

For databases which have neither a native .NET data provider nor an OleDb
provider.

<snip>

They did not have to add new features, but they did, so as far as they are
concerned ODBC is not dead just yet.

 

[Scott M.]

Please forget the .NET evangelism for a minute and concentrate on what I

am
saying. Very early in the piece, I posted ADO code that could connect to
any
database without knowing anything beforehand except the driver name. If
you
have a generic API that will handle specific objects of different types,
it
is wise to have ways of querying the objects to find out their
capabilities,
ways to tell them to configure themselves, etc.

What you are telling me is that if I am writing a word processor and my
app
wants to print, it should display a standard print dialog box, and if my
users want to use some printer-specific feature that I didn't think of,
then
too bad. Fortunately it doesn't worklike that. The printer driver knows
how
to configure the printer, so neither my app nor GDI needs to know what the
printer is capable of. However, ny GDI code to print would be exactly the
same, regardless of the printer. I do not consider this to be a bogus
concept.

There's no evangelism going on here. I think you are presuming that ADO
..NET and OleDb must somehow work just as ADO and ODBC did, which they do
not. It's actually very simple, in order to connect to any data source, you
need a connection string and different data sources use different connection
strings. You are telling me that you'll need to connect to Access and
Oracle, so that means you need to have two different connection strings.
The best practice is to store connection strings in your app.config or
web.config file, so you'd need to set up the basic shell for both of theses
and fill in the missing pieces later. This isn't a big deal and I'm not
sure why you seem so hung up on it. You'll just have two connection strings
standing by and a simple "if" statement to determine which is necessary at
runtime.

There's nothing about this that is evangelistic or even an opinion. This is
how connections are made in ADO .NET with OleDb - - you need to forget about
ODBC, ADO, and VB Script. They are gone (and a long time ago).

Now I understand. You are assuming that connecting to a database is just a
matter of supplying credentials. (By the way, you left out server). Even
supplying credentials can be tricky. Consider SQL Server, but remember
that
my app has no built-in knowledge of SQL Server. I present a list box with
the
names of all OleDB providers on your computer. You select the one for SQL
Server. I display my generic box to get your user name, password, database
and server. I Build my ConnectionString: Provider=whatever;Data
Source=User
Id=johnbrown;Password=password;I

First, I reject the scenario you have started out with. All you need from
the end user is their id, password, and database. You would not present
them with a list of OleDb Providers because they wouldn't understand what
that list represents. Since you've stated that you might need Access or
Oracle, you could just provide them with a radiobutton set with those two
choices.

Problem: Your environment uses Windows Authentication.

This is less a programming issue and more of a configuration issue. But, if
you need windows authentication, you can certainly set up the connection
string to use it.

Problem: Even if you leave the fields blank, you still cannot connect,
because I need to specify the keyword to turn on Windows authentication.

Problem: the program doesn't know that.

This is where I am bit confused. Are you saying that the user gets to tell
your program if Windows Authentication is being used? This is not something
that end users know and understand and hardly something that you'd ask them
to tell you. You should know how your users need to connect and code for
that.

There are more problems, such as:

Problem: How do I specify the database? Sometimes, the keyword is
"Initial
Catalog", sometimes it is "Database"; there may be others. There are
endless
keywords. Some are necessary, some are optional. For example, maybe system
tables are hidden by default (SQL Anywhere), but now I need to see them.
How
would I know how to make system tables visible?

Solution, the SqlConnectionStringBuilder class. This is not the problem you
think it is.

You can connect to all sorts of databases, and things that are not really
databases, such as a SharePoint site, an MS Exchange Server, etc. A
generic
dialog box will not work for all data sources.

Your OP was that you need to code against Access or Oracle, now you are
saying it could be anything?

It's not all about credentials. See above.

It really is. There's a picture that I have obviously not been able to
convey to you, but you only need 3 pieces of info. from your users and it
need not be technical:

What is the Database/Data Source you want to connect to (a simple dropdown
list can handle this - no need to talk about providers).
What is your User Name?
What is your ID?

Based on the answer to question #1, your program uses the correct connection
string and fills in the gaps with #'s 2 and 3.

Yes, you certainly are, but I hope that my latest explanation clears
things
up.



I have not the slightest idea what you are talking about. I am resisting
the
idea that with backward, "dead" technology, I could write a program to
connect to a database and execute a few inserts and selects without
knowing
or caring (much) about the database,in the same way that a program can
print
regardless of the printer, or send emails without knowing which email
program
is installed. Now I have to care, because I can't connect unless I specify
a
correct connection string.

But, with ODBC you couldn't connect without using the proper DSN. We're
simply replacing your code's selection of a DSN with your code's selection
of a connection string. Just like your ODBC programs supplied a way for the
user to tell you who they are, your OleDb programs would do the same.

It's not all about credentials. You are telling me that if I want to write
an app that will work with any database (let's say any database that has a
certain minimum capability), then I would have to write a login dialog box
for each one. This is not practical. Anyway, I have not investigated
DBProvider yet.

What I am having trouble understanding is that it seems that you want the
user to tell the program (or you think that I'm saying that a user should
tell the program) explicit details about how to connect to the desired data
source. I'm not saying that, but it seems that you are and I don't
understand why.

-Scott

 

[Scott M.]

:



Why do you think that they have released a new ODBC version (3.8) for
Windows 7?

Because they haven't actually dropped the entire technology, but for all
intents and purposes, there is no reason to use ODBC in new application
development unless no OleDbProvider exists but an ODBC driver does. This is
not the case most of the time.

-Scott

 

[Scott M.]

After a multitude of posts with much information that is completely
irrelevant to my simple and clearly-stated problem, we're finally getting
somewhere. I have not gone through it yet, but it seems to be a more
complicated and less general technique than the DBProvider class mentioned
by
the other poster.

This is exactly what I've been recommending to you all along. The "factory"
pattern is the same thing as the DBProvider solution I've been suggesting.

The other poster who has replied says that you can't. In particular, he
says
that OleDB providers do not have their own login dialog box so I must
supply
my own. Of course, I would rather agree with you.

As I stated, OleDb does not supply ANY form of UI. What I belive Mark was
saying "yes" to was the "could I save the connection string for later use"
part, which you can.

So you support several databases with the same code, but to add another,
you
would have to make a small change. This will work for me, but it is not
the
same as my program being able to connect to an arbitrary database without
my
knowledge or permission.

John, I really am trying to help you out here, but I've got to say that you
seem to be shooting down suggested courses of action, without really having
any knowledge or experience with what's being suggested. To accomplish what
you want is not at all complicated and does not put the burdens on your
end-user that you keep saying it will. Take it from people who have done
exactly what you are saying you want to do.

I wish you good luck with it, but I can't see a continued debate about the
correct course of action with someone who seems to want to shoot down
exactly the solution that they are seeking. If I haven't explained the
solution correctly, my apologies, but you can get just what you want with
the DB Factory paradigm (implemented via the DBProvider classes) and two
basic connection strings that are completed with user imput using the
ConnectionStringBuilder class.

Please take the time to research these items (links previously provided) and
you'll see that it's what you want. It may not have the built in login
boxes that you used to have, but the performance gains as well as the device
indepenence is well worth doing DSN-less connections, which were introduced
13 years ago.

-Scott

 

[John Brown]

This is exactly what I've been recommending to you all along. The "factory"
pattern is the same thing as the DBProvider solution I've been suggesting.

OK.


As I stated, OleDb does not supply ANY form of UI. What I belive Mark was
saying "yes" to was the "could I save the connection string for later use"
part, which you can.

Well, I didn't ask *part* of a question. I asked a whole question, and I am
sure that you cannot deny that I asked it very clearly and concisely, so if
he said "Yes", I can only assume that he must be have been responding to the
whole question.

John, I really am trying to help you out here, but I've got to say that you
seem to be shooting down suggested courses of action, without really having
any knowledge or experience with what's being suggested.

I have not shot down anything. I already said that DBProvider looks
promising, but you're right. I won't have anything more to say on this
subject until I try it.

... you can get just what you want with
the DB Factory paradigm (implemented via the DBProvider classes) and two
basic connection strings that are completed with user imput using the
ConnectionStringBuilder class.

The very name of this class fills me with ecstasy. This is of course,
exactly what I want to do.

Please take the time to research these items (links previously provided) and
you'll see that it's what you want.

I will.

It may not have the built in login
boxes that you used to have, but the performance gains as well as the device
indepenence is well worth doing DSN-less connections, which were introduced
13 years ago.

Not to flog a dead horse, but DSN-less connections existed in ODBC 1.0
(Sep.1992, according to Wikipedia.)

Regards,
John Brown.

 

[Scott M.]

Not to flog a dead horse, but DSN-less connections existed in ODBC 1.0
(Sep.1992, according to Wikipedia.)

Well, I wasn't an ODBC expert, but I doubt the accuracy of that statement.
By it's very definition, ODBC required DSN's. The DSN's were the middle-man
created by the ODBC manager. The DSN was the extra layer that is precisely
the thing that causes the performance bottlenecks. It was OleDB that freed
us from ODBC and thus, DSN's. In other words ODBC = DSN.

-Scott

 

[Scott M.]

Well, I didn't ask *part* of a question. I asked a whole question, and I
am
sure that you cannot deny that I asked it very clearly and concisely, so
if
he said "Yes", I can only assume that he must be have been responding to
the
whole question.

I can only assume he was respoding to *part* of the question because I'm
fairly confident that he does know that OleDb has no UI. So, if he knows
that, then his "yes" couldn't possibly be to answer both questions you
asked.

Just my take on it.

-Scott