Can't set auto-increment via SQL Server Express Management Studio?

Question

I just tried inserting value in to a database and that work. Now I insert again and I get an error for identical primary key.

I can't find any option to alter it to be auto-increment.

I'm updating the table via Linq-To-Sql.

User u = new User(email.Text, HttpContext.Current.Request.UserHostAddress,
                                        CalculateMD5Hash(password.Text));
db.Users.InsertOnSubmit(g);
db.SubmitChanges();

I didn't fill in the user_id and it worked fine the first time. It became zero. Trying to add a second user, it wants to make the ID 0 again.

I could query the database and ask for the highest ID, but that's going to far if you know about auto-increment.

How can I turn this on? All I can find are scripts for table creation. I'd like to keep my existing table and simply edit it.

Answer 1

How is your Linq-to-SQL model defined?? Check the properties of the user_id column - what are they set to??

In your Linq-to-SQL model, be sure to have Auto Generated Value set to true, Auto-Sync set to OnInsert, and the server data type should also match your settings (INT IDENTITY),

In SQL Server Management Studio, you need to define the user_id column to be of type INT IDENTITY - in the visual table designer, you need to set this property here:

Answer 2

It is zero because you have a integer for a primary key column type. To use auto-increment, set tables identity column to the ID (selected in the table properties)

Answer 3

Would probably be easier to edit the database using VS if you have a version that will work for, otherwise if you have to edit it in management studio see this article: http://blogs.msdn.com/b/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

Answer 4

Or you can increment the user_id manually and pass it to the insert function if you cannot alter the property/table field description