May 28, 2011 at 7:41 pm
So I changed the identity increment to yes, I even put set identity_insert <tablename> on in my sqlcommand statement that is in my program and set it back to off at the end of the statement.
For whatever reason, the ID of the first insert is 0 and since the auto increment isn't working, I couldn't add another course to the table without removing the primary key from ID.
So now I can add new courses to the table... but the ID for all of them is 0.
Identity Increment and Identity Seed are both set to 1.
Any ideas of what's wrong?
May 28, 2011 at 10:00 pm
OK so I have to ask...is the identity property for the column set to Yes?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 28, 2011 at 11:05 pm
Yes it is. 🙁 Otherwise the option to set the increment and seed are disabled.
May 29, 2011 at 2:57 am
talismc (5/28/2011)
So I changed the identity increment to yes, I even put set identity_insert <tablename> on in my sqlcommand statement that is in my program and set it back to off at the end of the statement.For whatever reason, the ID of the first insert is 0 and since the auto increment isn't working, I couldn't add another course to the table without removing the primary key from ID.
So now I can add new courses to the table... but the ID for all of them is 0.
Identity Increment and Identity Seed are both set to 1.
Any ideas of what's wrong?
- You only need to use identity_insert if you want to load data and specify the identity columns value yourself.
- for now, can you run DBCC CHECKIDENT ( table_name ) ?
http://msdn.microsoft.com/en-us/library/ms176057.aspx
this will check the current identity value for the specified table and, if it is needed, changes the identity value to function again properly. an identity property will not detect which values are already in use, so this dbcc is needed.
have a look at http://vyaskn.tripod.com/sql_server_check_identity_columns.htm
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 29, 2011 at 3:17 am
set identity_insert <tablename> on basically means you're providing the identity value within your INSERT statement (making sure there are no duplicate or NULL values).
With set identity_insert <tablename> off you don't provide the identity value and leave it to SQL Server to add that value.
Slightly off topic: if you need to know what identity values are assigned, you could use the OUTPUT clause instead of requery the table.
May 29, 2011 at 3:42 am
Table definition (the create table statement) and the insert statement please
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 29, 2011 at 5:55 pm
Thanks guys, this is helping me to understand sort of what's going on.
So I tried taking out the SET IDENTITY_INSERT ON and OFF from my sqlcommand statement in my vb.net program but when I try to add a new record to my sql table, it returns the error... "Cannot insert explicit value for identity column in table 'Courses' when IDENTITY_INSERT is set to off.".
So if I don't adjust this in my program to do it (because I want the sql database to add the incremented id value for me, every time) how do I get it to add properly?
I can tell from the error message that it isn't the vb code, it is in fact an issue with my table.
P.S. I didn't hardcode the table, I created it using SQL Server Management Studio.
May 29, 2011 at 5:59 pm
When I executed DBCC CHECKIDENT <tablename> is just gives me the error Incorrect Syntax.
May 29, 2011 at 6:01 pm
Sorry lutz, you may need to dumb that down for me.
What's the OUTPUT clause?
May 29, 2011 at 6:39 pm
GilaMonster (5/29/2011)
Table definition (the create table statement) and the insert statement please
Here, I wrote a statement to create a new table to see if it would work but I still have the same problem.
USE aip
CREATE TABLE Course
(
ID int NOT NULL IDENTITY,
Name nvarchar(30) NOT NULL,
Abbrev nvarchar(10) NOT NULL,
Details nvarchar(500),
Start_date datetime NOT NULL,
Start_time nvarchar(10) NOT NULL,
Expiry_date datetime NOT NULL,
Instructor nvarchar(50),
Type char(10) NOT NULL,
Company nvarchar(50),
Cost money NOT NULL,
PRIMARY KEY (ID)
)
Old insert statement in my program:
myCommand.CommandText = "SET IDENTITY_INSERT Courses ON; INSERT INTO Courses " & _
"(ID, Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost) VALUES (@courseId, @courseName," & _
" @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost); SET IDENTITY_INSERT Courses OFF;"
New statement without setting the identity insert:
myCommand.CommandText = "INSERT INTO Course " & _
"(ID, Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost) VALUES (@courseId, @courseName," & _
" @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost);"
May 29, 2011 at 11:42 pm
When you have an identity, it must not be specified as part of the insert statement, as SQL handles the value. Change your insert statement to
INSERT INTO Course (Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost)
VALUES (@courseName, @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost);
Some other advice. Store the date and time (start_date and start_time) in a single column of type datetime. It simplifies any date manipulation and means that you don't have to worry about date validation (which you currently do)
NVarchar? Are you expecting unicode data? If not, make the columns varchar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2011 at 12:57 am
talismc (5/29/2011)
GilaMonster (5/29/2011)
Table definition (the create table statement) and the insert statement pleaseHere, I wrote a statement to create a new table to see if it would work but I still have the same problem.
USE aip
CREATE TABLE Course
(
ID int NOT NULL IDENTITY,
Name nvarchar(30) NOT NULL,
Abbrev nvarchar(10) NOT NULL,
Details nvarchar(500),
Start_date datetime NOT NULL,
Start_time nvarchar(10) NOT NULL,
Expiry_date datetime NOT NULL,
Instructor nvarchar(50),
Type char(10) NOT NULL,
Company nvarchar(50),
Cost money NOT NULL,
PRIMARY KEY (ID)
)
Old insert statement in my program:
myCommand.CommandText = "SET IDENTITY_INSERT Courses ON; INSERT INTO Courses " & _
"(ID, Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost) VALUES (@courseId, @courseName," & _
" @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost); SET IDENTITY_INSERT Courses OFF;"
New statement without setting the identity insert:
myCommand.CommandText = "INSERT INTO Course " & _
"(ID, Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost) VALUES (@courseId, @courseName," & _
" @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost);"
Here i have provided example for you,Please check it out and let me know.
--Example 1
--First Let us declare variable that will help you to understand code
DECLARE @courseId INT ,
@courseName NVARCHAR(30) ,
@courseAbbrev NVARCHAR(10) ,
@courseDetails NVARCHAR(500) ,
@courseSDate DATETIME ,
@courseSTime NVARCHAR(10) ,
@courseEDate DATETIME ,
@courseInstructor NVARCHAR(50) ,
@courseType CHAR(10) ,
@courseCompany NVARCHAR(50) ,
@courseCost MONEY
--Setting variable Value
--we have set @courseId=1 i assume that you want to insert this value in Column ID manualy in Course table
SET @courseId = 5
SET @courseName = 'Learn C and C++'
SET @courseAbbrev = 'C&C++'
SET @courseDetails = 'Starting of C and C++ Basic Topic covered'
SET @courseSDate = 2011 - 05 - 30
SET @courseSTime = '10:00 AM'
SET @courseEDate = 2011 - 06 - 30
SET @courseInstructor = 'Mikel Cren'
SET @courseType = 'Basic'
SET @courseCompany = 'McGraw'
SET @courseCost = 200
--Set identity insert on (this will enable identity insert for user)
SET IDENTITY_INSERT dbo.Course ON
INSERT INTO dbo.Course
( ID ,
Name ,
Abbrev ,
Details ,
Start_date ,
Start_time ,
Expiry_date ,
Instructor ,
Type ,
Company ,
Cost
)
VALUES ( @courseId ,
@courseName ,
@courseAbbrev ,
@courseDetails ,
@courseSDate ,
@courseSTime ,
@courseEDate ,
@courseInstructor ,
@courseType ,
@courseCompany ,
@courseCost
)
--Set identity insert OFf (this will enable identity insert OFF for user,SQL server now on handles Identity)
SET IDENTITY_INSERT dbo.Course OFF
GO
--Example 2
--now Assume that you want from the fist handle identity column by sql server then
--you don't need to On and OFF identity again and again,Please
--USE SET IDENTITY_INSERT dbo.Course OFF
--First Let us declare variable that will help you to understand code
--we don't need to declare @courseId as SQL sever going to handle it self.
DECLARE
@courseName NVARCHAR(30) ,
@courseAbbrev NVARCHAR(10) ,
@courseDetails NVARCHAR(500) ,
@courseSDate DATETIME ,
@courseSTime NVARCHAR(10) ,
@courseEDate DATETIME ,
@courseInstructor NVARCHAR(50) ,
@courseType CHAR(10) ,
@courseCompany NVARCHAR(50) ,
@courseCost MONEY
--Setting variable Value
--we have set @courseId=1 i assume that you want to insert this value in Column ID manualy in Course table
SET @courseName = 'Learn C#'
SET @courseAbbrev = 'C#'
SET @courseDetails = 'Starting of C# Basic Topic covered'
SET @courseSDate = 2011 - 05 - 30
SET @courseSTime = '11:00 AM'
SET @courseEDate = 2011 - 06 - 30
SET @courseInstructor = 'Mikel Cren'
SET @courseType = 'Basic'
SET @courseCompany = 'McGraw'
SET @courseCost = 300
--Set identity insert OFf (this will enable identity insert OFF for user,SQL server now on handles Identity)
--Please use this Option if u have set Identity status on
SET IDENTITY_INSERT dbo.Course OFF
INSERT INTO dbo.Course
( Name ,
Abbrev ,
Details ,
Start_date ,
Start_time ,
Expiry_date ,
Instructor ,
Type ,
Company ,
Cost
)
VALUES ( @courseName ,
@courseAbbrev ,
@courseDetails ,
@courseSDate ,
@courseSTime ,
@courseEDate ,
@courseInstructor ,
@courseType ,
@courseCompany ,
@courseCost
)
May 31, 2011 at 7:41 am
Take the ID and @courseid values out of your insert statement. By having them there, you are attempting to give it specific values. Skip that value and start with name. It will automatically create the new value for the identity column.
Bob L
June 2, 2011 at 4:48 pm
GilaMonster (5/29/2011)
When you have an identity, it must not be specified as part of the insert statement, as SQL handles the value. Change your insert statement to
INSERT INTO Course (Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost)VALUES (@courseName, @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost);
Some other advice. Store the date and time (start_date and start_time) in a single column of type datetime. It simplifies any date manipulation and means that you don't have to worry about date validation (which you currently do)
NVarchar? Are you expecting unicode data? If not, make the columns varchar.
I can't because the company will be specifying a time for when this course will commence. It's not about what time the course was created, it's about when the company will be instructing their students.
As for validating the date, I've just included some code to restrict improper format.
I appreciate what you're saying Gila Monster, but this isn't the problem that I am having. My code works well and is done accordingly to my clients specifications.
My problem is that my SQL database is not auto-incrementing.
June 2, 2011 at 4:51 pm
Gaurang-Patel (5/30/2011)
talismc (5/29/2011)
GilaMonster (5/29/2011)
Table definition (the create table statement) and the insert statement pleaseHere, I wrote a statement to create a new table to see if it would work but I still have the same problem.
USE aip
CREATE TABLE Course
(
ID int NOT NULL IDENTITY,
Name nvarchar(30) NOT NULL,
Abbrev nvarchar(10) NOT NULL,
Details nvarchar(500),
Start_date datetime NOT NULL,
Start_time nvarchar(10) NOT NULL,
Expiry_date datetime NOT NULL,
Instructor nvarchar(50),
Type char(10) NOT NULL,
Company nvarchar(50),
Cost money NOT NULL,
PRIMARY KEY (ID)
)
Old insert statement in my program:
myCommand.CommandText = "SET IDENTITY_INSERT Courses ON; INSERT INTO Courses " & _
"(ID, Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost) VALUES (@courseId, @courseName," & _
" @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost); SET IDENTITY_INSERT Courses OFF;"
New statement without setting the identity insert:
myCommand.CommandText = "INSERT INTO Course " & _
"(ID, Name, Abbrev, Details, Start_Date, Start_Time, Expiry_Date, Instructor, Type, Company, Cost) VALUES (@courseId, @courseName," & _
" @courseAbbrev, @courseDetails, @courseSDate, @courseSTime, @courseEDate, @courseInstructor, @courseType, @courseCompany, @courseCost);"
Here i have provided example for you,Please check it out and let me know.
--Example 1
--First Let us declare variable that will help you to understand code
DECLARE @courseId INT ,
@courseName NVARCHAR(30) ,
@courseAbbrev NVARCHAR(10) ,
@courseDetails NVARCHAR(500) ,
@courseSDate DATETIME ,
@courseSTime NVARCHAR(10) ,
@courseEDate DATETIME ,
@courseInstructor NVARCHAR(50) ,
@courseType CHAR(10) ,
@courseCompany NVARCHAR(50) ,
@courseCost MONEY
--Setting variable Value
--we have set @courseId=1 i assume that you want to insert this value in Column ID manualy in Course table
SET @courseId = 5
SET @courseName = 'Learn C and C++'
SET @courseAbbrev = 'C&C++'
SET @courseDetails = 'Starting of C and C++ Basic Topic covered'
SET @courseSDate = 2011 - 05 - 30
SET @courseSTime = '10:00 AM'
SET @courseEDate = 2011 - 06 - 30
SET @courseInstructor = 'Mikel Cren'
SET @courseType = 'Basic'
SET @courseCompany = 'McGraw'
SET @courseCost = 200
--Set identity insert on (this will enable identity insert for user)
SET IDENTITY_INSERT dbo.Course ON
INSERT INTO dbo.Course
( ID ,
Name ,
Abbrev ,
Details ,
Start_date ,
Start_time ,
Expiry_date ,
Instructor ,
Type ,
Company ,
Cost
)
VALUES ( @courseId ,
@courseName ,
@courseAbbrev ,
@courseDetails ,
@courseSDate ,
@courseSTime ,
@courseEDate ,
@courseInstructor ,
@courseType ,
@courseCompany ,
@courseCost
)
--Set identity insert OFf (this will enable identity insert OFF for user,SQL server now on handles Identity)
SET IDENTITY_INSERT dbo.Course OFF
GO
--Example 2
--now Assume that you want from the fist handle identity column by sql server then
--you don't need to On and OFF identity again and again,Please
--USE SET IDENTITY_INSERT dbo.Course OFF
--First Let us declare variable that will help you to understand code
--we don't need to declare @courseId as SQL sever going to handle it self.
DECLARE
@courseName NVARCHAR(30) ,
@courseAbbrev NVARCHAR(10) ,
@courseDetails NVARCHAR(500) ,
@courseSDate DATETIME ,
@courseSTime NVARCHAR(10) ,
@courseEDate DATETIME ,
@courseInstructor NVARCHAR(50) ,
@courseType CHAR(10) ,
@courseCompany NVARCHAR(50) ,
@courseCost MONEY
--Setting variable Value
--we have set @courseId=1 i assume that you want to insert this value in Column ID manualy in Course table
SET @courseName = 'Learn C#'
SET @courseAbbrev = 'C#'
SET @courseDetails = 'Starting of C# Basic Topic covered'
SET @courseSDate = 2011 - 05 - 30
SET @courseSTime = '11:00 AM'
SET @courseEDate = 2011 - 06 - 30
SET @courseInstructor = 'Mikel Cren'
SET @courseType = 'Basic'
SET @courseCompany = 'McGraw'
SET @courseCost = 300
--Set identity insert OFf (this will enable identity insert OFF for user,SQL server now on handles Identity)
--Please use this Option if u have set Identity status on
SET IDENTITY_INSERT dbo.Course OFF
INSERT INTO dbo.Course
( Name ,
Abbrev ,
Details ,
Start_date ,
Start_time ,
Expiry_date ,
Instructor ,
Type ,
Company ,
Cost
)
VALUES ( @courseName ,
@courseAbbrev ,
@courseDetails ,
@courseSDate ,
@courseSTime ,
@courseEDate ,
@courseInstructor ,
@courseType ,
@courseCompany ,
@courseCost
)
Thanks gurang but I want the ID field to be populated by the database itself. I was told before that setting the identity_insert to on (as I was originally doing) was telling the database that I would be setting the ID value. =/
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply