The wierd thing is this was working fine and then it just started throwing this error,
"Cannot insert explicit value for identity column in table 'Reports' when IDENTITY_INSERT is set to OFF."
Here is the code:
[Code]....
I have error:
Cannot insert explicit value for identity column in table 'UserDetails' when IDENTITY_INSERT is set to OFF.
I'm trying to insert a record through a BLL (Business Logic Layer) class with an 'Insert' button attached to the FooterTemplate of a GridView2. My code:
[Code]....
[Code]....
BLL layer code:
[Code]....
The field 'idUser' is autoincremented. When I use SqlDataSource with a DetailsView, add option it adds a record and autoincrements the 'isUser'.
Following the MVC1 vers of Nerddinner, but using 2010 and MVC2. Never seen this error. Looked in controller, model, view. Where does MVC pass the identity value, and how to stop it? I have never had to set IDENTITY_INSERT in any app before.
View 2 RepliesI have an insert table with identity specification set. And I getthis error. I'm using a stored procedure to store it. How do I correct it. I'm also using a details view on my insert page.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Insert' when IDENTITY_INSERT is set to OFF.
I am working on a web app for an online photo album. It is the last project in Scott Mitchell's book: "ASP.Net 2.0 in 24 hours".
I am creating a page where users can enter new photo images. The functionality for uploading an image will come later. I am working on just inserting a new row into the Pictures table with an optional category, a required title and a required description.
I am getting an error when I try to insert a new row.
Here is a screenshot of the page with a shot of the dropdown list.
The Categories are user-specific. I am getting the correct values retrieved.
[URL]
There are two pretty simple database tables involved here.
NOTE: The CategoryID and PIctureID are both autoincrement integer columns.
[URL]
The page uses a DetailsView that uses a SqlDataSource that uses the Pictures table. (The DetailsView's Default Mode property is set to "Insert". The "Enable Inserting" checkbox is also checked.)
The dropdown list uses a dropdown control that uses a SqlDataSource that uses the Categories table.
When the "Insert" button is clicked the CategoryID value associated with selected (Category) Name on the dropdown list will be used along with the Title and Description values to insert a row in to the Pictures table. (If no Category value is selected, then a null value will be used for the CategoryID. This is OK because the CategoryID column in the Pictures table allows nulls.)
My problem is that I am getting this error:
Cannot insert explicit value for identity column in table 'Pictures' when IDENTITY_INSERT is set to OFF.
(FYI: I have the full version of both Visual Studio and SQL Server.)
Here is my source code for the page:
[Code]....
im using sql server 2008
as per microsoft, [URL]
when i execute the following
set identity_insert on
//insert statements here
set identity_insert off
the identity of the column is set to the maximum value. can i aviod this.
consider the following scenario,
my table has 2 rows as follows
id, name comm
1, John, 232.43
2, Alex, 353.52
now using the above code, when i insert
10, Smith, 334.23
as per the above link, sql server automatically sets the identity to 10. so for newly inserted records(without using identity_insert on), id automatically starts with 11.
i want the identity value to be 3, after using identity_insert on/off
I have been searching on here for many hours and read many posts but for some reason I am being slow and cannot figure this out. I am writing a record and need to get the identity field back. I have tried many combinations based on what I have read but the below code is where I am sitting at now. it has appdatasource.ExecuteScalar() underlined and the error says 'ExecuteScalar' is not a member of 'System.Web.UI.WebControls.SQLDataSource'. I would be very grateful if someone could point out what I am missing to get this to work.
Protected Sub SubmitApp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitApp.Click
Dim appdatasource As New SqlDataSource
appdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString1").ToString
appdatasource.InsertCommandType = SqlDataSourceCommandType.Text
appdatasource.InsertCommand = "INSERT INTO APPLICATION (FirstName,MiddleName,LastName,AcceptTerms,IPv4Address,DateInserted) VALUES (@FirstName,@MiddleName,@LastName,@IPv4Address,@DateInserted);select @@SCOPE_IDENTITY"
appdatasource.InsertParameters.Add("FirstName", fname.Text)
appdatasource.InsertParameters.Add("MiddleName", minit.Text)
appdatasource.InsertParameters.Add("LastName", lname.Text)
appdatasource.InsertParameters.Add("DateInserted", DateTime.Now())
appdatasource.InsertParameters.Add("IPv4Address", Request.UserHostAddress.ToString)
Dim appid As Integer = appdatasource.ExecuteScalar()
End Sub
Resetting IDENTITY column to a new valueDuring application development we always entry some test data also our tester test with test data and as a result if the table contains IDENTITY columns( specially ID column) which incremented to the number of data already entered.
However, when the application goes running with real data to the client and the client want his ID values from starting 1, then we need to RESEED the value of the IDENTITY column of the table after clearing all data. This can be done as bellows:
DBCC CHECKIDENT (TableName, RESEED, 0)
However, can be reseed with any values instead of zero(0) in the above statement.
I have an identity column that increment by 1 on every insert. I have deleted all the rows using delete query but the identity coulumn is still not reseting to zero, how do i reset it, as it was when the table was first created.
View 4 RepliesI have created small database prods. I have created one table Products. I have given columns like productid, productname, price. after creating table. I got an idea to add identity to productid ie., auto increment. how to add identity to existing column.
View 5 RepliesThe scenario is that I wanna insert a record to the database via INSERT query and I need to user the identity number of this record in the INSERT query.
But I don't know how to achieve this.
Here is my table structure and code for your better understanding.
There are three columns in the Table: categoryID, categoryName, categoryPath. I need the path based on the categoryID which is an identity int column.
Now I used this code to do INSERT:
[Code]....
I have one table it's contain multiple columns and one of them is
[Call_Ref_No] [numeric](18, 0) IDENTITY(1,1) NOT NULL
In this column I am facing issue is some time its missing the identity value... For example
1
2
3
5
6
8
9
Here in this example 4 and 7 is missing...I could not able to find these two reference in my table so what I can do? How I can stop this occurrence.
I am trying to get the identity column on which the record is inserted but i couldn't using stored procedure
see
[Code]....
My Code Using ASP.NET C# i am trying to display the identity in textbox
[Code]....
How do I set my insert identity return value parameter in codebehind? I have working code now but i have all of my parameters set in codebehindexcept the following:
[Code]....
How can I check if IDENTITY_INSERT is on or off for a table in my DB?
View 4 RepliesI am in a serious trouble right now, i wanted to know that how to write a query in LINQ to SQL to retrieve identity column's current value before inserting any value.
I need this cos i m building a windows app n i need to display this value before inserting a record. I have tried DataContext.ExecuteCommand('DBCC CHECKIDENT (Tablename)') but it returns the number of rows modified.
i have created a web form, where i have 3 textboxes into a table and one submit button.
i would like to store values from the textboxes into my database (sql server) when i click the submit button.
but how to insert the value that is not in textbox into database which is primary key for table without identity column.
I create a table as picture below :
when I insert any character to column Name then A new string random will auto insert into column Random (picture below) I had used Trigger but It was error !
I want to column Random use to code :
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
insert into table_1 values(@myid, substring(CONVERT(varchar(255), @myid), 1, 5))
but It must auto like column Number (column Number is Identity)
How to handle this error
"An explicit value for the identity column in table 'UserActivation' can only be specified when a column list is used and IDENTITY_INSERT is ON."
I would like to insert a new column in my database table with a count of all of the records
How can I do this?
I am developing a asp.net application and i am using SQL Server 2008. I took a IDENTITY column as Record_ID for detail table where i will have trillions of records per year. So just want to ask whats the largest number record id (identity) column can hold and in ASP.NET which data type i should use to handle record id as i am using this id as a reference to update the table data. I don't want to end up being trapped some day.
View 1 RepliesI have an orders table with a column names orderID. What I want to do is insert a new record and get the value from the new orderID column. The orderID is my Primary key. The Insert SQL I have now is -
INSERT INTO orders
(creditAutorizarionNumber, OrderName, OrderPersonPhone, OrderPersonEmail)
VALUES (@creditAutorizarionNumber,@OrderName,@OrderPersonPhone,@OrderPersonEmail)
How do I write the query to get the newly entered orderID ?
This is my code so far:
SQL = "insert into " & tableNameToday _
& "(ID, Name, Calories, Of_Which_Fat, Protein, Carbohydrate, Of_which_sugars," _
& "Fat, Of_Which_Saturated, Of_Which_Monounsaturates, Of_Which_Polyunsaturates," _
[Code]....
how do i multiply the columns like this. for now static explanation would be ok but eventually i would like to make it dynamic so there would be a text box for users to enter the amout they like e.g 160g then the table would know to multiply the columns by 1.6.
I am trying to create a stored procedure where I can insert null values in columns that have a foreign key constraint and allow nulls. The columns I am trying to insert nulls into are commentid and imageid. I have created an Insert statement in a stored procedure and I get the foreign key constraint error. Can you tell me how to allow inserting data in the other columns without having to insert anything in the commendid and the imageid?
Here is my exec of the stored procedure that is not working:
[Code]....
[Code]....
I need to insert multiple records using a script.
My table is
Region
(
ID int,
Name varchar(50),
Description varchar(51)
)
One thing I can use is to retrieve the max(ID) from region and store it in a variable and increment the variable sequentially and insert. But, if any other user inserts a record while I am executing this script then max(id) might clash with the id inserted with other user causing my script to fail.
Is there any other option to insert record sequentially base on the highest number in the table.
