OleDbConnection

  • Thread starter Thread starter abcd
  • Start date Start date
A

abcd

string strDSN = lstDSN.SelectedItem.Value.ToString();
OleDbConnection Conn = new OleDbConnection(strDSN);
Conn.Open();


Why the above code fails at line second. I want to open the conection using
OleDBConnection and want to pass the DSN there

thanks
 
string strDSN = lstDSN.SelectedItem.Value.ToString();
OleDbConnection Conn = new OleDbConnection(strDSN);
Conn.Open();


Why the above code fails at line second. I want to open the conection
using OleDBConnection and want to pass the DSN there
Click to expand...

You've really got to offer a bit more information if you expect people to
help you... E.g.

1) What is the value of strDSN?

2) How do you know that the second line fails? What error message are you
getting.
 
Basically I am trying get the table names for a particular DSN using C#.NET
string strDSN = lstDSN.SelectedItem.Value.ToString(); //strDSN = say
"foodmart 2000" which is my DSN on the machine, properly configured and
worrking
OleDbConnection Conn = new OleDbConnection(strDSN);
Conn.Open();
System.Data.DataTable schemaTable =
Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null,
null, "TABLE"});
lstTables.DataSource =schemaTable;
Conn.Close();

I get error which says "Format of the initialization string does not conform
to specification starting at index 0. "

the equivalent ADO code is as below

onst adSchemaTables = 20
' -- create objects
Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset

Set objConn = New ADODB.Connection
objConn.Open "foodmart 2000"

' -- Get list of tables - ALL tables
Set objRs = objConn.OpenSchema(adSchemaTables)
' -- Walk the recordset
Do While Not objRs.EOF

If objRs("TABLE_TYPE") = "TABLE" Then
List1.AddItem (objRs("TABLE_NAME"))
End If
objRs.MoveNext
Loop
' -- Close all objects
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing

thanks
 
Hi Mark

you are right, I want the same thing ...only thing is that in the
OleDbConnection I need to pass the DSN name dynamically...I dont want to
create the Provider string etc...

thanks
 
Basically I am trying get the table names for a particular DSN using
C#.NET
string strDSN = lstDSN.SelectedItem.Value.ToString(); //strDSN = say
"foodmart 2000" which is my DSN on the machine, properly configured and
worrking
Click to expand...

You "appear" to be trying to tell ADO.NET to create an OleDbConnection using
an ODBC DSN...
 
you are right, I want the same thing ...only thing is that in the
OleDbConnection I need to pass the DSN name dynamically...I dont want to
create the Provider string etc...
Click to expand...

Erm, but that's not how OleDb works...
 
You have to say OLEDB you are using ODBC as a provider and the name of the
DSN. OLEDB is not ODBC and therefore you cannot assume it will understand
the DSN name only by passing it as the connection string.

Your connection string should look like this:

Trusted_Connection=Yes;DSN=foodmart
2000;UID=youruser;DATABASE=yourdatabase;APP=Microsoft® Visual Studio
..NET;WSID=yourworkstation

Some of this fields are optional, probably you could reduce this connection
string like this:

DSN=foodmart 2000

Hope this helps.


abcd said:
Basically I am trying get the table names for a particular DSN using C#.NET
string strDSN = lstDSN.SelectedItem.Value.ToString(); //strDSN = say
"foodmart 2000" which is my DSN on the machine, properly configured and
worrking
OleDbConnection Conn = new OleDbConnection(strDSN);
Conn.Open();
System.Data.DataTable schemaTable =
Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null,
null, "TABLE"});
lstTables.DataSource =schemaTable;
Conn.Close();

I get error which says "Format of the initialization string does not conform
to specification starting at index 0. "

the equivalent ADO code is as below

onst adSchemaTables = 20
' -- create objects
Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset

Set objConn = New ADODB.Connection
objConn.Open "foodmart 2000"

' -- Get list of tables - ALL tables
Set objRs = objConn.OpenSchema(adSchemaTables)
' -- Walk the recordset
Do While Not objRs.EOF

If objRs("TABLE_TYPE") = "TABLE" Then
List1.AddItem (objRs("TABLE_NAME"))
End If
objRs.MoveNext
Loop
' -- Close all objects
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing

thanks





Mark Rae said:
You've really got to offer a bit more information if you expect people to
help you... E.g.

1) What is the value of strDSN?

2) How do you know that the second line fails? What error message are you
getting.
Click to expand...
Click to expand...
 
I get error when I use the below statement

OleDbConnection Conn = new OleDbConnection("DSN=foodmart 2000");

error is

An OLE DB Provider was not specified in the ConnectionString. An example
would be, 'Provider=SQLOLEDB;'.

Marvin Varela said:
You have to say OLEDB you are using ODBC as a provider and the name of the
DSN. OLEDB is not ODBC and therefore you cannot assume it will understand
the DSN name only by passing it as the connection string.

Your connection string should look like this:

Trusted_Connection=Yes;DSN=foodmart
2000;UID=youruser;DATABASE=yourdatabase;APP=Microsoft® Visual Studio
.NET;WSID=yourworkstation

Some of this fields are optional, probably you could reduce this
connection
string like this:

DSN=foodmart 2000

Hope this helps.


abcd said:
Basically I am trying get the table names for a particular DSN using C#.NET
string strDSN = lstDSN.SelectedItem.Value.ToString(); //strDSN = say
"foodmart 2000" which is my DSN on the machine, properly configured and
worrking
OleDbConnection Conn = new OleDbConnection(strDSN);
Conn.Open();
System.Data.DataTable schemaTable =
Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null,
null, "TABLE"});
lstTables.DataSource =schemaTable;
Conn.Close();

