| Author |
Topic |
|
shirlyxu
Starting Member
5 Posts |
 Posted - 2008-12-28 : 17:45:53
|
| Why does I can't find the property for IDENTITY on the list of data type in SQL server management studio 2005 express when I use design to create new table. If I type identity in data type, I get the message "invalid data type."If I create the new table using the script to set the identity(1,1), CREATE TABLE register ( user_id int identity(1,1), Name char(50) )after I execute it, the identity(1,1)disappear automatically.Thanks for any help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
shirlyxu
Starting Member
5 Posts |
Posted - 2008-12-28 : 19:50:26
|
| I want this column of user_id to be auto generated and auto increment( making it a primary key and setting the Identity Column to yes/true). But I still can't find it in list of data type. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-29 : 00:34:37
|
| did you try inserting records to table after you created it by above statement. it should work fine. |
|
|
|
jasKoenig
Starting Member
1 Post |
Posted - 2008-12-29 : 05:11:20
|
| First of all IDENTITY is not a datatype so u can't select the same within the table designer from DataType column, but instead, specify the appropriate datatype, lets say int is the Data Type and then, go to the column properties ( just below the table designer) -> Identity Specification -> Make IsIdentity = true. Thats It. |
|
|
|
shirlyxu
Starting Member
5 Posts |
Posted - 2008-12-30 : 12:21:42
|
| Maybe I'm not clear to explain my question.May I ask which specify of the appropriate datatype can I use to auto generated and increment for user_ID (set it a primary key)?I carefully check every different datatype in datatype drop down list of column , but I still can't find it.I have used the uniqueidentifier,but it can't auto increment. Thank you for your reply. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 12:27:29
|
quote:
Originally posted by shirlyxu
Maybe I'm not clear to explain my question.May I ask which specify of the appropriate datatype can I use to auto generated and increment for user_ID (set it a primary key)?I carefully check every different datatype in datatype drop down list of column , but I still can't find it.I have used the uniqueidentifier,but it can't auto increment. Thank you for your reply.
for making unique identifier fields auto incrementing with sequential values, you should define a default constraint on them using NEWSEQUENTIALID() function. this will cause them to be filled with sequential values automatically for each insertion. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-30 : 13:03:32
|
| yeah...i have heard integer is better choice as GUIDs need more size and can cause storage memory problems. Also they will not perform much in range queries.And i have always preferred to use integer identity columns |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-12-30 : 14:33:11
|
>>May I ask which specify of the appropriate datatype can I use to auto generated and increment for user_ID (set it a primary key)?Just for the sake of completeness - From Books Online:quote:
The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns.
>>after I execute it, the identity(1,1)disappear automatically.It didn't "disappear". You may not see the identity property in object explorer, try scripting the table to a new query window and you will see the identity property in the resulting code.Be One with the OptimizerTG |
|
|
|
shirlyxu
Starting Member
5 Posts |
Posted - 2008-12-30 : 16:54:03
|
| I don't know what is wrong in my code, the forum_ID still can't be incremented automatically. When I run code,only the data of one row can be inserted into table. There is no error message when adding second one. The code is below:alter PROCEDURE [dbo].[sp_forumNewUser] @forum_ID bigint,@forum_DiscussID bigint, @forumUsername varchar(50), @forumEmail varchar(50), ASBEGIN SET NOCOUNT ON; DECLARE @MaxRegID bigint SELECT @MaxRegID = max(forum_ID) from forumRegistration SET @MaxRegID = @MaxRegID+ 1 IF(Select countbig(1) from forumRegistration where forum_ID = @forum_ID)>0 BEGIN UPDate forumRegistration set forumUsername = @forumUsername where forum_ID = @MaxRegID END ELSE BEGIN insert into forumRegistration(forum_ID,forum_DiscussID,forumUsername,forumEmail)values(@forum_ID,@forum_DiscussID,@forumUsername,@forumEmail) ENDEND |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2008-12-30 : 22:40:34
|
| Identity is not a datatype, You can implement identifier columns by using the IDENTITY property. This enables the developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers. When values are inserted into a table that has an identifier column, the SQL Server 2005 Database Engine automatically generates the next identity value by adding the increment to the seed |
|
|
|
shirlyxu
Starting Member
5 Posts |
Posted - 2008-12-31 : 15:17:32
|
| Thank you everyone for helping me.I was confused the uniqueidentifier datatype with IDENTITY property, I understand it now.I have deleted forum_ID from INSERT command as Tkizer said, but the data only can be inserted into the table once, and it doesn’t work on the second time. I don’t know what value can use for (auto generated and increment) Forum_ID in cmd.Parameters.AddWithValue("@Forum_ID", -1); I use -1,it is not right.Happy New Year! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
prpball
Starting Member
19 Posts |
Posted - 2008-12-31 : 16:06:54
|
| Are you just trying to insert a row into the column and have the forum_id be incremented each time?Have you set the user_id Identity to Yes?Go to SQL Server Management Studio and right click on the table and choose modify it. Click the column name and look at the Column Properties. Scroll down to Identity Specification and click the plus sign next to it. Choose (is Identity) = Yes and set increment and speed to 1.Anytime you insert date into the table you will leave off the user_id field name and SQL will manage it for you.For instance:insert into forumRegistration(forum_DiscussID,forumUsername,forumEmail)values(@forum_DiscussID,@forumUsername,@forumEmail)This statement will only run once unless you call it more than once. |
|
|
|
prpball
Starting Member
19 Posts |
Posted - 2008-12-31 : 16:08:22
|
| Don't pass any parameter for form_id. Completely ignore it in your code. |
|
|
|
pkj1383
Starting Member
3 Posts |
Posted - 2009-01-01 : 08:11:25
|
| Hi,Please avoid the insertion in FormID column and check property of that column its set as Identity yes or not.Prashant Joshi |
|
|
|
|
no property for IDENTITY in SQL server express