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?
Best regards,
George
View 21 Replies
ADVERTISEMENT
Jan 25, 2015
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?
View 6 Replies
View Related
Feb 5, 2001
hi,
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).
does anyone know how to deal with this?
tnx,
erik.
View 1 Replies
View Related
Dec 24, 2007
Hello,
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.
Please advise...
Thanks,
Russ
View 4 Replies
View Related
Dec 4, 2003
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.
Thanks.
View 4 Replies
View Related
Mar 1, 2004
Hi,
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".
Any idea?
Thanks.
View 4 Replies
View Related
Apr 17, 2007
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.
Thanks much for your help.
View 1 Replies
View Related
Jun 1, 2015
By mistake i deleted a record form prod table.
how can i enter that record back? i get this error.
INSERT INTO item_details(ItemID,Item_Name,price) VALUES(201, bag,10)
ItemID is  identity column. can i disable  identity column and enter and then enable using sql statement?
View 3 Replies
View Related
Mar 26, 2006
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.
Nils Nordenbrink
Winkonsult AB
Sweden
View 4 Replies
View Related
Sep 9, 2015
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.
View 4 Replies
View Related
Sep 9, 2015
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.
View 2 Replies
View Related
Jun 19, 2008
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
View 6 Replies
View Related
Jul 25, 2015
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'
[code].....
View 2 Replies
View Related
Jan 22, 2008
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?
Thanks
View 1 Replies
View Related
Aug 12, 2009
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'.
View 2 Replies
View Related
Aug 18, 2005
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."
View 1 Replies
View Related
Jan 9, 2005
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
View 6 Replies
View Related
Sep 19, 2005
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
View 2 Replies
View Related
May 8, 2006
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!
View 1 Replies
View Related
Aug 1, 2014
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
* I can't change anything on T
* The PK is NOT an Identity
View 4 Replies
View Related
Oct 16, 2006
Hi all,
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.
any workarounds for this ?
View 3 Replies
View Related
Sep 7, 2007
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;
View 18 Replies
View Related
Jan 9, 2007
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
View 3 Replies
View Related
Aug 3, 2006
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.
View 5 Replies
View Related
Mar 5, 2004
Hi,
I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.
I tried
alter table <table_name>
alter column <col_name> int Identity (1,1)
but it fails.
Ajay
WORD4LIFE
(http://www.word4life.com)
View 2 Replies
View Related
Jul 20, 2005
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
View 3 Replies
View Related
Apr 2, 2007
Hi,
I have two tables table1 and new_table
Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .
And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.
This type of functionality is requirement for my project.
I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;
Please help me out to fix this issue
Thanks
Purnima
View 3 Replies
View Related
Aug 29, 2014
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?
View 1 Replies
View Related
Dec 17, 2001
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
View 4 Replies
View Related
May 19, 2004
I want to add a record and then get the automatically generated identifier(ReqChildID in this case). Is there a better way? Why won't this one work.
This is what I have:
rsRec.AddNew
rsRec("RequisitionID") = intReqNum
rsRec("CreatedBy") = cint(session("empno"))
rsRec("DateCreated") = Now
rsRec.Update
intItemId = rsRec("ReqChildID")
rsRec.Close
Forgive me if this has already been posted, but I couldn't find it.
[edit to add:]
rsRec is an ADODB.Recordset in case that wasn't clear.
View 10 Replies
View Related
Oct 25, 2007
hey everyone, I have the following SQL:
CREATE PROCEDURE [dbo].[sp_InsertItem]
@item_channel_id INT, @item_title VARCHAR(75), @item_link VARCHAR(75),
@item_description VARCHAR(150), @item_long_description VARCHAR(1000),
@item_date DATETIME, @item_type VARCHAR(20)
AS
IF (@item_type = 'article')
BEGIN
INSERT INTO items (
item_channel_id, item_title, item_link, item_description, item_long_description,
item_date, item_type
) VALUES (
@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
@item_date, @item_type
)
END
IF (@item_type = 'mediaItem')
BEGIN
DECLARE @new_identity INT
DECLARE @new_link VARCHAR(100)
INSERT INTO items (
item_channel_id, item_title, item_link, item_description, item_long_description,
item_date, item_type
) VALUES (
@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
@item_date, @item_type
)
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.
Why is this doing this?
Thanks all!
View 2 Replies
View Related
Nov 8, 2007
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)
VALUES (@DropDate,@Comment,@DateEntered,@EnteredBy)
SET @Sol_ID=SCOPE_IDENTITY()
RETURN
Sub ProcessTracking()
Dim myNewSolID As Integer
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
Cmd.Parameters.AddWithValue("@DateEntered", Now())
Cmd.Parameters.AddWithValue("@EnteredBy", Profile.UserName)
Try
MyConn.Open()
myNewSolID = Cmd.ExecuteScalar()Response.Write(myNewSolID)
Catch ex As Exception
Response.Write(ex.Message)
Finally
MyConn.Close()
End Try
Else
End If
End Sub
View 8 Replies
View Related
Apr 24, 2008
Hi to all, Using sql query i want to check whether the column is Identity column or not? Please members reply me.
View 2 Replies
View Related
