VS 2005 express: ".NET Framework Data Provider for ODBC" does not show up in Choose/Change Data Source dialog :(

Question

Can anyone help me with this? I desperately need that ODBC Connector in order to access an MS SQL 2000 Server. I am using VB Express 2005 Beta 2, and as I mentioned in the topic, that ODBC Data Provider won't show up in the Dialog, the only choice I have there is ".NET Framework Data Provider for SQL Server", and there is no other Data Provider available.

Any way to fix that?

thanks in advance!

Answer 1

Hi,

did you try to to this by code, like here?

using System.Data;
using System.Data.OleDb;

The System.Data.OleDb namespace contains the components necessary to implement the OLE DB data provider. Now, double click on the button control and add this code into the buttons click event:

private void button1_Click(object sender, System.EventArgs e)

{

OleDbConnection Myconnection = null;
OleDbDataReader dbReader = null;

Myconnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=C:\familyTree.mdb");
Myconnection.Open();

OleDbCommand cmd = Myconnection.CreateCommand();
cmd.CommandText = "SELECT * FROM familyTree";
dbReader = cmd.ExecuteReader();

string Email;

while(dbReader.Read())
{
Email = (string)dbReader.GetValue(5);
lb.Items.Add(Email);

}

dbReader.Close();
Myconnection.Close();
}

To make a connection to the database, we should use a connection object from the ADO.NET Classes. Before we do anything with the database, we must have an active, working connection to it. Connection objects have different parameters, dependant upon the underlying database. For example, if we use an MS-Access database, then this code will establish a session with the database:

OleDbConnection Myconnection = null;

Myconnection = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0; User Id=; Password=; Data Source=C:\familyTree.mdb");
Myconnection.Open();

An OleDbConnection object represents a unique connection to a data source. When you create an instance of OleDbConnection, all properties are set to their initial values. The OleDbconnection object has one string parameter with four attributes for the connection. The are as follows:

• Provider: This is dependant upon the underlying database. In this example we use the Microsoft Jet database engine to connect to a MS-Access database.
  
  We can add new vender-specific data providers. OLEDB and SQL data providers are the standard data providers that come with the .NET framework.
• User Id and Password: The username and password to connect to the database.
• Data Source: This is the physical location of the database file.

from: http://www.devarticles.com/c/a/ADO.NET/Data-Access-in-.NET-using-C-sharp-Part-1/1/

Answer 2

Thanks for your reply!

I am quite sure this would work, since the system.data.odbc namespace exists. However, I would like to get the Data Source Wizard working, since i don't know how to build the ConnectionString. Currently, I have to use the old VS 2003 to create an ODBC Connection there and then copy&paste the ConnectionString to VBE 2005, which is quite annoying.

I will try to reinstall the Framework and VBE 2005, maybe that works...

any other suggestions?

Answer 3

Well, that didn't work.

Something i tried out: When adding the ODBC Connection Toolbox item and trying to add an ODBC Connection, VBE 2005 responds with a critical error box saying: "Unexpected Error". As if errors weren't unexpected anyways. Any way to work around that?

Answer 4

Hi,

>As if errors weren't unexpected anyways.

There are errors you can expect. For Example if you connect to a database in the internet, you can expect that your user may have no inet connection when trying to connect. So you can write code for this error.

Answer 5

The data designers in the Express editions of Visual Studio only allow you to connect to SQL Server Express and Access (Jet) databases.  I've passed your feedback along to the people who own these components in the hopes of getting this limitation clearly documented.

As Ralph noted, you can still use the various .NET Data Providers at run-time.

David Sceppa
ADO.NET Program Manager
Microsoft

Answer 6

David,

I've seen MSDN documentation that shows this function as working where Express and VS products are dicussed side by side.

The documentation needs to begin here at this dialog box as it clearly says:

"Use this selection to attach a database file to a local Microsoft SQL database instance  (Including MS SQL Express) using the .NET Framework Data Provider for SQL Server."


It should say that Express users can only use the wizard on local SQL and MS Access Databases. 

It's very typical of Microsoft to leave out what can't be done forcing us to run around circles on tech boards finding the "RIGHT" answer.  

It would be very useful to have tech bulletins that specifically reference dialog boxes and screens as very often the problem eminates from that box.

I will say that ASP 2.0 is the best release I've ever seen come out of Microsoft and I've been dealing with this for the 20 years now starting back in DOS x.x. 

Pete

Pete Bennett
www.sensibleprogramming.com
www.petebennett.net
www.contractorewire.com

 

www.msofficeexperts.com

 

[e-mail]

Answer 7

Hi David,

I am also trying to make an ODBC connection live with VB2005 Express.

I wonder why it is not easily accessible through visual dev steps....

Anyway, I just added an OdbcConnection object to my project but whenever I try to create a new Connectionstring of it in the Properties window, I keep recevig an error:

"Data providers identified by GUID 0000-000000-000000000-0000000000000....  could not be loaded." 

What is the problem?

Thanks a lot,

Gyozo