I get error which says "Format of the initialization string does not conform
to specification starting at index 0. "

the equivalent ADO code is as below

onst adSchemaTables = 20
' -- create objects
Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset

Set objConn = New ADODB.Connection
objConn.Open "foodmart 2000"

' -- Get list of tables - ALL tables
Set objRs = objConn.OpenSchema(adSchemaTables)
' -- Walk the recordset
Do While Not objRs.EOF

If objRs("TABLE_TYPE") = "TABLE" Then
List1.AddItem (objRs("TABLE_NAME"))
End If
objRs.MoveNext
Loop
' -- Close all objects
objRs.Close
Set objRs = Nothing
objConn.Close
Set objConn = Nothing

thanks





Mark Rae said:
string strDSN = lstDSN.SelectedItem.Value.ToString();
OleDbConnection Conn = new OleDbConnection(strDSN);
Conn.Open();


Why the above code fails at line second. I want to open the conection
using OleDBConnection and want to pass the DSN there

You've really got to offer a bit more information if you expect people to
help you... E.g.

1) What is the value of strDSN?

2) How do you know that the second line fails? What error message are you
getting.
Click to expand...
Click to expand...
Click to expand...
 
I get error when I use the below statement

OleDbConnection Conn = new OleDbConnection("DSN=foodmart 2000");

error is

An OLE DB Provider was not specified in the ConnectionString. An example
would be, 'Provider=SQLOLEDB;'.
Click to expand...

With the greatest of respect, how many more times will it be necessary for
us to tell you that OleDb and ODBC are not the same? What you are trying to
do is NEVER going to work in the way you are trying...
 
¤ string strDSN = lstDSN.SelectedItem.Value.ToString();
¤ OleDbConnection Conn = new OleDbConnection(strDSN);
¤ Conn.Open();
¤
¤
¤ Why the above code fails at line second. I want to open the conection using
¤ OleDBConnection and want to pass the DSN there

As Mark mentioned, the DSN technology was designed for ODBC and isn't natively supported by OLEDB.
If you really want to use a DSN w/OLEDB you can probably use the Microsoft OLEDB Provider for ODBC:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdrefodbcprovspec.asp

However, if I was going to use a DSN I would simply use the .NET ODBC provider and avoid the
overhead of OLEDB.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
I'm sorry, Paul is right, i didn't notice i was using
System.Data.Odbc.OdbcConnection and not OLEDB when i sent you the example:

Trusted_Connection=Yes;DSN=foodmart
2000;UID=youruser;DATABASE=yourdatabase;APP=Microsoft® Visual Studio
..NET;WSID=yourworkstation

And

DSN=foodmart 2000

So if you change your types to use ODBC instead of OLEDB this connection
strings will work. The easiest way to do this is to connect to your
datasource using the server explorer in VS.Net and then drag-n-drop the
connection to your project. This way it will generate the connection and the
connection string appropriately.

There is also a way to use OLEDB to connect to ODBC sources, but as Paul
says, this adds innecessary overhead to the connection.

Sorry for the inconveniences.
 
You guys confused me !!!

My requirements

1. I have a DSN name. I dont have provider string details.
2. I want to access the Table names from the DSN

Whats available to me at this moment through ADO.NET

1. OLEDBConnection supports GetOleDbSchemaTable method which can get me the
table names for a particular DSN
2. ODBCConenction supports DSN but doesnt support something like
GetODBCDBSchemaTable

Problems

1. If I use OLEDBConnecton to call GetOleDbSchemaTable I can not construct
the provider connection string (because I have only DSN name, and I dont
know the details about that DSN connection, to which DB it connects and so
on)

Solutions I used

I wrote a VB component uses ADO and calls ADODB connection's OpenSchema
method. I am calling this VB component from my ASP.NET page and solving my
purpose.

I read that ADO.NET 2.0 is going to provide the GetODBCSchemaTable
(something like this) ......

Thanks
 
¤ You guys confused me !!!
¤
¤ My requirements
¤
¤ 1. I have a DSN name. I dont have provider string details.
¤ 2. I want to access the Table names from the DSN
¤
¤ Whats available to me at this moment through ADO.NET
¤
¤ 1. OLEDBConnection supports GetOleDbSchemaTable method which can get me the
¤ table names for a particular DSN
¤ 2. ODBCConenction supports DSN but doesnt support something like
¤ GetODBCDBSchemaTable
¤
¤ Problems
¤
¤ 1. If I use OLEDBConnecton to call GetOleDbSchemaTable I can not construct
¤ the provider connection string (because I have only DSN name, and I dont
¤ know the details about that DSN connection, to which DB it connects and so
¤ on)
¤
¤ Solutions I used
¤
¤ I wrote a VB component uses ADO and calls ADODB connection's OpenSchema
¤ method. I am calling this VB component from my ASP.NET page and solving my
¤ purpose.
¤
¤ I read that ADO.NET 2.0 is going to provide the GetODBCSchemaTable
¤ (something like this) ......

I followed up on my suggestion and apparently the OLEDB Provider for ODBC (MSDASQL) is not supported
by the OLEDB.NET provider under ADO.NET - it's only supported under ADO.

That would leave you with *your* solution (or ADOX) since there is currently no .NET support for
database schema retrieval via ODBC.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks Paul thats what I ultimately figured out. Its all coming in ADO.NET
2.0. Whidbey....

Cheers !
 
Back
Top