Skip to main content

[archive] AcuODBC and ADO.NET ODBC provider problem

  • October 11, 2006
  • 6 replies
  • 0 views
D

[Migrated content. Thread originally posted on 10 August 2004]

Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.

    6 replies

    D

    [Migrated content. Thread originally posted on 10 August 2004]

    Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
    However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
    I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
    Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
    Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
    Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.
    Hi Dan,
    I've read your message, and it was over my head, however, it seems that you know quite a bit about AcuODBC and .NET, therefore I would like to get some help from you if you don't mind.
    I am trying to use ASP.NET to access my VISION data files on UNIX host through AcuODBC. This is what I done:
    - Set up system DSN using AcuODBC driver.
    - Use MS Access to link and test it, it worked.
    - Run Visual Web Developer 2005 Express Edition.
    - Use Database Explorer to add data connection.
    - Select Microsoft ODBC Data Source with .NET Framework Data Provider
    - Select system DSN (AcuODBC) from ODBC control list.
    - Test connection, it worked.
    - However, when I click on TABLE folder under ODBC connection, I got this error: Column 'TABLE_CAT' does not belong to table Tables

    Your help would be greatly appreciated.

    Thanks,

    Quang Le
    Riverside County Sheriff
    D

    [Migrated content. Thread originally posted on 10 August 2004]

    Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
    However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
    I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
    Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
    Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
    Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.
    Quang, are you attempting to you use a linked table in Access because the database explorer in Visual Web Developer 2005 Express Edition does not support ODBC? Although this may sound like a good work around at first, I would avoid attempting to use an ODBC linked table via Access. This can be rather unstable as you can see. My experience is that the schema information appears to either not be retrieved correctly or there are mis-matches when any version of the Visual Studio database explorer is involved.

    My suggestion is that you connect and query the AcuODBC data source directly from your own code with the OdbcConnection and OdbcDataAdapter(or OdbcDataReader) classes in the System.Data.Odbc namespace. There are samples in the .NET Framework Class Library Reference documentation.
    D

    [Migrated content. Thread originally posted on 10 August 2004]

    Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
    However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
    I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
    Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
    Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
    Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.
    Hi Dan,

    Thank you for your response. I agreed with you 100% about NOT using MS Access link database, however, I have a problem of using ODBC directly:

    - Run Visual Web Developer 2005 Express Edition.
    - Use Database Explorer to add data connection.
    - Select Microsoft ODBC Data Source with .NET Framework Data Provider
    - Select system DSN (AcuODBC) from ODBC control list.
    - However, when I click on TABLE folder under ODBC connection, I got this error: "Column 'TABLE_CAT' does not belong to table Tables"

    Unable to access to this table folder, I can not drag any table to the design surface therefore VWD 2005 CAN NOT create Gridview and OdbcDataSource controls automatically.

    Your step by step instruction would be greatly appreciated.

    Thank you.

    Quang V. Le
    951-955-8336
    D

    [Migrated content. Thread originally posted on 10 August 2004]

    Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
    However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
    I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
    Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
    Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
    Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.
    I get the same result as you when I try it. Which version of AcuODBC are you using? I'm using 6.1. If you have the most recent version(7.2) and you are still having the problem, then you need to call acucorp support.
    D

    [Migrated content. Thread originally posted on 10 August 2004]

    Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
    However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
    I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
    Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
    Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
    Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.
    Thanks Dan,

    I am using AcuODBC version 6.2, and I have called acucorp tech support on this. They told me to call Microsoft or post it on the forum. I know that if I call Microsoft, they will tell me to call acucorp since AcuODBC belongs to acucorp.
    So I am stuck, however I will install 7.2 to try it, but I don't have much hope on it either.

    Let me know if you find any solution beside using MS Access link.

    Quang
    D

    [Migrated content. Thread originally posted on 10 August 2004]

    Reading data via AcuODBC from the ADO.NET ODBC data provider works fine, and acucorp has a VB.NET example of this in their downloads area.
    However, there are problems when trying to implement more advanced features of the OdbcDataAdapter to allow dataset/datatable updates to persist back to the AcuODBC datasource. For instance in Visual Studio.NET 2003, when you drop an OdbcDataAdapter component onto your form, a handy wizard appears allowing you to specify your connection object, and to specify your select query, which in turn will generate four command objects for you: select, insert, update, and delete command objects. This is very helpful. The problem is that the wizard seems to be unable to retrieve the schema from the AcuODBC driver correctly and the paramater collections for the command objects cannot be created, which appears to stop the insert, update and delete command objects from rendering. The wizard actually informs you of this with warning icons at the end.
    I'm thinking it may have to do with being unable to determine the primary key, but I'm not certain. I've triend workarounds like explictly setting the primary key column in the datatable, creating my own paramater collections, and using a CommandBuilder object, etc, but to no avail. I still get a system exception whenever I run the Update method of my OdbcDataAdapter object. The stack trace indicates a null reference for either the DataMappingTable or the DataRows array, but they appear to exist, so I'm lost.
    Also, when you use the Server Explorer in VS.NET 2003, and open a connection to your AcuODBC data source and try to drill down into the field list for a table, VS.NET crashes. Yikes! This does not happen for an ODBC Access database or ODBC MS SQL database.
    Another curious thing is that when I use an OdbcDataReader to read data from my AcuODBC datasource and then use the GetSchemaTable method, I get the schema just fine. So, the VS.NET wizard and the underlying .NEt framework Data classes must be having issues beyond just the schema, and that's why I mention perhaps the primary key issue.
    Can anyone confirm these findings? Have you found a workaround? I've tried this with both AcuODBC 6.1 and 6.2 beta read/write versions. Any and all thoughts are welcome. In the meantime, my workaround is to simply use plain text SQL queries for all my selects, inserts, updates, and deletes, which is unfortunately not taking full advantage of what the DataAdapter can really do with dataset/datatable persistence.
    I came across this: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=123248

    You may want to discuss it with Acucorp support. Hopefully, they can pass this issue along to development. Apparently, this problem happens with ODBC drivers that don't fully implement the ODBC 3.0 SQLInfo methods. This appears to be the case with the AcuODBC 6.x drivers. I don't know about the 7.x drivers.

    You might have to use an Access linked table for the time being. :(

    Recent badge winners

    Explore all badges
    Terms & ConditionsAccessibility statement