MS Access SQL query by column name not giving results

[Foxfire]

I am using CVI 7.1, SQL toolkit 2.0, Access 2003, all on WinXP, trying to read from a table using explicit SQL. When I do a "SELECT * FROM MYTABLE" I get the first record. But if I do a "SELECT COLUMN_1 FROM MYTABLE"The CVI example readtab2 works perfectly.

[Foxfire]

sorry, I accidently submitted the post before I was done.

I am using CVI 7.1, SQL toolkit 2.0, Access 2003, all on WinXP, trying to read from a table using explicit SQL (the database is registered for ODBC use with the OS). When I do a "SELECT * FROM MYTABLE" and bind COLUMN_1 with the result, I get the value of the first record column 1 as I should. But if I do a "SELECT COLUMN_1 FROM MYTABLE" I get a native error from Access -2146825023 (Item can not be found in the collection corresponding to the request name or ordinal). I know that column exists from the first query (plus the fact that I made COLUMN_1 myself). The table is very simple without any relationships to other tables. It has data.

Here's what I've tried:

I've tried any other explicit column name in my query and I get the same error. The CVI example readtab2 works perfectly with the shipping CVI database samples.mbd. I've searched Microsoft's KB, NI's KB, and this discussion forumn and am clueless what could be causing this.

Any ideas how to get past this?

[Foxfire]

Recreated the same basic database in Access97 and got the same error in CVI. So there is something wrong with how I'm setting up the database. Anyone run across this before?

[Jeremy_L]

Hi,

I have Access 2002 (XP) installed on my machine. I tried to reproduce your error with a simple database and explicit call like you did, but I was able to retrieve my data just fine. Can you post a small example of your database and source code so that I may try testing it on my system? It may be that something's corrupted with that particular database that you're accessing. Also, how are you getting access to the database, a file, user, or system DSN?

[Jeremy_L]

Try this code out as well to see if it makes any difference. I was able to retrieve data from my database without any problems.

[Foxfire]

I got the same error as before with your little test ("item can not be found in the collection..."). Obviously there is something wrong with how I am setting up the database.

Here's my code and my sample database and code.

[Foxfire]

I forgot to mention, I am trying to access this database using DSN. I have added it to the ODBC data source list using the ODBC admin tool in XP. I currently have it set up as a System DSN with the same properties as the shipping "CVI SQL 2.0 Samples" database. Based upon what you have said and what I have tried, it seems that I have either no set something up correctly in the database (i.e. there is some option somewhere that I have missed) or I have not configured it correctly in the ODBC data source admin. Can you tell me more specifically what you did to set up your test?

[Jeremy_L]

Ahh, I see the problem. Change this line of code in your application:

DBBindColChar (stmt, 2, 256, data, &status, "");

to

DBBindColChar (stmt, 1, 256, data, &status, "");

The reason you set the column number to 1 instead of 2 is that if you recall, you actually bind columns of the returned recordset, not the actual table. So, when you call DBActivateSQL with the query "SELECT LOCATION FROM SHOPPINGLIST", you are getting back a recordset of 1 column (LOCATION), and X rows. This means that you can only bind 1 column, which will be indexed as column 1 of the recordset. If you changed your query to be "SELECT * FROM SHOPPINGLIST", then you are returning a recordset of all columns in the table (ITEM, LOCATION, and STORAGE) and X rows. Then, you can bind column 2 (LOCATION) and essentially get the same as above. Hope this helps!!

[Foxfire]

Jeremy,

You're a stud. Thanks, man. It worked.