Trying to Query a SQL Server from ASP.net MVC 4 using C#

Question

I am trying to use a query to find the last ID (entry) in the table called ContributorUser in the database FPTContributorUsers and then add in a new entry thus assigning it the next available ID.

the below code allows me to add data to the table in the database however when I run it the ID (new entry to the table) shows as 0 and not 4. because I currently have three entries in my table

[HttpPost]
public ActionResult AddContributor(ContributorUsers AddCont)
{
  if (AddCont.UserID == null)
  {
    throw new HttpException(404, "Please enter a valid RacfId");
  }
  else
  {
    FPTContributorUsers NewUser = new FPTContributorUsers();
    NewUser.UserID = AddCont.UserID;
    NewUser.ID = AddCont.ID;
    db.ContributorUsers.Add(NewUser);
    db.SaveChanges();
    return RedirectToAction("index");
  }
}

sorry db is my instance of the entity container which is connected to the database and thus the table in question — user D, Apr 02 '14 at 13:36
I don't see where you're getting the next ID value in this code or why you would think that NewUser.ID would be anything other than what is already inside AddCont.ID. As a suggested below, using an auto-increment on that field and then simply not specifying an ID is more typical (IMO). — Mike C., Apr 02 '14 at 13:37
sorry how would you implement a auto increment in this context? sorry i am new at this — user D, Apr 02 '14 at 13:44

score 2 · Accepted Answer · edited May 23 '17 at 12:11

2

Have you thought about just making that the primary key and having it auto increment so you don't need to determine what it is yourself? That's usually the best way to go about handling actual ID's in my experience.

To do this in SQL Server follow these steps

To do this in MySQL follow these steps

As a note, if you do this you will need to update your EF model.

The other way to do it if you can't edit the database is to use MAX() for that column which will return the highest ID value, then just add one to it, no EF model updating required.

edited May 23 '17 at 12:11

Community

1
1

answered Apr 02 '14 at 13:32

Ben Black

3,751
2
25
43

can you tell me how i make it a primary key? and then auto increment? sorry I am new at this. – user D Apr 02 '14 at 13:40
I've edited my answer to give you some pointers on where to find the info you need. – Ben Black Apr 02 '14 at 13:43
thanks. Any idea how I can implement this just in the controller of visual studios. Just because my work task requires me to avoid programming sql server – user D Apr 02 '14 at 14:30
What do you mean avoid programming SQL server? Do you mean you just can't access it and can pass SQL Commands to the server or you can't edit the schema of the database? Either way, this kind of change requires that the change be made at the database level. – Ben Black Apr 02 '14 at 14:45
thanks ben. I have been able to resolve it by using a contributorusers.max(i => i.BPid)+1 – user D Apr 03 '14 at 13:34

score 0 · Answer 2 · answered Apr 02 '14 at 13:34

Try removing the "NewUser.ID = AddCont.ID" line and wait to get the NewUser.ID until after "db.SaveChanges()"

else
  {
    FPTContributorUsers NewUser = new FPTContributorUsers();
    NewUser.UserID = AddCont.UserID;
    db.ContributorUsers.Add(NewUser);
    db.SaveChanges();
    return RedirectToAction("index");
  }

If the NewUser.ID property is an Identity Field in the database, it will not get populated until after the record is created during the SaveChanges() transaction commit.

score 0 · Answer 3 · answered Apr 02 '14 at 13:38

0

Try Reload() the DB Context after SaveChanges() before you call the NewUser.ID so that the context is up-to-date

answered Apr 02 '14 at 13:38

display name

4,165
2
27
52

Trying to Query a SQL Server from ASP.net MVC 4 using C#

3 Answers3