CODEWITHSUNDEEP
c#ms-accessado.net
Lee.J.Baxter
Lee.J.Baxter

Reputation: 555

How can I manually set an ID for an AUTOINCREMENT column using a designer-generated DataTable?

I'm creating a database system in C# for a library system (using an Access database), and I've hit a brick wall. I've got a table called "BSUser" which has an auto-increment column for the primary key, "UserID"; I want this to be auto-generated if the user doesn't enter a value when defining a new User, but I also want it to be able to accept a manually-defined value. I'm using the following code to create my new row given the information entered by the user. Note the check to see whether the user ID is null...

BookshelfDataSet.BSUserRow user = dataSet.BSUser.NewBSUserRow();
if (userID != null) user.UserID = userID.Value;
user.Forename = forename;
user.Surname = surname;
user.IsAdmin = isAdmin;
dataSet.BSUser.AddBSUserRow(user);
SaveDataSetToDatabase();

Here's the code that should update the database...

private void SaveDataSetToDatabase()
{
    this.bookTableAdapter.Update(this.dataSet);
    this.copyTableAdapter.Update(this.dataSet);
    this.userTableAdapter.Update(this.dataSet);
    this.loanTableAdapter.Update(this.dataSet);
    this.ratingTableAdapter.Update(this.dataSet);
}

The row is successfully being saved to the database, but its ID is being automatically generated despite the fact that I've set it manually. This is a problem, as the existing users of the library will already have IDs from the old system, so I need to carry these over into the new system. Please could somebody tell me what I'm doing wrong and how to fix the problem?

Upvotes: 2

Views: 2294

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123664

For those using Access 2010 or later, the desired result can be achieved by using a Before Change data macro. For a sample table named [BSUser]:

[UserID] – Long Integer, Primary Key
[ExistingID] – Long Integer
[Forename] – Text(100)
[Surname] – Text(100)

and the Before Change data macro

BeforeChange.png

if we include an [ExistingID] value then it will be used as the [UserID] primary key (provided that it does not already exist in the table)

var ta = new Database1DataSetTableAdapters.BSUserTableAdapter();
ta.Insert(null, 3, "Gord", "Thompson");  // insert with ExistingID = 3

resulting in

UserID  ExistingID  Forename  Surname    
------  ----------  --------  -----------
     3           3  Gord      Thompson

and if we don't include an [ExistingID] value then the macro will assign one for us

var ta = new Database1DataSetTableAdapters.BSUserTableAdapter();
ta.Insert(null, null, "Nora", "Newuser");  // insert with ExistingID = null

like so

UserID  ExistingID  Forename  Surname    
------  ----------  --------  -----------
     3           3  Gord      Thompson   
  1000              Nora      Newuser

Upvotes: 2

STLDev
STLDev

Reputation: 6174

You'll have to either use an auto-incrementing value, or set it yourself. There is no option to do both.

Upvotes: 1

Related Questions