A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in...

[th3gman]

Here's hoping someone can help a noob out..

I am working out of an old book (Beginning VB.NET Databases) circa 2004'ish... and I'm working in Visual Studio 2008 and running Windows 7 x64.

I've determined that the code for the project I am working on is sound, as I downloaded the same completed code out from WROX (the publisher of the book) and it generated the same error. I can only conclude it is some sort of permissions or new (Windows 7, Visual Studio 2008, .NET 3.5) environment issue that causing the error.

When I go to Debug my app... I get this In the "Intermediate Window":
A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll

Any ideas?

Many thanks.

 

[jmcilhinney]

The problem would appear to be that the code is trying to retrieve the DataTable by name without having specified that name when populating it. This code:

OdbcDataAdapter1.Fill(CustomerDS1)

will create and populate an unnamed DataTable in the DataSet. In order to retrieve that table you would have to use an index rather than a name, i.e.

CustomerDS1.Tables(0)

That will get the first DataTable. If you want to be able to get the table by name then you must specify the name when creating it, i.e.

OdbcDataAdapter1.Fill(CustomerDS1, "Customers_csv")

 

[th3gman]

Wow...

CustomerDS1.Tables(0)

Did the trick... why it was coded the other (broken) way I have no idea. I typed the code in by hand directly from their code in the book per the books instructions.

Do you know any reason they would go about it that way when they simply could have done

CustomerDS1.Tables(0)

 

[jmcilhinney]

Personally, I prefer to use named tables. When you see the name in code it tells you exactly what the table is for. Using a 0 gives no indication of what the purpose of that table is. I would tend to leave the name in and add the name when calling Fill rather then the other way.

 

[JohnOfStony]

VB.NET/SQL 2008 EXPRESS - Connection Strings

Personally, I prefer to use named tables. When you see the name in code it tells you exactly what the table is for. Using a 0 gives no indication of what the purpose of that table is. I would tend to leave the name in and add the name when calling Fill rather then the other way.

I found this discussion very interesting as it is close to my problem. I am writing a program without a predefined database location so I can't use the wizard to create a data source connection without having to recompile for every different database location. I have done this many times in VB6 but VB.NET is unbelievably different for what should be a simple operation. VB.NET doesn't seem to support ADO which I was using in VB6 and ODBC seems to be the only option. I've tried using the connection string I used in VB6 but it throws up the error message at the start of this thread, "A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll". Can you direct me to documentation on connection strings and what the various parts of a connection string do as then I should be able to figure out what's wrong with the various connection strings I have tried. I've looked at what appeared to be a useful site, SQL Server 2005 Connection String Samples - ConnectionStrings.com, but it has dozens of connection strings and not enough detail to explain which one I should use. The documentation I have found all seems centred on using wizards rather than code. The book you refer to earlier does contain code BUT for an Access database so the connection string used there doesn't work. Any help or suggestions would be much appreciated. This is particularly frustrating as I've worked in software development since 1978 and feel I should be able to sort this out myself.

 

[jmcilhinney]

I found this discussion very interesting as it is close to my problem. I am writing a program without a predefined database location so I can't use the wizard to create a data source connection without having to recompile for every different database location. I have done this many times in VB6 but VB.NET is unbelievably different for what should be a simple operation. VB.NET doesn't seem to support ADO which I was using in VB6 and ODBC seems to be the only option. I've tried using the connection string I used in VB6 but it throws up the error message at the start of this thread, "A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll". Can you direct me to documentation on connection strings and what the various parts of a connection string do as then I should be able to figure out what's wrong with the various connection strings I have tried. I've looked at what appeared to be a useful site, SQL Server 2005 Connection String Samples - ConnectionStrings.com, but it has dozens of connection strings and not enough detail to explain which one I should use. The documentation I have found all seems centred on using wizards rather than code. The book you refer to earlier does contain code BUT for an Access database so the connection string used there doesn't work. Any help or suggestions would be much appreciated. This is particularly frustrating as I've worked in software development since 1978 and feel I should be able to sort this out myself.

First of all, VB.NET does support ADO but you should never use it in new code. It should only be used if you have a VB6 application upgraded via the wizard.

Secondly, ODBC is not an alternative to ADO. ADO can use OLE DB providers or ODBC drivers as an intermediary between an application and a data source and so can ADO.NET.

Thirdly, if you're connecting to SQL Server then you should absolutely not be using ODBC. You should be using SqlClient, which is an ADO.NET provider dedicated to SQL Server specifically.

As for your issue, what exactly do you mean by "database location"? If you mean that the database will always have the same schema but the folder for the data file or the database instance may be different each time then that is absolutely no impediment to using the Data Source wizard. You run the wizard and it will create a connection string and store it in the config file. If you ever need to connect to a different database, e.g. after deployment, then you simply edit the config file.

If you're connecting to SQL Server then the Data Source wizard will use SqlClient under the hood. ConnectionStrings.com will almost certainly give you everything you need for a SqlClient connection string. If you have some special need then how about you explain that to us and then we can deal with that specifically? In almost all cases though, all you will need is the Data Source, the Initial Catalog or the AttachDbFileName, and Integrated Security or the User Id and Password.

 

[JohnOfStony]

Many thanks indeed - I wasn't even aware of the SQLClient Connection. Once I started using it I was able to use the connection string generated by the wizard and all is now OK. A great introduction to VB.NET Forums. Your other advice and comments were also very helpful.