Solved

Access Database Table problem

Posted on 2001-07-12
15
309 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
Comment Utility
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
Comment Utility

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
Comment Utility
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
 
LVL 2

Expert Comment

by:GloriousRain
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 2

Accepted Solution

by:
GloriousRain earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
hi atsac,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 

Author Comment

by:atsac
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

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 …
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now