MFC insert into access db (CRecordset)

This is a discussion on MFC insert into access db (CRecordset) within the Windows Programming forums, part of the Platform Specific Boards category; I'm connection to an access database (Access 2000) through ODBC. (code came from this board) I can do a select ...

  1. #1
    Banned maes's Avatar
    Join Date
    Aug 2001
    Posts
    744

    MFC insert into access db (CRecordset)

    I'm connection to an access database (Access 2000) through ODBC. (code came from this board)
    I can do a select query on the database, but when I replace the select with an insert, I get assertion errors.
    This thing is driving me nuts for the past days.
    The connection with the db works because of the select statements are working just fine.

    I did not create my own version of CRecordset, I don't know if I should? I saw some tutorials where they create a classe that inherits from CRecordset, but I never got that to work properly.

    here's some code:
    g_mydb is my database (a global CDatabase)
    the first query works, the second gives assertion failures.
    Code:
    CRecordset rs(&g_mydb);
    
    strSQL.Format( "SELECT COUNT(nr) FROM woorden WHERE duits='"+m_german+"'" );
    rs.Open( CRecordset::forwardOnly, strSQL ); 
    rs.GetFieldValue( (short)0, strField );
    rs.Close();
    
    strSQL.Format( "INSERT INTO woorden ( duits, nederlands, geslachtduits, geslachtned, hoofdstuk ) 
    VALUES (\"Shnee\", \" sneeuw\", \" 1\", \" 1\", \" 2\");");
    
    rs.Open( CRecordset::dynamic, strSQL ); 
    //rs.GetFieldValue( (short)0, strField );
    rs.Close();
    does it has something to do with the way you open the recordset? (forwardOnly etc.)
    I tryed all of them and none worked

    Thanks

    Maes
    SVG is the future

  2. #2
    Registered User Codeplug's Avatar
    Join Date
    Mar 2003
    Posts
    4,607

  3. #3
    Banned maes's Avatar
    Join Date
    Aug 2001
    Posts
    744
    yeah, I read and tryed it, but it was one of those things that I couldn't get to work . That was the reason why I just used the recordset class in it's original form.
    But what I don't get is, why does the select work and not the insert. If one statement works, the others should too, doesn't it?

    Anyway, I'll try again with the msdn method.

    Thx Codeplug
    SVG is the future

  4. #4
    Yes, my avatar is stolen anonytmouse's Avatar
    Join Date
    Dec 2002
    Posts
    2,544
    I haven't used CRecordset or CDatabase, but I would have thought that if you just want to execute an SQL statement that doesn't return records you would use CDatabase.ExecuteSQL

    But what I don't get is, why does the select work and not the insert.

    The select returns records, the insert does not.

  5. #5
    Registered User Codeplug's Avatar
    Join Date
    Mar 2003
    Posts
    4,607
    CRecordset::Open() expects a SELECT or CALL statement only.
    You can only use a CRecordset object for reading. You have do derive from CRecordset for editting and inserting.
    You might want to download some of the samples on this page.

    Thanks anonytmouse, I was looking for that one.

    gg

  6. #6
    train spotter
    Join Date
    Aug 2001
    Location
    near a computer
    Posts
    3,853
    to insert you first need to tell the database you want to change it.

    I also think you have to tell the database you want to transact before you open the record set. This is differnet to MSDN's help which states
    "Call BeginTrans after you open any recordsets involved in the transaction and as close to the actual update operations as possible."

    The code below works in my applications.

    Code:
    //open the database
    CDatabase  DB;
    DB.OpenEx(DB_DNS_NAME,CDatabase::noOdbcDialog);
    //tell it you want to change it
    DB.BeginTrans();
    //open the recordset
    CDBDerivedRecordSet  RS(&DB);
    //SQL_Statement is your SQL select or just the table name
    RS.Open(AFX_BD_USE_DEFAULT_TYPE,SQL_Statement);
    RS.AddNew();//add new record
    //or
    RS.Edit();
    //if using edit check the return from RecordSet Update() looking for FALSE
    if(!RS.Update())
    {
    	iError=GetLastError();
    	if(!iError)
    	{
    		//no error, data was same as that already in database
    	}
    }
    DB.CommitTrans();
    RS.Close();
    BD.Close();
    
    //these may help in debugging
    DB.CanTransact();
    
    RS.CanUpdate();
    
    RS.IsBOF();
    To create a derived recordset in MSVC .NET 2003 (using MFC), add a MFC ODBC consumer class to the project.

    Click the 'DATA SOURCE' button and select your DSN, it may be in the machine data sources.
    Enter any admin logon info or just click OK when asked to connect to the database.
    Select the required table from the database and click OK.
    "Man alone suffers so excruciatingly in the world that he was compelled to invent laughter."
    Friedrich Nietzsche

    "I spent a lot of my money on booze, birds and fast cars......the rest I squandered."
    George Best

    "If you are going through hell....keep going."
    Winston Churchill

  7. #7
    Registered User Codeplug's Avatar
    Join Date
    Mar 2003
    Posts
    4,607
    You don't have to bother with CDatabase::BeginTrans() and CDatabase::CommitTrans() unless you want transactional rollback functionality.

    gg

  8. #8
    Banned maes's Avatar
    Join Date
    Aug 2001
    Posts
    744
    Thanks for all the reply's guys and sorry for my late reply.

    I tried it with deriving a class from CRecordset (MSVC++ 6) with the class wizard.
    It opened my database correct. I have member variables for all fields of the DB but I got errors.
    When I open a recordset with the tablename as second parameter, I get an error saying something like this "Not enough parameters. The expected amount is 36"
    This is not a VC error, it is a messagebox that pops up in my program (that I didn't write). It is the Jet engine that invokes this message.

    here's the code:
    Code:
    CDBDerivedRecordSet  RS(&g_mydb);
    duits='"+m_german+"'" );
    try
    {
      RS.Open(AFX_DB_USE_DEFAULT_TYPE,"woorden");
      if(RS.CanUpdate()==0)
        AfxMessageBox("can NOT be updated");
      if(RS.IsBOF()==0)
        AfxMessageBox("has records");
    }
    catch(void)
    {
      int iError=GetLastError();
    }
      //RS.AddNew();//add new record
    RS.Close();
    The 2 if statements are never reached. I pleaced breakpoints there but the program doesn't get there. Also the catch doesn't work. same problem as with the if's the program doesn't get to it.

    and when I try to open it with a select statement as I've done before with the original class. I get an other messagebox:"Invalid descriptor index"
    Code:
    CDBDerivedRecordSet  RS(&g_mydb);
    strSQL.Format( "SELECT COUNT(nr) FROM woorden WHERE duits='"+m_german+"'" );
    try
    {
      RS.Open(AFX_DB_USE_DEFAULT_TYPE,strSQL);
      if(RS.CanUpdate()==0)
        AfxMessageBox("can NOT be updated");
      if(RS.IsBOF()==0)
        AfxMessageBox("has records");
    }
    catch(void)
    {
      int iError=GetLastError();
    }
    //RS.AddNew();//add new record
    RS.Close();
    also so tryed it with BeginTrans(); but didn't make a diffrence

    here's how I open my DB, maybe there is something wrong:
    Code:
    sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
    sFile="duits.mdb";
    
    sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,sFile);
    g_mydb.Open(NULL,false,false,sDsn);
    Pffff, what am I doing wrong here
    SVG is the future

  9. #9
    train spotter
    Join Date
    Aug 2001
    Location
    near a computer
    Posts
    3,853
    >>RS.Open(AFX_DB_USE_DEFAULT_TYPE,"woorden");<<

    Sorry I forgot to mention the table name has to be enclosed in [] if used on its own. ie "woorden" should be "[woorden]" (if only using the table name/title).

    Have you added a DSN to the PC thru the ODBC dialog?

    If so, try this to open your DB.
    Code:
    CDatabase	DB;
    if(!DB.OpenEx("DSN=YourDataSourceName",CDatabase::noOdbcDialog))
       return -1;
    
    //if not add one with
    CString      Attrib.Format("DSN=YourDataSourceName; DBQ=\\\\server\\Path\\YourDatabase.mdb; DESCRIPTION=Test; READONLY=FALSE; EXCLUSIVE=FALSE;" );
    
    //this adds a SYSTEM DSN for the database to use, there other flags for diff  types
    
    ::SQLConfigDataSource(NULL, ODBC_ADD_SYS_DSN, "Microsoft Access Driver (*.mdb)", Attrib);
    I find using RS.IsBOF() is handy as well.
    Last edited by novacain; 12-22-2003 at 07:40 PM.
    "Man alone suffers so excruciatingly in the world that he was compelled to invent laughter."
    Friedrich Nietzsche

    "I spent a lot of my money on booze, birds and fast cars......the rest I squandered."
    George Best

    "If you are going through hell....keep going."
    Winston Churchill

  10. #10
    Banned maes's Avatar
    Join Date
    Aug 2001
    Posts
    744
    I've put the table name within [] but sadly, tha didn't made a diffrence. I still get the errors

    I also tryed with SQLConfigDataSource. But I get a VC error saying that it is not a member of the global namespace. I've put it in the constructor of my main window program (where I open my original db connection). msdn doesn't say which library or header file to include. did I put it in the wrong place?

    then I tryed opening the recordset with no parameters.
    Code:
    CDBDerivedRecordSet  RS(&g_mydb);
    RS.Open();
    if(RS.CanUpdate()==0)
        AfxMessageBox("can NOT be updated");
    if(RS.IsBOF())
       AfxMessageBox("has records");
    RS.Close();
    when I do this, both messageboxes pop up saying it can't be updated and it has no records.
    But that isn't correct. there are records in the DB (not all tables have records but some have) and how does it know what table to check for records.

    Have I derived the class wrong? I used the class wizard from VC++6 and derived from CRecordset.
    When I was asked to select the table, I selected all of the tables. Is this the right way to do it or shoul I've created a class for every table. But that would give me alot of classes (I have 12 tables) for a small programme. I can't see the point of it.



    Maes


    BTW, if you think: "I have enough of this db questions", I can't blame you. I'm starting to think the same way .
    SVG is the future

  11. #11
    Registered User
    Join Date
    Feb 2002
    Posts
    329
    You don't use a recordset to insert records, you just use the database object.

    Code:
    g_mydb.ExecuteSQL(LPCTSTR(strSQL))
    Use ExecuteSQL for insert/delet/update/etc.. Use recordset for select

  12. #12
    train spotter
    Join Date
    Aug 2001
    Location
    near a computer
    Posts
    3,853
    >>Is this the right way to do it or shoul I've created a class for every table.

    I have one for every table. Yes its a lot of tables. Most are small and have few member functions.

    Test that the Record set opened with

    if(!RS.Open())

    as it will not be update-able or have records if it is closed.


    SQLConfigDataSource() is in odbcinst.h (in MSVC right click and go to its declaration to find the right header)
    "Man alone suffers so excruciatingly in the world that he was compelled to invent laughter."
    Friedrich Nietzsche

    "I spent a lot of my money on booze, birds and fast cars......the rest I squandered."
    George Best

    "If you are going through hell....keep going."
    Winston Churchill

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Access a Specific View in MainFrame :: MFC
    By kuphryn in forum Windows Programming
    Replies: 1
    Last Post: 01-07-2011, 08:06 AM
  2. db program access GetFieldValue
    By WaterNut in forum C++ Programming
    Replies: 2
    Last Post: 11-06-2005, 09:14 PM
  3. Mfc Dao Access Database
    By LISANANA in forum Windows Programming
    Replies: 1
    Last Post: 07-03-2003, 01:18 PM
  4. MFC :: WM_SIZE message causes access violation?
    By SyntaxBubble in forum Windows Programming
    Replies: 1
    Last Post: 06-09-2003, 10:01 AM
  5. DBComboBox and Access DB
    By dbs5150 in forum C++ Programming
    Replies: 0
    Last Post: 03-11-2002, 09:53 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21