DAO question.......

I am doing a simple DAO query that selects all items from two columns in a table. I have set up the Dao Recordset stuff in my app. However when I try to open the recordset in the app , it crashes out with message "Item not found in this collection"...

I don't fully understand the steps involved with dao however all I need to know is how to open a recordset and corectly store the returned info. also I need to handle exceptions..

Any pointers or sample code would be appreciated..

Tanks

LK--<
LizardKingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SadhuCommented:
It looks like that you are not getting correct record set.

Please post some code

Sadhu
0
V_BapatCommented:
Possible causes for the error you are getting:
1· The object doesn't exist in this collection. Make sure the object is appended to a collection before referencing it.
2· There is more than one object with this name in the collection; using its name is an ambiguous reference. Reference the object by its ordinal position in the collection (for example, Recordsets(3)).

Steps involved in a typical DAO application:
1. Create a workspace. A workspace can have one or more database. However, if you do not create a workspace, a default workspace is created for you. Usually, it is not created.
2. Create/Open a database. A database can contain zero or more recordsets/tabledefs/querydefs.

To manipulate data in the tables, you need  a recordset object. How to open a recordset ?

CDaoDatabase db(/*.MDB filename*/);
CDaoRecordset rs(&db);

rs.Open(...);
The parameters in Open typically are, recordset type and a SQL query. Read the help about the recordset types(snapshot/dynaset/table type). For SQL help, search for jetsql35.hlp(if I remember correctly) in your PC. This file is usually in %WinDir%\help.
For example, if you have a table(say table1) in the database, with fields Id, Name, City and you want to read all the records, do this:
CDaoRecordset rs(&db);
rs.Open(dbOpenDynaset, "SELECT * FROM table1");
while(!rs.IsEOF())
{
   /* read the field values*/
   rs.MoveNext();
}
rs.Close();

If you want to read only Name and City, open the recordset as:
rs.Open(dbOpenDynaset, "SELECT Name, City FROM table1");

Exception handling:
try
{
   rs.Open(...);  // for example
}
catch (CDaoException* e)
{
   /* Handle your exception here. You can either display an error message or ignore it or do whatever you want
   */
   e->delete();
}

If an exception occurs in the try block, the execution will go to the catch block where you can handle it. For more information read the online documentation.

Good samples: Daotable and Daoview in MSDN

If you have more queries, feel free to ask.

All the best.
Vicky
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LizardKingAuthor Commented:
Vicky

I tried to use CDaoDatabase db(/*My .MDB Database Address*/)

But I keep getting this error

D:\billing\MultiView.cpp(126) : error C2664: 'CDaoDatabase::CDaoDatabase' : cannot convert parameter 1 from 'char [39]' to 'class CDaoWorkspace *'
        Types pointed to are unrelated; conversion requires reinterpret_cast, C-style cast or function-style cast

Also Sadhu

I don't think I have enough code written for it to be helpful to post on the site..... all I have done is added A class to my program..(Not from AppWizard but ClassWizard... then derived this class from CDaoRecordset and picked a sample MDB then , I changed the Hardcoded .MDB filename stored in the code to a value which I can Get on starting The application..

I am just having trouble opening the recordset, adding to it and storing values in an Array of some sort.

I have values for the fields I need set up in the DDX correctly so I just need to read the recordset into an array

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

V_BapatCommented:
Ooops ! I am sorry. You have to give the file name while opening the database.

CDaoDatabase db;
db.Open(/* MDB filename*/);

Vicky
0
SadhuCommented:
LizardKing,

Basically there are two ways you can display data from qry.
-- derive your own recordset class from CDaoRecordset. Following link is a sample very easy to understand example
http://www.codeguru.com/mfc_database/DialogDb.shtml

---Dynamically binding your recordset to display like ListCtrl. See example DAOVIEW in msdn CD or Link below.

http://msdn.microsoft.com/library/devprods/vs6/visualc/vcsample/_sample_mfc_daoview.htm

I hope this will help.
Sadhu
0
LizardKingAuthor Commented:
Thanks to Vicky and Sadhu

Just one more thing...

I can now get at the correct information in my database. I want to now display all this information in a ListBox on a FormView dialog.

I am now having problems with the listbox .AddString function ...

What are the correct steps involved in setting up a list box with dynamic data

Thanx Again

LK--<
0
SadhuCommented:
This code is not compiled.
Download sample from link(Microsoft) I mentioned earlier. it has a class called CCrack
a function strVARIANT takes variant and returns string. You can write something like
that for your own use. I just showed for example. If you have daorecordset as pointer
then use rs->

try
{
   
    // Move through records
    while( !rs.IsEOF( ) )
    {
        COleVariant var;
        // Move through fields in current record
        int nFields = rs.GetFieldCount( );
      CString strTemp;
        for ( int i=0; i < nFields; i++ )
        {
            var = rs.GetFieldValue( i );
            // Add field value to list control
          if(i==0)
            strTemp=CCrack::strVARIANT( var );
          else
            strTemp+=CCrack::strVARIANT( var )
           
        }
        nRecord++;
        rs.MoveNext( );
    }
}
catch(CDaoException* e )
{....
      // your code to catch exception
}
Hope this helps
0
SadhuCommented:
sorry I forgot a line to add string into list box
try
{
     
    // Move through records
    while( !rs.IsEOF( ) )
    {
        COleVariant var;
        // Move through fields in current record
        int nFields = rs.GetFieldCount( );
CString strTemp;
        for ( int i=0; i < nFields; i++ )
        {
            var = rs.GetFieldValue( i );
            // Add field value to list control
    if(i==0)
    {
     strTemp=CCrack::strVARIANT( var );
     strTemp+='\t';
    }
    else
    {
     strTemp+=CCrack::strVARIANT( var );
     strTemp+='\t';
    }
             
        }
        m_ctrlListBox.InsertString(strTemp);
        nRecord++;
        rs.MoveNext( );
    }
}
catch(CDaoException* e )
{....
// your code to catch exception
}
Hope this helps

0
V_BapatCommented:
There are two ways to communicate with the database:
1. DAO Record Field Exchange(RFX) method.
2. Dynamic binding.
Read technical note 53 (TN053) in MSDN help.

Sadhu has used the second one. That is because we have made rs an object of CDaoRecordset.

You might consider the first method(which I personally prefer). For this, derive your class(say CMyRecSet) from CDaoRecordset. You will be prompted to select the database file and the table for which you are creating the recordset. This will give you member variable for each field of your table. Coming back to my earlier example, of table1 with fields Id, Name, City, you will get member variables m_Id, m_Name, m_City. You can use them directly as you would use any other variable.

Do you want to insert them in a ListBox or ListCtrl? Suppose you want to insert names into ListBox (m_listbox),

CMyRecSet rs(&db);
rs.Open(dbOpenDynaset, "SELECT Name FROM table1");

while(!rs.IsEOF())
{
   m_listbox.AddString(m_Name);
   rs.MoveNext();
}

rs.Close();

If you want to display all the fields, you have to insert a CListCtrl instead of CListBox in your FormView.

Vicky
0
LizardKingAuthor Commented:
Thanks for the Help from the both of ye
Vicky can have the points seen as she proposed an answer...

Sadhu if I could I would give you 25 also

Thanx lots

LK----------<

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.