A simple ADO.NET and table design question
-
Hi, All: I am writing my FIRST Windows Form Application in .NET to edit my MS Access database. In this Access DB, I have a table with two columns: EquipmentID and EquipmentDescription. Some other tables use the ID to refer the equipment. This application supports multiple users at the same time. That mean that it could be more than one user add new equipmment. I use in-memory DataSet to get user's input and then call Insert/Delete/Update methods. I am debating that whether I should use Access AutoNumber for the ID field, or I should just use DataReader to get the Max ID used, then Add 1 to create my new ID. The AutoNumber makes things simpler, but if I try to merge to DB later, I will have a lot of headaches. If I assign my own ID, I might have conflict with other users and fail the INSERT. So what is the typical way to achieve this, I know a lot of people have done this, please give me some ideas. Thanks in advance. Dion
-
Hi, All: I am writing my FIRST Windows Form Application in .NET to edit my MS Access database. In this Access DB, I have a table with two columns: EquipmentID and EquipmentDescription. Some other tables use the ID to refer the equipment. This application supports multiple users at the same time. That mean that it could be more than one user add new equipmment. I use in-memory DataSet to get user's input and then call Insert/Delete/Update methods. I am debating that whether I should use Access AutoNumber for the ID field, or I should just use DataReader to get the Max ID used, then Add 1 to create my new ID. The AutoNumber makes things simpler, but if I try to merge to DB later, I will have a lot of headaches. If I assign my own ID, I might have conflict with other users and fail the INSERT. So what is the typical way to achieve this, I know a lot of people have done this, please give me some ideas. Thanks in advance. Dion
If you use a GUID (Globally Unique Identifier) as the ID, you should have something that can be ported to any platform as well as solve your Identity problems. I have previously used a VB6 function that produced GUID's, and am sure you should find one for .Net. Regards Peet Schultz Centurion SA YASP
-
Hi, All: I am writing my FIRST Windows Form Application in .NET to edit my MS Access database. In this Access DB, I have a table with two columns: EquipmentID and EquipmentDescription. Some other tables use the ID to refer the equipment. This application supports multiple users at the same time. That mean that it could be more than one user add new equipmment. I use in-memory DataSet to get user's input and then call Insert/Delete/Update methods. I am debating that whether I should use Access AutoNumber for the ID field, or I should just use DataReader to get the Max ID used, then Add 1 to create my new ID. The AutoNumber makes things simpler, but if I try to merge to DB later, I will have a lot of headaches. If I assign my own ID, I might have conflict with other users and fail the INSERT. So what is the typical way to achieve this, I know a lot of people have done this, please give me some ideas. Thanks in advance. Dion
There're was an article on MSDN covering this issue, but I cannot find it. What it suggests is to configure your EquipmentID to use negative autoincrement values:
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementSeed = -1; ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementStep = -1;...and to modify your insert SP (or insert code if you use Access) to return the newly created identity value. SP would look like this:CREATE PROCEDURE dbo.daInsertEquipment ( @EquipmentID int OUT, @EquipmentDescription nvarchar(200) ) AS INSERT INTO Equipment (EquipmentID, EquipmentDescription) VALUES (@EquipmentID, @EquipmentDescription) SET @id = SCOPE_IDENTITY()HTH Alexandre Kojevnikov MCP (SQL2K) Leuven, Belgium