Solved

Access Database Table problem

Posted on 2001-07-12
15
315 Views
Last Modified: 2013-11-20
I have a CFormView project and I added an Access Database lookup dialog window.  I got it to work if I only have one table in my Database file.  Now if I expand to two tables in the same *.mdb file I get an exception error.  I used the class wizard to add the the new varaibles and table to the CRecordset Class.  What else do I need to do to modify to access two tables?  Can anyone help me.  I'm new at this database access programming so I might need some extra help. Thanks.

void CDBAccess::OnInitialUpdate()
{
     CFormView::OnInitialUpdate();
     CString os="ODBC;DSN=Db1;";
     m_MyDatabase.Open(NULL,FALSE,FALSE,os);
     pMyRecordSet=new CRecDB(&m_MyDatabase);
     pMyRecordSet->Open(); <--Exception occurs here
}

void CDBAccess::OnButtonDb()
{
    char buffer[40];
    m_edit_DB.GetWindowText(buffer,40);
    while(!pMyRecordSet->IsEOF()){
        .
        .
     pMyRecordSet->MoveNext();
    }
    pMyRecordSet->MoveFirst();
}
0
Comment
Question by:atsac
  • 5
  • 4
  • 4
  • +1
15 Comments
 
LVL 2

Expert Comment

by:GloriousRain
ID: 6279419
Hi,
What the exception said?
put pMyRecordSet->Open() in try, catch to see the error message
try
{
   pMyRecordSet->Open();
}
catch (CDBException &e)
{
   AfxMessageBox(e->m_strError);
}

0
 
LVL 3

Expert Comment

by:tdaoud
ID: 6282940

I am making ssome gusses here since not enough information regarding the error/exception hapenning.

But I would assume that if you somehow while adding the other table, you changed some or many of the fields of the original table which causes your original recordset to not find fields it is looking for that were originally there.  You might want to check the classwizard on the CRecordset of pMyRecordSet to check o nthe variables and redo an "update and bind columns" there to reflect the current database table structure.

Second, after adding the other table, did you create another recordset object in your program, or how do you plan on using the other table?

Good Luck,

Tarik
0
 

Author Comment

by:atsac
ID: 6286232
A dialog box pops up and says "Too few parameters. Expected 2."

