DAO Fails when ignoring field?
Poul A. Costinsky -- Poul@wizsoft.com
Monday, September 16, 1996
Environment: Win NT 3.51/4 /Win95 VC++ 4.1
Hi people here!
I have a strange problem with MFC DAO.
I have two tables: Table1 with fields "a" and "b"
and Table2 with "a" and "bb" (don't be shocked,
I just try to downsize the problem -:),
where "a" field must be joined.
I've asked ClassWizard to create the app
and it worked fine.
Then I changed GetDefaultSQL to the following:
SELECT [b],[Table2].[a],[bb] FROM [Table1], [Table2] WHERE [Table1].[a] =
[Table2].[a]
Then I deleted 1st DFX for field [Table1].[a] and changed m_nFields to 3.
(I.e. I've excluded first field from SELECT and DFX)
The CDaoRecordset::Open fails in
with following message:
"GetRows failed. The requested column is not a member
of this recordset".
It calls m_pICDAORecordsetGetRows->GetRows(...
and scode is -2147217406.
The excluding of a filed from single table works fine.
What's a problem and/or workaround?
I cannot afford to hold spare fields because in reality there are
many, many of them.
Thanx,
Poul.
("`-''-/").___..--''"`-._ ~~~~~~~~~~Poul A. Costinsky~~~~~~~~~~
(`6_ 6 ) `-. ( ).`-.__.`) Poul@wizsoft.com
(_Y_.)' ._ ) `._ `. ``-..-' PoulACost@msn.com
_..`--'_..-_/ /--'_.' ,'
(il).-'' (li).' ((!.- http://www1.wizsoft.com/~Poul
==========Disclaimer:All my words are my own.==========
Jim Leavitt -- jimll@halcyon.com
Tuesday, September 17, 1996
[Mini-digest: 3 responses]
Poul:
You wrote...
----------
Then I changed GetDefaultSQL to the following:
SELECT [b],[Table2].[a],[bb] FROM [Table1], [Table2] WHERE [Table1].[a] =
[Table2].[a]
Then I deleted 1st DFX for field [Table1].[a] and changed m_nFields to 3.
(I.e. I've excluded first field from SELECT and DFX)
The CDaoRecordset::Open fails in
with following message:
"GetRows failed. The requested column is not a member
of this recordset".
1. I've found that its best to work with both Access and msdev when using dao.
If you get the query right in Access, it will always work with the dao classes.
2. Looks like you're trying to do an inner join, ie select only the records where both
fields are equal. I suspect your SQL statement may be in error...
You could try this SQL Statement...
SELECT DISTINCTROW [table 1].[b], [table 2].[a], [table 2].[bb]
FROM [table 1] INNER JOIN [table 2] ON [table 1].[a] = [table 2].[a];
Or you could fire up Access, create your query, test your result, then copy the
sql statement back to your program, really, that IS the easiest way.
Jim Leavitt
-----From: "Steve Barta"
I think you need to give the full table.field description in your SELECT
statement such as:
SELECT [Table1].[b], [Table2].[a], [Table2].[bb]
FROM [Table1], [Table2]
WHERE [Table1].[a] =[Table2].[a]
-----From: "Ray Frohnhoefer"
Poul,
Perhaps if this is only a problem with the join field, you can keep that
one extra field in as a reasonable workaround. If there are many spare
fields, have you normalized the design of the database?
Regards,
Ray F.
Gott wuerfelt nicht.
- Albert Einstein
----------------------------------------------------------------------------
* Ray Frohnhoefer email: rayf@datatree.com, Frohnzie@aol.com
* DataTree Corporation web: http://www.datatree.com
* 550 West C Street, Ste. 2040 phone: (619) 231-3300 x131
* San Diego, CA 92101 fax: (619) 231-3301
*
DevTrain@aol.com
Friday, September 20, 1996
Environment: VC++ 4.2, Access 7, Win95
Works ok for me in 4.2 with your exact SQL statement (which is fine by the
way).
What are your remaining DFX statements? I can simulate your error if I
include the table qualifier for the join field. For example:
DFX_Text(pFX, _T("[a]"), m_a) works
while
DFX_Text(pFX, _T("table2.a"), m_a) does not
(nor does [table2.a] or [table2].[a]).
Poul A. Costinsky -- Poul@wizsoft.com
Tuesday, September 24, 1996
[Mini-digest: 2 responses]
The statement is
DFX_Text(pFX, _T("table2.a"), m_a)
because both table1 and table2 has field a.
Regards,
Poul.
("`-''-/").___..--''"`-._ ~~~~~~~~~~Poul A. Costinsky~~~~~~~~~~
(`6_ 6 ) `-. ( ).`-.__.`) Poul@wizsoft.com
(_Y_.)' ._ ) `._ `. ``-..-' PoulACost@msn.com
_..`--'_..-_/ /--'_.' ,'
(il).-'' (li).' ((!.- http://www.wizsoft.com/~Poul
==========Disclaimer:All my words are my own.==========
----------
> From: DevTrain@aol.com
> To: mfc-l@netcom.com
> Subject: Re: DAO Fails when ignoring field?
> Date: Friday, September 20, 1996 6:35 PM
>
> Environment: VC++ 4.2, Access 7, Win95
>
> Works ok for me in 4.2 with your exact SQL statement (which is fine by
the
> way).
>
> What are your remaining DFX statements? I can simulate your error if I
> include the table qualifier for the join field. For example:
>
> DFX_Text(pFX, _T("[a]"), m_a) works
> while
> DFX_Text(pFX, _T("table2.a"), m_a) does not
>
> (nor does [table2.a] or [table2].[a]).
>
-----From: "Poul A. Costinsky"
Hi folks!
My question isn't about database design, because I have to
be able to investigate any database - my app is a data mining
program.
> From: Jim Leavitt
> You could try this SQL Statement...
> SELECT DISTINCTROW [table 1].[b], [table 2].[a], [table 2].[bb]
> FROM [table 1] INNER JOIN [table 2] ON [table 1].[a] = [table 2].[a];
> Or you could fire up Access, create your query, test your result, then
copy the
> sql statement back to your program, really, that IS the easiest way.
This fails too.
> -----From: "Steve Barta"
>
> I think you need to give the full table.field description in your SELECT
> statement such as:
>
> SELECT [Table1].[b], [Table2].[a], [Table2].[bb]
> FROM [Table1], [Table2]
> WHERE [Table1].[a] =[Table2].[a]
This fails too.
> -----From: "Ray Frohnhoefer"
> Perhaps if this is only a problem with the join field, you can keep that
> one extra field in as a reasonable workaround. If there are many spare
> fields, have you normalized the design of the database?
In the most cases there are ID fields which join the relational tables.
Thanx.
Regards,
Poul.
("`-''-/").___..--''"`-._ ~~~~~~~~~~Poul A. Costinsky~~~~~~~~~~
(`6_ 6 ) `-. ( ).`-.__.`) Poul@wizsoft.com
(_Y_.)' ._ ) `._ `. ``-..-' PoulACost@msn.com
_..`--'_..-_/ /--'_.' ,'
(il).-'' (li).' ((!.- http://www.wizsoft.com/~Poul
==========Disclaimer:All my words are my own.==========
DevTrain@aol.com
Wednesday, September 25, 1996
The problem here is that the second argument to DFX_Text does not refer to
columns in the underlying tables used in your query, but instead to the
columns in the resulting recordset.
Thus your query:
SELECT [b],[Table2].[a],[bb] FROM [Table1], [Table2]
WHERE [Table1].[a] = [Table2].[a]
results in a recordset whose column names are "b", "a" and "bb".
Hence one correct syntax is: DFX_Text(pFX, _T("a"), m_a)
but not: DFX_Text(pFX, _T("table2.a"), m_a).
Of course the SQL "AS" qualifier can be used in a SELECT statement to change
the names of the recordset columns should you wish to. For example, "SELECT
a AS col1 FROM table1".
For some rather poor documentation, see the Visual C++ doc: "Programming with
MFC" (volume 2); topic: "DAO Record Field Exchange (DFX)"; subtopic: "DFX
Function Syntax". Page 281 in the Version 4.0 doc. Same basic info can also
be found under RFX and applies to ODBC.
| Вернуться в корень Архива
|