troubleshooting Question

Typed dataset INSERT command return value: data row or boolean result of insertion?

Avatar of kkamm
kkammFlag for United States of America asked on
Visual Basic.NETMicrosoft SQL Server
6 Comments2 Solutions1139 ViewsLast Modified:
This is an extension of a previously answered question. I thought I had the solution, but got sidetracked on return values.

I am trying to get the value of the unique identifier(autogenerated) for newly inserted rows so that I can update memory-resident collections to reflect the newly inserted identity value.

 I am using typed datasets and I set up an insert command that contains code to return the newly inserted row via SCOPE IDENTITY. (See code window)

The problem I am having is that the command seems to be returning a numeric value, indicating the success of the operation, and not a row containing the field values I need.

If this does return a row, where can it be referenced?  
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'TestTableInsertQuery' AND user_name(uid) = 'dbo')
        DROP PROCEDURE dbo.TestTableInsertQuery
GO
 
CREATE PROCEDURE dbo.TestTableInsertQuery
(
        @PatID int,
        @Comments text
)
AS
        SET NOCOUNT OFF;
INSERT INTO TestTable
               (PatID, Comments)
VALUES  (@PatID,@Comments);
         
SELECT UniqueID, PatID, Comments FROM TestTable WHERE (UniqueID = SCOPE_IDENTITY())
GO
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros