Stupid $%^@#$ OleDbdataAdapter.Fill (Datatable) as Integer

[countzero]

Been staring at this for 2 days now.
I have left out everything and condensed the problem to the following bit of code.
Note: Opening locations.mdb (Access2003 database) manually and running the SQL query returns a single column with about 10 records.

Stupid $%&#@# VB Studio Express 2010 .NET Stupid Imports and the lack of proper documentation / code samples / error handling make me want to bang my head against a wall.

This example is like fetching 'hello world' from a record in a database table. It doesn't get much simpler than this and just look at this mess :smash: < smiley hitting VB Studio Express with large hammer.

 

[jmcilhinney]

I don't know what you mean by "lack of error handling" because the only place I see no error handling is in your code. If you add a Try/Catch block then you will have access to the OleDbException object and you will be able to enumerate its Errors collection and look at each item. That said, given that the Message of the exception says "Unspecified error", I'm not sure that you'll get much more from the Errors.

While I would have expected a different error message under these circumstances, my first question is whether or not you're running on a 64-bit OS and, if you are, what is the target platform for your project? Jet won't work with 64-bit apps, but it looks like you're OK there.

Next, I would forget the data adapter and just try calling Open on the connection. From what we can see of the StackTrace, I'm guessing that that will fail, so the issue is related to the connection and not the query.

Next, I'd try using the Data Source wizard and see if that works. If it does then it is likely something that you have done, otherwise it's likely a system issue, either a fault with ADO.NET or the OLE DB provider.

 

[Herman]

If you are on Windows 7 Service Pack 1 there is a known compatibility break with the DAO objects. There is a backcompat library out there that is supposed to fix the problem, but it did not work for me. Note that previously compiled binary work fine, applications linked to the libraries just refuse to compile after SP1. Something about messing up some GUIDs.

However this does not look like the classic symptoms, I would look at making sure you have permissions to write to the folder where the .mdb is, as JET needs to create the lock file. You also need to have write permissions to the %temp% folder, but that should be so unless there is something very wrong with your permissions.

 

[countzero]

- Try/Catch says the same thing.
- Win7x86 (no 64 bit).
- conn.open() fails.
- Data Source Wizard works, but I don't know how to use it to fill a Combobox1.Items in less than 5 lines of code. I don't like the Data Source Wizard as it obfuscates code and introduces complexity and -by a lack of a better description- 'GUI goop'. I can't easily integrate code variables/parameters into the query editor embedded in the the Data Source connection. Also, I can't set the location of the Data Source connection *.mdb file at runtime. If anything in my code would break in a next version of VB Studio or a conversion to Sharp Develop, it would be the Data Source connection wizard generated objects/code.
- I have successfully used this code on WinXP and Win7x86. Only recently this problem started to occur. The previously compiled executable still works fine on all computers. I have reinstalled VB .NET...same problem. I can't tell whether or not I was previously running Win7 SP1 or not (work computer that gets frequently rebuilt). Currently it is running Win7x86 enterprise SP1...
- "There is a known compatibility break with the DAO objects." I am using "Imports System.Data.OleDb" and "Microsoft.Jet.OLEDB.4.0" which is ADO.

 

[countzero]

I have narrowed it down to the following:
1. New project (Windows Forms Application).
2. Double click form to generate Form1_Load event code
3. Paste the following code:

Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;")
cn.Open()

4. At the top of the page, add the following line:

Imports System.Data.OleDb

5. Put a new empty Access2003 file "C:\test.mdb" in place.
6. Compile and run. All works fine. The form appears on screen without errors.
7. Save project. Compile and run. "Unspecified error"

??

I'll build a new computer (Win7x86EntSP1 + VB .NET Studio Express 2010) and try again...grmbl...

 

[jmcilhinney]

If the Data Source wizard works then I'd suggest that the issue is your connection string. What connection string does the wizard generate? Try using that in your code and see if it works.

Is there any particular reason that you're not adding the database to your project?

In a Catch block you have access to the OleDbException object. As you can see from your own screen shots, that object has an Errors property, which is a collection. Like any collection, you can run a For Each loop over it to access each item. That's enumerating.

Data Source Wizard works, but I don't know how to use it to fill a Combobox1.Items in less than 5 lines of code. I don't like the Data Source Wizard as it obfuscates code and introduces complexity and -by a lack of a better description- 'GUI goop'. I can't easily integrate code variables/parameters into the query editor embedded in the the Data Source connection. Also, I can't set the location of the Data Source connection *.mdb file at runtime. If anything in my code would break in a next version of VB Studio or a conversion to Sharp Develop, it would be the Data Source connection wizard generated objects/code.

Basically none of that is true.

 

[countzero]

