CODEWITHSUNDEEP
sql-server
javing
javing

Reputation: 12433

Why i cant add new columns to my Users table?

I am doing some homework. The users of my database uses some other attributes, not just the ones that ASP 2.0 automatically created for me when i implemented the login and registration mechanism. But when i try to save the modification displays me an error. Can someone give me a hand? This is the error:

enter image description here The error says:

'aspnet_Users' table - Unable to modify table. ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'kjoptekvoten' cannot be added to non-empty table 'aspnet_Users' because it does not satisfy these conditions.

That database was automatically created when i implemented Forms based authentification and registration. The problem now is that that users needs some more attributes. How can i give to it more attributes? What is the easiest way to do it?Does not mind if it is not theorically correct(It is just for a homework).

I would appreciate a lot your help.

Upvotes: 3

Views: 8210

Answers (4)

Squirrel5853
Squirrel5853

Reputation: 2406

Normally I just set the column as allow nulls

then do an SQL UPDATE TABLE SET VALUE = whateva

then update the table definition to not allow nulls.

Upvotes: 2

Pleun
Pleun

Reputation: 8920

Apart form the technicalities on the database side, there is a deeper issue here.

You should not alter the aspnet_Users table because you are bypassing the way the membership 'system' in asp.net is working. Instead, have a look into the Profile mechanism: https://web.archive.org/web/20211020111657/https://www.4guysfromrolla.com/articles/101106-1.aspx

Upvotes: 5

Christian Specht
Christian Specht

Reputation: 36451

The error message says it all:
You are adding a new column that can't be Null (checkbox "Allow Nulls" not checked), but as you didn't provide a default value, it will be Null.
So SQL Server can't create the new column.

You can do two things:

a) Create the new column with Nulls allowed.
THEN put a default value in all existing rows:
update aspnet_Users set kjoptekvoten = 0)
...and THEN uncheck "Allow Nulls"

b) Create the new column directly with default values.
I don't know if you can do this in Management Studio, but it's easy in T-SQL:

alter table aspnet_Users 
add kjoptekvoten int not null
constraint Name_For_Constraint default(0) with values

This will add the new not nullable column, AND create a constraint with a default value, AND fill the default value in all existing rows (SQL Server will not do this without the "with values" clause).

Upvotes: 4

faester
faester

Reputation: 15086

You need to make the new attributes nullable or provide a default value. But you also need to consider how to obtain the values from db. The sql membership provider utilizes an auto generated stored procedure to put data into the membership user instance returned,so just adding the attributes in the table will not be sufficient to get the attribute values to your application. I would use a user attribute table instead.

Upvotes: 3

Related Questions