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::Properties collection in ADO.Net?

Regards,
John Brown.

"Mark Rae [MVP]" wrote:

> "John Brown" <(E-Mail Removed)> wrote in message
> news:5F054111-768C-4FD8-86FF-(E-Mail Removed)...
>
> > 2) More generally, is there an equivalent to the
> > ADODB.Connection::Properties collection in ADO.Net?
>
> More pertinently, why on earth are you using ODBC...?
>
>
> --
> Mark Rae
> ASP.NET MVP
> http://www.markrae.net
>

And what, pray tell, should I be using?

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" <(E-Mail Removed)> wrote in message
news:E7322825-2163-4B59-9AA4-(E-Mail Removed)...
>
>
> "Mark Rae [MVP]" wrote:
>
>> "John Brown" <(E-Mail Removed)> wrote in message
>> news:5F054111-768C-4FD8-86FF-(E-Mail Removed)...
>>
>> > 2) More generally, is there an equivalent to the
>> > ADODB.Connection::Properties collection in ADO.Net?
>>
>> More pertinently, why on earth are you using ODBC...?
>>
>>
>> --
>> Mark Rae
>> ASP.NET MVP
>> http://www.markrae.net
>>
>
> And what, pray tell, should I be using?

"Mark Rae [MVP]" wrote:

> "John Brown" <(E-Mail Removed)> wrote in message
> news:E7322825-2163-4B59-9AA4-(E-Mail Removed)...
>
> >> "John Brown" <(E-Mail Removed)> wrote in message
> >> news:5F054111-768C-4FD8-86FF-(E-Mail Removed)...
> >>
> >> > 2) More generally, is there an equivalent to the
> >> > ADODB.Connection::Properties collection in ADO.Net?
> >>
> >> More pertinently, why on earth are you using ODBC...?
> >
> > And what, pray tell, should I be using?
>
> ODBC was superseded thirteen years ago by OleDb:
> http://msdn.microsoft.com/en-us/library/ms810892.aspx
> http://database.ittoolbox.com/docume...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?

<snip>

> 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.

"Scott M." wrote:

>
> 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.

"John Brown" <(E-Mail Removed)> wrote in message
news:937278FB-2561-43F4-BB09-(E-Mail Removed)...
>
>
> "Scott M." wrote:
>
>>
>> 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.

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/dave...erFactory.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

"John Brown" <(E-Mail Removed)> wrote in message
news:197C6525-4154-4C0C-9932-(E-Mail Removed)...
>
>
> "Mark Rae [MVP]" wrote:
>
>> "John Brown" <(E-Mail Removed)> wrote in message
>> news:E7322825-2163-4B59-9AA4-(E-Mail Removed)...
>>
>> >> "John Brown" <(E-Mail Removed)> wrote in message
>> >> news:5F054111-768C-4FD8-86FF-(E-Mail Removed)...
>> >>
>> >> > 2) More generally, is there an equivalent to the
>> >> > ADODB.Connection::Properties collection in ADO.Net?
>> >>
>> >> More pertinently, why on earth are you using ODBC...?
>> >
>> > And what, pray tell, should I be using?
>>
>> ODBC was superseded thirteen years ago by OleDb:
>> http://msdn.microsoft.com/en-us/library/ms810892.aspx
>> http://database.ittoolbox.com/docume...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.

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/tec...net/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

>
> <snip>
>
>> 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.

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

-Scott


"Scott M." <s-(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "John Brown" <(E-Mail Removed)> wrote in message
> news:937278FB-2561-43F4-BB09-(E-Mail Removed)...
>>
>>
>> "Scott M." wrote:
>>
>>>
>>> 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.
>
> 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/dave...erFactory.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." wrote:

>
> "John Brown" <(E-Mail Removed)> wrote in message
> news:937278FB-2561-43F4-BB09-(E-Mail Removed)...
> >
> >
> > "Scott M." wrote:
> >
> >>
> >> 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.
>
> 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/dave...erFactory.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.

>

<snip>

> 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.

<snip>

> > 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.
>

From the description of SQLDriverConnect in the ODBC API reference at
http://msdn.microsoft.com/en-us/libr...8VS.85%29.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.

"John Brown" <(E-Mail Removed)> wrote in message
news:C5F5D28B-2A07-45F7-996A-(E-Mail Removed)...
>
>
> "Scott M." wrote:
>
>>
>> "John Brown" <(E-Mail Removed)> wrote in message
>> news:937278FB-2561-43F4-BB09-(E-Mail Removed)...
>> >
>> >
>> > "Scott M." wrote:
>> >
>> >>
>> >> 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.
>>
>> 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.

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.

>
>> but there's no reason
>> you can use the DBProvider factory classes to accomodate either database
>> being used.
>>
>> http://www.davidhayden.com/blog/dave...erFactory.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.

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.

>
>>
>
> <snip>
>
>> 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.

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?

>
> <snip>
>
>> > 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.
>>
>
> From the description of SQLDriverConnect in the ODBC API reference at
> http://msdn.microsoft.com/en-us/libr...8VS.85%29.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