Adding int value to database giving error, yet string value works

Question

I am currently developing in an MVC project, using Entity framework, and SQL Express server.

I am trying to add a user to a Users table. The user is currently compromised of the following:

CREATE TABLE [dbo].[Users] (
    [UserID] INT NOT NULL PRIMARY KEY,
    [Admin]  BIT NOT NULL,
);

When I go through the function below to add into the above table:

[HttpPost]
public String AddUser(List<String> values)
{
    using (DataBASE db = new DataBASE())
    {
        Users newUser = new Users();
        newUser.UserID = Int32.Parse(values[0]); //There is correct data stored here as an int value
        newUser.Admin = Boolean.Parse(values[1]); //There is correct data stored here as an bool value
        db.Users.Add(newUser); //The newUser object is correct
        db.SaveChanges(); //The error occurs here
    }
    return "success";
}

EDIT, this is the Users class requested (which is changed to a string whenever stored into the database):

[Table("Users")]
public class Users
{
    [Key]
    public int UserID { get; set;}
    public bool Admin { get; set; }
}

I receive the following error:

"Cannot insert the value NULL into column UserID, table ‘…\\DATABASE.MDF.dbo.Users';
 column does not allow nulls. INSERT fails.\r\nThe statement has been terminated."}

The value is not NULL and the database is connecting correctly (as the delete function works properly and I when I break into the function the database connection is correct and the function is giving the correct data before updating by adding a new user.

When I change the database to the following:

CREATE TABLE [dbo].[Users] (
    [UserID] NVARCHAR(50) NOT NULL PRIMARY KEY,
    [Admin]  BIT NOT NULL,
);

And I add a user using the following function (changed the function above to where it doesn't pass an int but rather keeps the string value):

[HttpPost]
public String AddUser(List<String> values)
{
    using (DataBASE db = new DataBASE())
    {
        Users newUser = new Users();
        newUser.UserID = values[0]; //There is correct data stored here as an string value
        newUser.Admin = Boolean.Parse(values[1]); //There is correct data stored here as an bool value
        db.Users.Add(newUser); //The newUser object is correct
        db.SaveChanges(); //NO error occurs here
    }
    return "success";
}

This is the Users class changed to a string to work for the string solution:

[Table("Users")]
public class Users
{
    [Key]
    public string UserID { get; set;}
    public bool Admin { get; set; }
}

Is there something against using an INT value in the database that I do not know of?

show your Users class. And unrelated but please name your class in singular. Most probably your Id has Identity flag set. — Mat J
– Mat J, Commented Jul 20, 2016 at 17:39
@JohnBustos & Mathew I have added the user class and its current types which is currently an int. — Baraa
– Baraa, Commented Jul 20, 2016 at 17:46
@ARUN inserting through the database manually? Or writing out the SQL Statement my self instead of using the Add function? I insterted manually which works fine no matter what the variables are. But may write out my own SQL statement — Baraa
– Baraa, Commented Jul 20, 2016 at 17:47

Kirill Bestemyanov · Accepted Answer · 2016-07-20 20:56:46Z

3

You should add attribute

[DatabaseGenerated(DatabaseGeneratedOption.None)]

to your UserId property. It says that EF should get value from property instead of defaulting on database level.

answered Jul 20, 2016 at 20:56

Kirill Bestemyanov

12k2 gold badges26 silver badges38 bronze badges

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

1 Comment

Baraa Over a year ago

Thank you...I thought I was loosing my mind. This fixed the issue.

Arun Prasad E S · Accepted Answer · 2016-07-20 18:28:55Z

0

 Users newUser = new Users();
        newUser.UserID = Int32.Parse(values[0]); //There is correct data stored here as an int value
        newUser.Admin = Boolean.Parse(values[1]); //There is correct data stored here as an bool value


        db.Users.Attach(newUser);


        db.Users.Add(newUser); //The newUser object is correct
        db.SaveChanges();

Final try, I am not sure , Just came to mind

if same id already in database, may have trouble

edited Jul 20, 2016 at 18:28

answered Jul 20, 2016 at 18:23

Arun Prasad E S

10.2k8 gold badges81 silver badges94 bronze badges

1 Comment

Baraa Over a year ago

I am sorry the above did not work, I just posted a solution to my issue but had to execute raw query. Will never know why it doesn't work.

Baraa · Accepted Answer · 2016-07-20 18:42:25Z

0

To be honest this isn't much of answer. But the best thing that I was able to do to solve my issue, which is writing a raw query and executing it below works with int values in the database and throughout the application. I still do not know, and probably will never know, why the above function does not work for int values.

        using (DataBASE db = new DataBASE())
        {
            try
            {
                db.Database.ExecuteSqlCommand("INSERT INTO Users VALUES({0},{1})", Int32.Parse(values[0]), Boolean.Parse(values[1]));
                db.SaveChanges();
            }
            catch (Exception e)
            {
                var exception = e;
            }
        }

answered Jul 20, 2016 at 18:42

Baraa

7271 gold badge10 silver badges26 bronze badges

1 Comment

Gert Arnold Over a year ago

Look at Kirill Bestemyanov's answer. User's primary key is not an identity field (auto-incremented), but EF assumes it is, by default. Therefore, it doesn't include the field in the INSERT statement.

Uriel Gonçalves · Accepted Answer · 2016-07-20 20:40:14Z

0

You could try rename the UserID column to Id or UsersId, removing the [Key] notation, following the EF convention, but just for test.

answered Jul 20, 2016 at 20:40

Uriel Gonçalves

263 bronze badges

Collectives™ on Stack Overflow

Adding int value to database giving error, yet string value works

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related