What connection string does the wizard generate? Try using that in your code and see if it works.

The connection string property generated by the data connection wizard contains:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source="C:\test.mdb"

It is identical to what is used in the custom code. I will rebuild this computer. Something's not right. Would have preferred to solve it rather than nuke it.

Basically none of that is true.

True:welcoming:

...but for your amusement:
1. Start a new Forms project
2. Run the data source wizard to create a connection to Ms Access file "test.mdb". This creates a testDataset.xsd file.
3. Add a ComboBox field to the form
4. Click on the drop down arrow for the ComboBox1.DataSource property and browse to the testDataSet object.
5. A testDataSetBindingSource object is created automatically.
6. I didn't really needed a binding; I don't need to update database records - I only once needed a single table field containing 10 records to fill 'ComboBox1.Items' collection.
But just for a test scenario, a binding would be a nice test I thought.
For some reason, Combobox1.Text field got populated with the text "System.Data.DataViewManagerListItemTypeDescripton" in the process. No idea why or what it means?
From here I couldn't get it to work, didn't understand what I was supposed to do, couldn't find any help in the documentation (MSDN online) and gave up.
7. I deleted the testDataSetBindingSource object and testDataset.xsd from the project expecting this would revert things back to step 3.
8. It didn't work that way; I got a number of compilation errors from code that was inserted automatically by Visual Studio and left behind(?)

To me, this classifies as code obfuscation and introducing complexity; fetching records from a single field>table>database (database location set at runtime) -and closing the connection immediately after records are fetched- to add to the ComboBox1.Items collection is (arguably) best done in a few lines of code rather than using a data source connection.

That's enumerating.

I know what it means...I don't believe a Try/Catch clause would give me any more information than the default VB Studio .NET debugger would allow me to explore. It is a bit disappointing if the debugger would not allow exploration/enumeration of the OleDbException object/errors. Try/Catch is what I would use when I want to present the end user with a simplified error message ('operation failed') or take corrective action ('exit sub') on a block of code. Not when I want to get to the bottom of things when debugging?

 

[countzero]

Either way, thanks for having me here & apologies for the rant. Am just frustrated...finding myself in a hole and seem to keep digging...

 

[Herman]

I know what it means...I don't believe a Try/Catch clause would give me any more information than the default VB Studio .NET debugger would allow me to explore. It is a bit disappointing if the debugger would not allow exploration/enumeration of the OleDbException object/errors. Try/Catch is what I would use when I want to present the end user with a simplified error message ('operation failed') or take corrective action ('exit sub') on a block of code. Not when I want to get to the bottom of things when debugging?

To the contrary, having try/catch blocks everywhere, and a single handling routine that pops up a messagebox with the exception text and stack trace is probably the best tool you can have to debug. Visual Studio does obfuscate some exceptions, considered non-fatal, by not breaking back to the debugger for some exceptions. Having the try/catch blocks gives you more information and shows you exactly where it fails.

Try

Catch ex As Exception
    ShowException(ex)
End Try

Private Sub ShowException(ex As Exception)
    MessageBox.Show(ex.Message & vbcrlf & ex.StackTrace)
End Sub

Start with this around every block and adapt from there on.

 

[countzero]

Without Catch/Try:

With Catch/Try:

I remain convinced Try/Catch is a nice thing to have, but offers no advantages over the built-in debugger. StackTrace and errors can be explored just fine. (although I have no clue how in my code I could descend into the OleDbConnection object class whilst debugging...hmmm...I remember something about a setting that affects step into/over behavior...will investigate how to descend into the bowels of OleDbConnection)

If anyone knows how to repair/reinstall a corrupt ADO/OleDB Windows component (and perhaps what the installer is called / where it can be found) and whether it is built-in to the .NET 3.5/4.0 framework or was part of VB Studio Express 2010 .NET or....

I am too stubborn to just move to another computer or nuke it. "If it works, take it apart and find out why" philosophy. (thanks for people's help sofar). To be honest, I'd say there is something fishy about this computer really as nobody seems to be able to reproduce the symptoms.

 

[jmcilhinney]

As I have said more than once, in a Catch block you have access to the OleDbException object, which means that you have access to the Errors collection of that object, which means that you can enumerate that collection, i.e. loop through it with a For Each loop, to examine each individual error item. That is something that you cannot do with the Exception Assistant. As I have also said though, I'm guessing that it won't give you any additional information. I shouldn't have to guess though, because I first said it two days ago so you should have already done it by now and know for sure.

 

[countzero]

Reinstalled Visual Basic Express 2010 including ADO .NET component. Problem persisted. Formatted hard disk and rebuilt computer. Problem can no longer be reproduced. All code examples compile without error. Case closed.