"Native error code -2147217887" When Accessing a Sheet in an Excel 2000 .xls File with LabWindows/CVI SQL Toolkit Browser2.prj ProjectPrimary Software: LabWindows/CVI Add-ons>>Enterprise Connectivity ToolsetPrimary Software Version: 1.1 Primary Software Fixed Version: N/A Secondary Software: N/A
Problem: When using the Browser2.prj project that ships with the LabWindows/CVI SQL Toolkit to view the records of an Excel 2000 .xls file, I get a "Native error code -2147217887" error after selecting the specific sheet to perform SQL statements on (A sheet is represented in the list as "$sheetname"). Solution: If you put a breakpoint at the line of code where this error is being generated and then look at the variable's "buffer" value which contains the string for the SELECT statement, you will see that it would look something like this: -> SELECT * from sheet1$ <- assuming that we have a sheet named sheet1 in our .xls file. This syntax works fine for Excel 4.0, but for Excel 2000 you must put double quotes around the sheet name like this: -> SELECT * from "sheet1$" <- These quotes are required by any ODBC driver that works with Excel 2000. Now, you may be wondering if this means that whoever created the Browser example didn't realize this and messed up somehow, and the answer is definitely NO. What the browser does is ask the ODBC Administrator in Windows what available User Data Source Names there are available on the system and displays them in that first window where you were selecting "Excel file", it then queries that data source name for locations of tables to work with and lists the Excel files you have associated with that DSN to choose from. After you select the file you want to work with it then asks you which particular sheet in the .xls file you want to query and after selecting one it immediately issues the sql statement mentioned above to find all of the fields listed in that sheet to work with, but since it has an improper SELECT statement that the driver is trying to execute, the driver then creates this native error code telling you that you cannot select a sheet name in that manner. Since Excel has this special requirement for selecting table names, our "generic" browser which constructs select statements that work with most other tables cannot know ahead of time that you are going to select a provider that has a special way of doing things. The main thing to know here is that you WILL have to know the exact SQL syntax your database provider supports. To make the suspect SQL statement correct in the Browser2.prj project you can try replacing the line that generates the SELECT statement to be put into the "buffer" variable in the Browser2.c file with the following line so that those double quotes are there for Excel 2000 to work, but remember to change it back if you want to work with other databases later (dBase, Access, etc...): sprintf(buffer, "select * from \"%s\"", aTable); A modified version of the Browser2.prj project that implements this change is attached to this document. Here is a link to the Microsoft website that contains the exact information stated about this issue: http://support.microsoft.com/support/kb/articles/Q130/1/37.asp **Note: There may be very similar things that cause the Browser2.prj project to not work with other database providers as well. The first thing you should check is that all of the SQL statements are compatible with the SQL syntax of the database, then check to see if the datatypes and properties that are being set on your particular database are compatible as well. These are generally the main sources of all "Native error codes" that you will encounter when using the SQL Toolkit. Related Links: Attachments:
Report Date: 08/03/2001 Last Updated: 12/07/2004 Document ID: 2C2BTTEV |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
