CRecordset, ODBC, and Access
-
I've a table in an Access database with an AutoNumber field. When I add a row to the table through a
CRecordsetobject, I want to know the value of the AutoNumber field. I've read various articles on how this is done, but none of them work. The code basically looks like:CMyRecordset set;
CDBVariant bookmark;
set.Open(AFX_DB_USE_DEFAULT_TYPE, NULL, CRecordset::useBookmarks);
set.AddNew();
set.field1 = 123;
set.field2 = 456;
...
set.GetBookmark(bookmark);
// the value of 'bookmark' at this point is invalid since the recordset has
not been updatedset.Update(); // this adds the new record, but also makes the first record
the current oneset.Requery();
set.SetBookmark(bookmark); // this has no effectI successfully use bookmarks in other areas of the program when editing rows. I populate a listbox with each record in a recordset and assign the AutoNumber field using
SetItemData(). When an item in the listbox is selected and the Edit button is clicked, I have the correct bookmark and can then useSetBookmark()andEdit()successfully. Using this scenario, if a newly added listbox item does not have a correct bookmark associated with it,SetBookmark()cannot be used prior toEdit()orDelete()calls. In other words, I have no way to navigate to the desired record. Any ideas for this long-winded request? Thanks, DC
Five birds are sitting on a fence. Three of them decide to fly off. How many are left?
-
I've a table in an Access database with an AutoNumber field. When I add a row to the table through a
CRecordsetobject, I want to know the value of the AutoNumber field. I've read various articles on how this is done, but none of them work. The code basically looks like:CMyRecordset set;
CDBVariant bookmark;
set.Open(AFX_DB_USE_DEFAULT_TYPE, NULL, CRecordset::useBookmarks);
set.AddNew();
set.field1 = 123;
set.field2 = 456;
...
set.GetBookmark(bookmark);
// the value of 'bookmark' at this point is invalid since the recordset has
not been updatedset.Update(); // this adds the new record, but also makes the first record
the current oneset.Requery();
set.SetBookmark(bookmark); // this has no effectI successfully use bookmarks in other areas of the program when editing rows. I populate a listbox with each record in a recordset and assign the AutoNumber field using
SetItemData(). When an item in the listbox is selected and the Edit button is clicked, I have the correct bookmark and can then useSetBookmark()andEdit()successfully. Using this scenario, if a newly added listbox item does not have a correct bookmark associated with it,SetBookmark()cannot be used prior toEdit()orDelete()calls. In other words, I have no way to navigate to the desired record. Any ideas for this long-winded request? Thanks, DC
Five birds are sitting on a fence. Three of them decide to fly off. How many are left?
In theory - you need to obtain an exclusive lock to your datasource (I don't know how to do this because depending on ODBC drivers/ADO vrs DAO/etc. it changes). Then do the insert. Then query for MAX on the ID column. This will give you access to the newly inserted record. Then unlock it.
Have you answered an MTQ? Check out the stats!
What's the latest butt-scratch count? Check it out! -
I've a table in an Access database with an AutoNumber field. When I add a row to the table through a
CRecordsetobject, I want to know the value of the AutoNumber field. I've read various articles on how this is done, but none of them work. The code basically looks like:CMyRecordset set;
CDBVariant bookmark;
set.Open(AFX_DB_USE_DEFAULT_TYPE, NULL, CRecordset::useBookmarks);
set.AddNew();
set.field1 = 123;
set.field2 = 456;
...
set.GetBookmark(bookmark);
// the value of 'bookmark' at this point is invalid since the recordset has
not been updatedset.Update(); // this adds the new record, but also makes the first record
the current oneset.Requery();
set.SetBookmark(bookmark); // this has no effectI successfully use bookmarks in other areas of the program when editing rows. I populate a listbox with each record in a recordset and assign the AutoNumber field using
SetItemData(). When an item in the listbox is selected and the Edit button is clicked, I have the correct bookmark and can then useSetBookmark()andEdit()successfully. Using this scenario, if a newly added listbox item does not have a correct bookmark associated with it,SetBookmark()cannot be used prior toEdit()orDelete()calls. In other words, I have no way to navigate to the desired record. Any ideas for this long-winded request? Thanks, DC
Five birds are sitting on a fence. Three of them decide to fly off. How many are left?
after update you are requerying and setting the bookmark. this is wrong. you can get bookmark of a current record after you update. and bookmarks are valid only for navigating in the current recordsets. once you close and reopen or requery then the bookmarks get vanished. you should call recordset.canbookmark() to check whether it is possible to bookmark or not. use CDatabase::GetBookmarkPersistence. always use exception handling in database programming to get the actual cause of erro. never say die
-
after update you are requerying and setting the bookmark. this is wrong. you can get bookmark of a current record after you update. and bookmarks are valid only for navigating in the current recordsets. once you close and reopen or requery then the bookmarks get vanished. you should call recordset.canbookmark() to check whether it is possible to bookmark or not. use CDatabase::GetBookmarkPersistence. always use exception handling in database programming to get the actual cause of erro. never say die
sam India wrote: after update you are requerying and setting the bookmark. this is wrong. Please explain. sam India wrote: you can get bookmark of a current record after you update. Which is what I am doing. sam India wrote: and bookmarks are valid only for navigating in the current recordsets. once you close and reopen or requery then the bookmarks get vanished. you should call recordset.canbookmark() to check whether it is possible to bookmark or not. use CDatabase::GetBookmarkPersistence. As I already indicated, bookmarks are used elsewhere so
CanBookmark()was omitted from the code snippet. I have usedCheckBookmarkPersistence()to verify that bookmarks support theSQL_BP_SCROLL,SQL_BP_UPDATE, andSQL_BP_DELETEoperations. sam India wrote: always use exception handling in database programming to get the actual cause of erro. Exception handling is in place. I omitted it from the code snippet for brevity.
Five birds are sitting on a fence. Three of them decide to fly off. How many are left?
-
In theory - you need to obtain an exclusive lock to your datasource (I don't know how to do this because depending on ODBC drivers/ADO vrs DAO/etc. it changes). Then do the insert. Then query for MAX on the ID column. This will give you access to the newly inserted record. Then unlock it.
Have you answered an MTQ? Check out the stats!
What's the latest butt-scratch count? Check it out!Terry O`Nolley wrote: In theory - you need to obtain an exclusive lock to your datasource... I'll try this, but I'm not following your logic. What would an exclusive lock on a single-user database have to with being able to query an AutoNumber field of a newly-added record? I guess my main concern is that the dozen or so examples I looked at on MSDN and the Internet all showed basically the same approach: after an
Update(), get the bookmark, callRequery(), set the bookmark, get the AutoNumber field value. The problem seems to be that after the call toUpdate(), the recordset is positioned back at the first record.
Five birds are sitting on a fence. Three of them decide to fly off. How many are left?
-
Terry O`Nolley wrote: In theory - you need to obtain an exclusive lock to your datasource... I'll try this, but I'm not following your logic. What would an exclusive lock on a single-user database have to with being able to query an AutoNumber field of a newly-added record? I guess my main concern is that the dozen or so examples I looked at on MSDN and the Internet all showed basically the same approach: after an
Update(), get the bookmark, callRequery(), set the bookmark, get the AutoNumber field value. The problem seems to be that after the call toUpdate(), the recordset is positioned back at the first record.
Five birds are sitting on a fence. Three of them decide to fly off. How many are left?
DavidCrow wrote: I'll try this, but I'm not following your logic. What would an exclusive lock on a single-user database have to with being able to query an AutoNumber field of a newly-added record? I didn't realize it was a single-user DB. If it is a single user DB then you don't need the lock. Just do the insert and query for MAX ID. If your ID field is an auto-number, then the MAX will be the newly inserted record. Using LOCK prevents other records from being inserted while you do your insert.
Have you answered an MTQ? Check out the stats!
What's the latest butt-scratch count? Check it out!