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
– user D, Commented Apr 2, 2014 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.
– Mike C., Commented Apr 2, 2014 at 13:37
sorry how would you implement a auto increment in this context? sorry i am new at this — user D
– user D, Commented Apr 2, 2014 at 13:44

Community · Accepted Answer · 2017-05-23 12:11:11Z

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, 2017 at 12:11

CommunityBot

11 silver badge

answered Apr 2, 2014 at 13:32

bamblack

3,7892 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user D Over a year ago

can you tell me how i make it a primary key? and then auto increment? sorry I am new at this.

bamblack Over a year ago

I've edited my answer to give you some pointers on where to find the info you need.

user D Over a year ago

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

bamblack Over a year ago

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.

user D Over a year ago

thanks ben. I have been able to resolve it by using a contributorusers.max(i => i.BPid)+1

ctschap · Accepted Answer · 2014-04-02 13:34:08Z

0

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.

answered Apr 2, 2014 at 13:34

ctschap

611 silver badge4 bronze badges

Comments

display name · Accepted Answer · 2014-04-02 13:38:22Z

0

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

answered Apr 2, 2014 at 13:38

display name

4,1952 gold badges32 silver badges55 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related