Identity Column Not Returnd After AddNew / Update With ADO And SQL 2005
Jun 12, 2006
Hi,
I add new records to a table with ADO. The tables contain an auto-increment identity column. I want to retrieve the identity value after the insert operation. This works fine for SQL Server 2000. On SQL Server 2005 this only works if I use a table in the select statement. If I use a view in the select statement, ADO returns no value for the identity column, a trace with profiler shows that there is no Select @@IDENTITY statement.
What is the reason for this behavior?
How can I change this behavior in SQL 2005 so that the behavior is the same as in SQL Server 2000?
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
i got a little problem. when i insert values into a table a trigger is launched wich updates something in the table too.
CREATE TRIGGER T1 ON Tbl FOR INSERT AS BEGIN IF (UPPER(USER) <> 'CONVERSIE') BEGIN UPDATE Tbl SET Datum = GetDate(), User = USER, WHERE Tbl.ID= (SELECT ID FROM inserted) END END
the error i receive is this: 'can not update identity column id' (id being pk).
I have a frustrating problem occuring in Sql 2005. We have the need in a few client upgrade situations to update the indentity column of a common resource table. It is populated each release with common records and the keys got out of sync for two releases due to developer error.
In Oracle this is not a problem. In SQL we get the message "Cannot update identity column 'mycolumn'". To get around this in SQL 2000 we updated the table by running the following.
exec sp_configure 'allow update', 1 go reconfigure with override go update syscolumns set colstat = colstat - 1 where id = object_id(Event') and name = 'EventKey' go exec sp_configure 'allow update', 0 go reconfigure with override go
In SQL 2005 this gives us the error
'Ad hoc updates to system catalogs are not allowed'.
Now I understand the need to keep people out of the system tables. However, can I have some sort of solution to this problem. I'm running a bunch of ad hoc queries to update the values and I don't want to create an exact duplicate of the table and copy over the information with the changes. It is much easier to run a simple update.
I set up replication on my DB between two server. The Publisher will not update tables on the Subscriber that use an identity column and I get an error "8102:cannot update identity column". I have looked around and can't seem to locate any solid fixes. If I remove the identity column from the subscribers tables the package works, but I am not able to use the DB on the subscriber because I need the Identity columns for my app to function properly. My Publisher and Subscriber are both running SQL Server 2000 Standard Edition SP3.
I'd set up Transactional Replication btw 2 servers running on MSSQL 2000.
However, from time to time, I encounter Error 8102. I try to reinitialise the subscription but failed. After I delete the current subscription and PUSH a new one, it will works fine again.
I'm curious why this is happening as I'd already set all the tables (articles) in the subscriber to "YES, not_for_replication".
We need to import data from flat/xml files into our database. we plan to do so in bulk as amount of data is huge, 2GB+ we need to do some validation checks in our code after that we create insert queries.
We have identity columns that are used as foreign keys in child tables. Question is how can I write a bulk/batch insert statement that will propogate the identity column to the child, as for all other we are creating the query in the application memory. there are 2 parent tables and 1st table value needs to be referred to in 7 tables and second table's value in 6.
The application is running ADO (MDAC) version 2.81.1117.0.
The application gets an recordset created by SQLServer by an select statement. In this case the recordset is empty.
The application adds a record to the recordset by AddNew(). Works fine. But when assigning the first field (smalldatetime in database) I get ADO error -2147217887.
I have inspected the recordset and I found a difference between the working one from SQLServer2000 and the new one from SQLServer2005: The Attributes item for the fields in the recordset has the flag adFldUnknownUpdatable set in the working SQL2000 version but is not set from SQL2005.
The database on SQL2005 is copied from the SQL2000 system and attatched to the SQL2005 system. I Have checket that the compability flag for the database on the SQL2005 system is set to SQL2000 compability.
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8 DECLARE @DetailQuestionID INT = 1380 /*------- I need the query to run for the below three data. Here i'm updating my planids that already exists in my database*/ DECLARE @planids VARCHAR(MAX) = '2,4,5'
Hi I am trying to use the identity data type ( column) I am using examples from the book and management studio in sql server 2005. I am using the identity property for the customer Id in customers table.I accidentatly executed the querry twice and I had two same record with different customer id numbers of 1 and 2 . I realised the problem and I had to delete the second record. The problem is now even if I have deteted the 2nd record with customer id 2 , when I insert a new record the identity value ( customer Id) increments with a number after the value I deleted. i.e if I deleted a second row with customer id 2 ( identity 2) when I enter a new record it enters with a customer id of 3 and whenever i add a new record it increments from there. So instead of first record have cust id 1 and second record customer id 2 etc , I get first record with cust id 1 , second record with cust id 3 , third record cust id 4 etc. How can I get rid of this wrong values of identity values whenever I delete a record and try to add a new record?
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic. Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
Hello, I have the following problem with MS SQL and ASP: When adding a new code with the code below I need to get the automatically generated ID. I know there are threads discussing this but I have found none which discuss it when using the rs.AddNew method. I don't think "SELECT @@IDENTITY" works for me. Here's the code:
Code:
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorType = 2 rs.LockType = 3
StrSQL = "SELECT * FROM table1"
rs.Open strSQL, objConn rs.Addnew
rs("Field1") = "Value1" rs("Field2") = "Value2"
rs.Update
rs.Movelast RowID = rs("ID")
rs.Close
I though the rs.Movelast will work and it did most of the time, but I found that it does not work ALL of the time. What happens is that sometimes a record may be added before the last record in the table and "RowID" would actually contain that records ID. So I need your help. Thanks
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
Hi,We have been using ADO and the AddNew method for a long time as a meansto add records to the database. It has always worked fine - no problem.But - we recently started using INSERT triggers that simply call a fewstored procs (they're actually SSNS stored procs that send new eventinfo to notification services). Anyway, these triggers do not seem tofire at all! If I execute an insert command manually from QueryAnalyser, no problems. But the trigger does not fire at all from myapplication!Does anyone know why this could be? For info, my connection string usedby the ADO connection object looks like this: Provider=SQLOLEDB.1;DataSource=XXX;Initial Catalog=YYYAnd my AddRecord ADO code looks like this:With rs.Open sSQL, ConnectionString, adOpenKeyset, adLockOptimistic,adCmdTable And adExecuteNoRecords.AddNewAm I mnissing something obvious here? Any help appreciated!
I'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is : Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be created where the referencing column 'child_table.child_id' is an identity column.
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
Hello; My Memebership table has Guid column as Primary key. But I would like to add Auto numbering Identity column to this table. Is this idea OK or it will bring some problems? Thank you in advance for your help
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY
I am working on a project to consolidate 3 distinct buildings name code(6 digit character called BUILDING_CODE Primary Key) into one building code. This is an effort to merge/move everyone who is currently coded under the three building codes into one building code. We currently have this column defined on 5 tables as primary key (none identity) in SQL Server 2005.
Out of the five tables one has two store procedure dependencies.
What would be the best practice to update the six digit BULDING_CODE column Primary Key value without causing any issues?
SET @new_identity = @@IDENTITY SET @new_link = @item_link + @new_identity
UPDATE items SET item_link = @new_link WHERE item_id = @new_identity
END GO
Basically, what I am trying to do is this...
IF the item type is article, insert normally... which works fine...
however, if the item time is mediaItem, insert part of the item_link... (everything minus id.. eg: site.com/items.aspx?item_id=)... then once the row has been inserted, update that row, to make the link site.com/items.aspx?item_id=<new id>
however, when the sql runs the mediaItem code, it leaves the item_link field blank.
I have been trying to get the new sol_id that is added so I can use it in other code, but it is only returning 0. The new record is getting added to the table, but my varaiable myNewSolID is 0 in my response.write(myNewSolID) I appeaciate any help! Here is my steored procedure and my code: ALTER PROCEDURE [dbo].[AddTrackings]
@DropDate datetime, @Comment nvarchar(100), @DateEntered datetime, @EnteredBy nvarchar(50), @Sol_ID Int OUTPUT AS INSERT INTO tblTrackings (DropDate, Comment, DateEntered, EnteredBy)
Dim ConnectStr As String = _ ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString
Dim MySQL As String = ""
If Me.chkDropSave.Checked Then MySQL = "AddTrackings" Dim MyConn As New SqlConnection(ConnectStr) Dim Cmd As New SqlCommand(MySQL, MyConn) Cmd.CommandType = CommandType.StoredProcedure Dim InsertedInteger As New SqlParameter("@Sol_ID", SqlDbType.Int) InsertedInteger.Direction = ParameterDirection.Output Cmd.Parameters.Add(InsertedInteger)
Dim MySqlParamStart As New SqlParameter("@DropDate", SqlDbType.DateTime) Cmd.Parameters.Add(MySqlParamStart) MySqlParamStart.Value = Me.BasicDatePickerDropDate.SelectedValue
Dim MySqlParamAmtTo As New SqlParameter("@Comment", SqlDbType.NVarChar, 100) Cmd.Parameters.Add(MySqlParamAmtTo) MySqlParamAmtTo.Value = Me.txtComment.Text
