armbsu
asked on
Can not make autogenerate column in Sql server 2008
Hello,
I am using SQL server. I am trying to make one of the columns as an autogenerate one. But when I go to do that (Is Indentity), it is not allowing me to put value to Yes. it's greyed out. I am new to the development of SQ server 2008. Is this method right? What else I have to do to make it autogenerated.
I am using Sql server 2008 with asp.net. On the screen top left corner it says, Administrator. But then why cannot I change the previllges?
Thanks,
I am using SQL server. I am trying to make one of the columns as an autogenerate one. But when I go to do that (Is Indentity), it is not allowing me to put value to Yes. it's greyed out. I am new to the development of SQ server 2008. Is this method right? What else I have to do to make it autogenerated.
I am using Sql server 2008 with asp.net. On the screen top left corner it says, Administrator. But then why cannot I change the previllges?
Thanks,
ASKER
Thank you for your comment.
This table does not contain any data as of now. So I can add a new column as you suggested but how do I specify isIdentity as true or yes. I tried with one test col but again I cannot mark that col as Yes.
Also, do you mean by set identity_insert YourTableName on" and set identity_insert YourTableName off?
We do not have any DBA here. So please can you explain in detail?
This table does not contain any data as of now. So I can add a new column as you suggested but how do I specify isIdentity as true or yes. I tried with one test col but again I cannot mark that col as Yes.
Also, do you mean by set identity_insert YourTableName on" and set identity_insert YourTableName off?
We do not have any DBA here. So please can you explain in detail?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you talking about an automatically assigned value to a column for each insert ? then an identity column or a row GUID is the correct thing to do, but "autogenerate" is also a term often used in a datagridview in programming parlance, and is a different thing.
Brandon has given you the code for an Identity column that will start at 1 and increment by 1 for each new inserted row. For the other definition for autogenerate, please have a quick look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=650217&SiteID=1
and you might like to describe what you need in a bit more detail...
Brandon has given you the code for an Identity column that will start at 1 and increment by 1 for each new inserted row. For the other definition for autogenerate, please have a quick look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=650217&SiteID=1
and you might like to describe what you need in a bit more detail...
ASKER
Mark and Brandon,
I a, talking about SQL server autogenerate colums and not gridview autogenrate.
I tried what Bradon suggested-
alter table [person]
Add [person_id] int identity(1,1)
I already have person_id col in the table and table has no data.
It gives an error column name in each table must be unique. Col name person_id in table person is specified mora than once.
I just have one col call ed person_id in person. What I am doing wrong?
sql-error.jpg
I a, talking about SQL server autogenerate colums and not gridview autogenrate.
I tried what Bradon suggested-
alter table [person]
Add [person_id] int identity(1,1)
I already have person_id col in the table and table has no data.
It gives an error column name in each table must be unique. Col name person_id in table person is specified mora than once.
I just have one col call ed person_id in person. What I am doing wrong?
sql-error.jpg
Then drop the table, then add it. You will have to add any indexes or constraints back on.
also, you should do it with SQL Server Management Studio, not Vis Web Dev.
ASKER
Worked!!
ASKER
Brandon,
Your query worked!!
Thanks,
But now I have another problem. My another webform inserts person_id columns from a text file. Since this column is autogenerated, that programe is failing saying that it has to be OFF. Is there any way that I can work this up?
Your query worked!!
Thanks,
But now I have another problem. My another webform inserts person_id columns from a text file. Since this column is autogenerated, that programe is failing saying that it has to be OFF. Is there any way that I can work this up?
Say again. Your other thing wants to insert the person_id. You really shouldn't have it both ways. You either do or don't want identity.
>> Since this column is autogenerated, that programe is failing saying that it has to be OFF <<
does not sound like identity, sounds like autogenerate ;)
does not sound like identity, sounds like autogenerate ;)
Either that, or you are maintaining multiple tables, and have to apply the same identity type process to you "people" table as well...
ASKER
Thank you all for commenting
I am working with my business reqs and will come up with the different design. I wanted that when text file gets inserted along with other table, it will insert in Person table as well , that way I could query the Person table and see all the person id taken. I know it's an autogenerate col, but did not know that I will not be able to add into this. If while inserting text file, if there is any duplicate entry into person table, I wanted to throw an error.
Anyways thanks all.
I am working with my business reqs and will come up with the different design. I wanted that when text file gets inserted along with other table, it will insert in Person table as well , that way I could query the Person table and see all the person id taken. I know it's an autogenerate col, but did not know that I will not be able to add into this. If while inserting text file, if there is any duplicate entry into person table, I wanted to throw an error.
Anyways thanks all.
Now. I can't GUARANTEE that you can update an identity with identity insert, but it would seem to make sense that you could. I have only ever used it to "INSERT" into the identity column.