Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Access Database Table problem

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
atsac
Asked:
atsac
  • 5
  • 4
  • 4
  • +1
1 Solution
 
GloriousRainCommented:
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
 
tdaoudCommented:

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
 
atsacAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GloriousRainCommented:
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
 
tdaoudCommented:

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
 
atsacAuthor Commented:
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
 
tdaoudCommented:

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
 
GloriousRainCommented:
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
 
atsacAuthor Commented:
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
 
GloriousRainCommented:
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
 
tdaoudCommented:

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
 
DanRollinsCommented:
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
 
atsacAuthor Commented:
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
 
DanRollinsCommented:
hi atsac,
Do you have any additional questions?  Do any comments need clarification?

-- Dan
0
 
atsacAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now