AutoID Field in Many

2018-06-12 06:05:14

I have a database of "Items", which are assigned to multiple "Categories". Categories can have multiple items, and vice versa. The relevant portion of the database structure is as follows:

[tblItem]
ItemID (AutoNumber)
MainText (Text)

[tblCategory]
CategoryID (AutoNumber)
Name (Text)

[tblItemCategory]
ItemID (Long Integer)
CategoryID (Long Integer)

I want to build a panel component which shows a category name at the top, with a databound grid of items belonging to that grid below. There will be many instances of this panel component, and the end-user should be able to create a new item and simultaneously assign it to the category in question from any one of them.

In MS Access, it's possible to create a nested form, with the "child" one databound to a query which is "MasterFields" linked to a databound "Category" field on the "parent" form, such that the grid of items changes as the Category field is changed. This Items grid can also easily have new records added to it, with both the ItemID (in tblItem AND table tblItemCategory) and the linked CategoryID field (in tblItemCategory) being populated automatically.

The query for that Access form's grid is:
SELECT tblItemCategory.CategoryID, tblItem.*
FROM tblItemCategory LEFT JOIN tblItem ON tblItemCategory.ItemID = tblItem.ItemID
ORDER BY tblItemCategory.CategoryID;

If I try the same thing in Delphi, the ItemID AutoNumber field doesn't get populated, resulting in the following error:

..exception class EOleException with message 'The field 'tblItemCategory.ItemID' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field'.

..and the ItemID field is accordingly blank in the grid.

Is there a way to get Delphi/ADO to handle the behind-the-scenes two-table ItemID population as easily/neatly as Access does, without manually handling it programmatically? If not, what's the best/most elegant way to handle it programmatically?

I'd like to keep whatever solution I end up with as closely tied to the conventional TDataSet / TDataSource approach as possible, as I use a number of different kinds of databound controls, all of which will have to deal with this same data structure.

(Note: I'm using Delphi 2007 and an MSAccess 2000 format MDB file.)

Pretty much the same way. Theres's master source and master fields properties, so you just chain detail to master.

So mastersource would be Customer, Detail Source, Orders linked by CustomerID.

Dead easy to show this, but hard to explain.

Some other bloke wrote it all out though.

Master Detail Forms And Delphi

If you want to add new records to the tables via the grid, then you will have to use the BeforePost method of the underlying query in order to obtain the new key, which you then insert manually into the link table along with the category id.

I admit that I never write code with editable (and insertable) grids so my answer may need a little tweaking.

链接地址: http://www.djcxy.com/p/35064.html

上一篇: VirtualStringTree如何释放多个节点

下一篇: 自动识别字段在许多