Unable to get the Identity column value for new row in MSSQL database.
-
I am inserting a new row in a database table (MSSQL). I want to retrieve the value of Identity column associated with 'this' new row. My stored procedure is as follows :
ALTER PROCEDURE dbo.Insert
(
@SubCategoryId tinyint,
@text nvarchar(MAX),
@Url nvarchar(2000)
)
AS
SET NOCOUNT ON;
INSERT INTO Table1
(SubCategoryId, text, Url)
VALUES (@SubCategoryId,@text,@Url)SELECT NEWID = SCOPE_IDENTITY()
Now, I have a table-adapter in which i have any Insert function which uses the above stored procedure. From the code-behind, i am inserting the data.
DataSetTableAdapters.Table1TableAdapter t1 = new DataSetTableAdapters.Table1TableAdapter();
int result = t1.Insert((byte)int.Parse(ddlSubCategory.SelectedValue), txtText.Text, txtUrl.Text);The row is inserted in the database, but the returned value is not the Identity column ... Is the stored procedure incorrect ? I posted this in ASP.NET forum, because i want to do this in ASP.NET, i found the stored procedure on a database related site, still the stored-procedure is not working ...
Apurv “Never trust a computer you can’t throw out a window.” (Steve Wozniak) “There are only two industries that refer to their customers as ‘users’.” (Edward Tufte)
-
I am inserting a new row in a database table (MSSQL). I want to retrieve the value of Identity column associated with 'this' new row. My stored procedure is as follows :
ALTER PROCEDURE dbo.Insert
(
@SubCategoryId tinyint,
@text nvarchar(MAX),
@Url nvarchar(2000)
)
AS
SET NOCOUNT ON;
INSERT INTO Table1
(SubCategoryId, text, Url)
VALUES (@SubCategoryId,@text,@Url)SELECT NEWID = SCOPE_IDENTITY()
Now, I have a table-adapter in which i have any Insert function which uses the above stored procedure. From the code-behind, i am inserting the data.
DataSetTableAdapters.Table1TableAdapter t1 = new DataSetTableAdapters.Table1TableAdapter();
int result = t1.Insert((byte)int.Parse(ddlSubCategory.SelectedValue), txtText.Text, txtUrl.Text);The row is inserted in the database, but the returned value is not the Identity column ... Is the stored procedure incorrect ? I posted this in ASP.NET forum, because i want to do this in ASP.NET, i found the stored procedure on a database related site, still the stored-procedure is not working ...
Apurv “Never trust a computer you can’t throw out a window.” (Steve Wozniak) “There are only two industries that refer to their customers as ‘users’.” (Edward Tufte)
It's still a SQL question. Does 'I found the stored proc' mean you don't really understand what it does ? I suspect your core issue is probably that the table adapter 's insert method probably doesn't return the result of the proc, but a value such as the number of rows changed. Hard to say, you don't say what value you're getting back, but SCOPE_IDENTITY() will return that new id, so I don't see what the issue could be. The reason to use this is, it will return the id that your proc created, if you added SQL to find the highest id, and it was called by two users at once, you'd have a race condition.
Christian Graus Driven to the arms of OSX by Vista.
-
It's still a SQL question. Does 'I found the stored proc' mean you don't really understand what it does ? I suspect your core issue is probably that the table adapter 's insert method probably doesn't return the result of the proc, but a value such as the number of rows changed. Hard to say, you don't say what value you're getting back, but SCOPE_IDENTITY() will return that new id, so I don't see what the issue could be. The reason to use this is, it will return the id that your proc created, if you added SQL to find the highest id, and it was called by two users at once, you'd have a race condition.
Christian Graus Driven to the arms of OSX by Vista.
Christian Graus wrote:
Does 'I found the stored proc' mean you don't really understand what it does ?
not 100% true. Actually I am not good at writing the stored procedures.
Christian Graus wrote:
the table adapter 's insert method probably doesn't return the result of the proc
this could be the issue ... But my task is straight forward, I have to first Insert a row in one table, then using the value of the Identity column of the created row, some rows are to have inserted into other table where, the primary-key in this table is Foreign key in the other table. What should be the approach to this problem ?
Apurv “Never trust a computer you can’t throw out a window.” (Steve Wozniak) “There are only two industries that refer to their customers as ‘users’.” (Edward Tufte)