Problem with SQL statement to open derived CDAORecordset

Problem with SQL statement to open derived CDAORecordset

Post by Donald R. Thoma » Sat, 14 Nov 1998 04:00:00

I have a class, CBVRecordSet, derived from CDaoRecordset.  This class was
created from a table in an Access database. The table has these fields:

Basis Vector ID
Atom Name
Base Material Name

Now, when I try to open a recordset with the following statements:

CString sqlStatement;
sqlStatement.Format ("SELECT DISTINCT [Atom Name] FROM [BasisVector] "
        "WHERE [Base Material Name] = \'%s\' ORDER BY [Atom Name]", bmName);
BasisVectorRecordSet bvRc;
bvRc.Open (dbOpenDynaset, LPCTSTR (sqlStatement));

I get an error stating "GetRows failed. The requested column is not a member
of this recordset."

What is really funny is if I use the same SQL statement in a query in
Access, substituting a valid bmName, the query completes correctly, without
any error.

I suspect that there is something specifically wrong in my syntax.  The
reason I say this is because if I change the syntax to:
  "SELECT * FROM [BasisVector] WHERE [Base Material Name] = \'%s\' ORDER BY
[Atom Name]"
I don't get the error message.

Does anyone know what is going on here?  What am I doing wrong?  Thanks in
advance for any suggestions.

Don Thomas


Problem with SQL statement to open derived CDAORecordset

Post by Frank Hickma » Tue, 17 Nov 1998 04:00:00

Hi Donald,

One problem with specifying the SQL select statement to a recordset derived
from CDaoRecordset is the recordset contains all the columns in the table
and your select statement may miss or omit some.  When a recordset is
derived from CDaoRecordset and an implicit select statement is used, it must
contain all the column name in it.

Try this instead:

BasisVectorRecordSet bvRc;
bvRc.m_strFilter.Format( _T("[Base Material Name] = \'%s\'"), bmName );
bvRc.m_strSort= _T("[Atom Name]");
bvRc.Open( dbOpenDynaset );

Frank Hickman
NobleSoft, Inc.



1. Classes derived from CRecordset and CDaoRecordset


I created some classes in VC++ to help solving the following problem.

Suppose we have three related tables: Customers, Orders, Order Details.
Let's imagine, for example, that a customer makes a new order to request
some products at certain conditions. In a hypothetic application we are
projecting, we have decided that, after having selected the customer (that
we suppose for simplicity already in the Database), we must click the button
New order, which opens a form to be filled in with the order data. In the
form, a button New Detail is needed, in order to add, one at a time, the
different details of this order. After having added all the details and
filled in the form, we can save everything clicking on the button Save
Such a project seems to be quite reasonable, but at the implementation time
we would meet with a difficulty: if the details are saved in the Database
only when the order is saved, where should they be memorized in the
It is evident that we need to define an auxiliary structure where the
details data can be temporarily memorized, and where to pick out when the
order is saved in the Database.
The problem would become more complicate if we decided, for example, to save
more than one order at a time. In this case we should move the button Save
Order at the customer's level, transforming it in a button Save Orders in
charge of saving in the Database the N newly inserted orders. This time a
two level auxiliary structure would be necessary, in order to temporarily
memorize each order with the corresponding details. More levels we want to
unify, more complex the management of the needed auxiliary structure would

I published something about it (, and I'd
like to know the opinion of anybody about them, and about the discussed
matter. There you can also find a sample about how the classes work, so if
you are able to run it, please let me know (OK, even if you are not


2. ADO vs DAO; Opinions?

3. Leaving a CDaoRecordset object as a derived class member

4. Problem at the start. Help Meeeee!!!!

5. HELP!!: CDaoRecordset::Open problem

6. Binding to timestamp column using string data

7. if statement not allowed in derived table statement??

8. derived tables in from clause of select statement

9. Using SELECT in CDaoRecordset::Open()

10. Fast way to open a cdaorecordset?

11. Error while opening many CDaoRecordset

12. New CDaoRecordSet opens in read-only mode...why?