Here's the CRecDB Class
CRecDB::CRecDB(CDatabase* pdb)
     : CRecordset(pdb)
{
     //{{AFX_FIELD_INIT(CRecDB)
     m_Area = _T("");
     m_IntersectionName = _T("");
     m_IntersectionNo = 0.0;
     m_ID2 = 0;
     m_Section = _T("");
     m_ID3 = 0;
     m_nFields = 6;
     //}}AFX_FIELD_INIT
     m_nDefaultType = snapshot;
}
.
.
.
.
void CRecDB::DoFieldExchange(CFieldExchange* pFX)
{
     //{{AFX_FIELD_MAP(CRecDB)
     pFX->SetFieldType(CFieldExchange::outputColumn);
     RFX_Text(pFX, _T("[Area]"), m_Area);
     RFX_Text(pFX, _T("[IntersectionName]"), m_IntersectionName);
     RFX_Double(pFX, _T("[IntersectionNo]"), m_IntersectionNo);
     RFX_Long(pFX, _T("[Intersections].[ID]"), m_ID2);
     RFX_Text(pFX, _T("[Section]"), m_Section);
     RFX_Long(pFX, _T("[Sections].[ID]"), m_ID3);
     //}}AFX_FIELD_MAP
}
In second DB table I have the Sections.ID (which is just an autonumber column) and the Section (which are names). The first DB table contains everything else.  When I add the second table, do I need to create another record object? or does it become part of the one I made for the first DB table?  I basically have two DB tables that contain similar data (they could be in one DB table but they're not.)  and I want to retrieve data from both tables.  Example:  I put in an autonumber say 2, and I want to get the Intersection Name & No.,Area,and the Section from the DB tables. Hope this helps you figure out what I'm trying to do.  Thanks.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Expert Comment

by:GloriousRain
ID: 6288240
Hi, let try
add this function in your CRecDB class
BOOL CRecDB::GetRecords(const CString& strSQLSelect)
{
     TRY
     {
          if (!Open(CRecordset::snapshot, LPCTSTR(strSQLSelect)))
               return FALSE;
     }
     CATCH(CDBException, e)
     {
          AfxMessageBox(e->m_strError);
          return FALSE;
     }
     END_CATCH
     return TRUE;
}

then
CString strSQLSelect; // specify your sql statement here
pMyRecordSet->GetRecords(strSQLSelect);
0
 
LVL 3

Expert Comment

by:tdaoud
ID: 6288958

Did you double check the field names in the table and in your recordset in the DoFieldExchange function?

Are all of them the same spelling and all exist?

Good Luck,

Tarik
0
 

Author Comment

by:atsac
ID: 6290561
void CDBAccess::OnInitialUpdate()
{
     CFormView::OnInitialUpdate();
     CString os="ODBC;DSN=Db1;";
     m_MyDatabase.Open(NULL,FALSE,FALSE,os);
     pMyRecordSet=new CRecDB(&m_MyDatabase);
    CString strSQLSelect; // specify your sql statement here
     strSQLSelect = "SELECT * FROM Intersections,Sections;";
    pMyRecordSet->GetRecords(strSQLSelect);
     pMyRecordSet->Open();
}

I used strSQLSelect = "SELECT * FROM Intersections,Sections;"  and I get the warning "Invalid character value for cast specification on column number 6 (Section) and then the error "Too few parameters. Expected 2."  My tables look like this:

Db1.mdb
Intersections : Table
ID   IntersectionNO    IntersectionName   Area
1    12.25             Street & Avenue    New York
2    12.26             Street1 & Avenue1  Houston

Sections : Table
ID   Section
1    A
2    B

I checked the spelling and variables and they exist.

0
 
LVL 3

Expert Comment

by:tdaoud
ID: 6290725

To make things a little easier, I can suggest that you create a query in Access that will combine whatever fields you want according to whatever relationships and criteria you want then build a recordset in MFC on that query (not on a table).

I think that will make it easier to bind the columns and make things clearer for MFC.

Good Luck,

Tarik
0
 
LVL 2

Accepted Solution

by:
GloriousRain earned 300 total points
ID: 6291982
first of all, you needn't call pMyRecordSet->Open(); because GetRecords has already called Open. (That you got 2 error message ) I mean use GetRecords insteads of Open.

second, modify
strSQLSelect = "SELECT * FROM Intersections,Sections;";
->strSQLSelect = "SELECT * FROM Intersections,Sections"; // omit ; at the end

void CDBAccess::OnInitialUpdate()
{
    CFormView::OnInitialUpdate();
    CString os="ODBC;DSN=Db1;";
    m_MyDatabase.Open(NULL,FALSE,FALSE,os);
    pMyRecordSet=new CRecDB(&m_MyDatabase);
   CString strSQLSelect; // specify your sql statement here
    strSQLSelect = "SELECT * FROM Intersections,Sections";  // omit ; at the end
   pMyRecordSet->GetRecords(strSQLSelect);
//    pMyRecordSet->Open();
}
0
 

Author Comment

by:atsac
ID: 6294990
Sorry about the last post.  I did remove the pMyrecordSet->Open() and fixed the SELECT*FROM line.  I still received the exception "Invalid character
value for cast specification on column number 6 (Section)"  when I combined the two tables into one query.  I used the update in class wizard and the bind to update the variables.  IS there something wrong with my DB tables or Query table?  When I use the query table I only get the exception error.  I assume something is wrong with my DB.  Any other suggestions?   When I query the two tables together do I include the autonumber column from both tables (intersections and sections) or just one?
0
 
LVL 2

Expert Comment

by:GloriousRain
ID: 6296623
there is no wrong with your DB. Modify your SQL statement like this
"SELECT * FROM Intersections,Sections WHERE Intersections.ID = Sections.ID". if it still not works create new class for both tables.
0
 
LVL 3

Expert Comment

by:tdaoud
ID: 6296768

In your query you should only select one of the autonumber and not both (since ther are the same, ... aren't they?)

In addition to any other fields from any of the tables that you would like to use in your program.

Good Luck,

Tarik
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6324092
I think you should not have tables that contain the & (ampersand) character.  

Try this:  Rename these database fields to StreetAndAve and StreetAndAve2 and make matching adjustments to your CRecordset-derived class.  See if the error goes away.

-- Dan
0
 

Author Comment

by:atsac
ID: 6385094
Sorry, been busy on another emergency project.  Back on this one again and I'm starting over.  I will try it again and see if it's works this time using your suggestions.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 6686575
hi atsac,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 

Author Comment

by:atsac
ID: 6721258
I finally got back to this project after having to work on another project.  Sorry it ook so long.  I got this part to work but now I'm having trouble accessing some of the data.  I will post another question if anyone can help me again.  Thanks for all the comments and help.
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
has22 challenge 11 105
tripleUp challenge 7 86
Problem to event 3 104
SQUD PROXY SERVER, UNIX, SLL/HTTPS 5 104
Introduction: Load and Save to file, Document-View interaction inside the SDI. Continuing from the second article about sudoku.   Open the project in visual studio. From the class view select CSudokuDoc and double click to open the header …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question