Accessing multiple recordsets

[Zulfi Khan]

I have a database which contains three tables Books, Members & Transactions. The Transaction table contains BookID & MemberID as foreign keys. The books and Members have 1 to many relationships with Transactions table. I want to access the Member Namme which is the field of Members Table. While creating the project in the datasource I have just associated my program with the Transactions table. How I can solve this problem?

[d_wzrdv_z]

Don't use the appwizard - just create some CDaoRecordSet objects (and a CDaoDatabase object to define the datasource for them) then go for your life! Mail me if you have any problems. I assume you're using VC++; whether you're using DAO or the plain vanilla MFC ODBC classes it will be simple enough.
Cheers,
dWz


\_|_|_|_/
oOOOOOo
(*^@ @^*)
| O |
\&_&/
__-__
/?;|;?\

D_WZRDV_Z

[Steve McNeese]

Are you using CDAORecordset or just CRecordset? You can still use the Class Wizard to created your recordssets for your table(s). It all depends on how you want to access the data. Unfortunately, the wizard only lets you select one table, so making a joined record set takes some mods to the derrived CRecordset class. There are samples of this on the MSDN CD. I have run into problems creating joined tables with a CRecordset that seems to be a limitation of the number of columns...does not make sense. When you have tables with many columns or multiple tables you want to join, the best way is to create a view directly in the DBMS. This dumps all of the work associated with the join on the server not your app. For you, either way would work.

Later...

Steven M. McNeese
steven.mcneese@boeing.com

[Zulfi Khan]

Thanks for your attention. I was looking at the material for accessing multiple recordsets. In this regard I found "Inside Visual C++" very helpful. We can access the *Set class by wizard & then we can update columns to choose a new table. It would then add all the fields of that column.We can then make changes in GetDefaultSQL routine of *Set class.It works fine.
I am facing two problems now:
1) I got trouble in handling fields having same name in both tables. I made changes in DoFieldExchange but its not accepting both the fileds. How I can get around with this problem??
2)I have two tables Books & Transaction. The Acc_No is the primary key in the books Table but Acc__No (by chance spelling is different) is a foreign key in Transaction table. IfI dont use multiple Tables then I can get the Acc_No while displaying the Transaction table. But now I have Updated the Columns so I want to get the m_Title which is a field of Books table.
I used the following expression for filter i.e
m_strFilter = "[Books].[m_Acc__No] == [Transaction].[m_Acc_No]" but I am getting the following syntax error:
Syntax error (missing operator) in query expression 'Books'.'m_Acc__No' == 'Transaction'.'m_Acc_No'
How can I get around with this problem??

Zulfi

[Zulfi Khan]

Thanks for your attention. I was looking at the material for accessing multiple recordsets. In this regard I found "Inside Visual C++" very helpful. We can access the *Set class by wizard & then we can update columns to choose a new table. It would then add all the fields of that column.We can then make changes in GetDefaultSQL routine of *Set class.It works fine.
I am facing two problems now:
1) I got trouble in handling fields having same name in both tables. I made changes in DoFieldExchange but its not accepting both the fileds. How I can get around with this problem??
2)I have two tables Books & Transaction. The Acc_No is the primary key in the books Table but Acc__No (by chance spelling is different) is a foreign key in Transaction table. IfI dont use multiple Tables then I can get the Acc_No while displaying the Transaction table. But now I have Updated the Columns so I want to get the m_Title which is a field of Books table.
I used the following expression for filter i.e
m_strFilter = "[Books].[m_Acc__No] == [Transaction].[m_Acc_No]" but I am getting the following syntax error:
Syntax error (missing operator) in query expression 'Books'.'m_Acc__No' == 'Transaction'.'m_Acc_No'
How can I get around with this problem??

Zulfi