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

Adding a Record to a Recordset

gg

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

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.

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

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.

You don't have to bother with CDatabase::BeginTrans() and CDatabase::CommitTrans() unless you want transactional rollback functionality.

gg

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

>>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.

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 .

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

>>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)