SCOPE_IDENTITY() returning the ID value of an inserted record.

There are loads of postings on the net about this problem but none I have found explain the cause.

Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.

Has anyone come across the solution for this issue?

Thanks

Can you provide your code?|||

Here is the Stored Proc

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[InsertNewEnquiry_Client]

(

@.Salutation

varchar(10),

@.Name

varchar(60),

@.Address1

varchar(60),

@.Address2

varchar(60),

@.Address3

varchar(60),

@.Town

nvarchar(50),

@.PostCode

char(10),

@.County

char(60),

@.Telephone1

char(12),

@.Telephone2

char(12),

@.email

char(30)

)

AS

SETNOCOUNTOFF;

INSERT

INTO tblClient(Salutation,Name, Address1, Address2, Address3, Town, PostCode, County, Telephone1, Telephone2, email)

VALUES

(@.Salutation,@.Name,@.Address1,@.Address2,@.Address3,@.Town,@.PostCode,@.County,@.Telephone1,@.Telephone2,@.email);

SELECT

ClientID, Salutation,Name, Address1, Address2, Address3, PostCode, County, Telephone1, Telephone2, Telephone3, emailFROM tblClientWHERE(ClientID=SCOPE_IDENTITY())|||And can you show us the code which calls this stored procedure?|||int ClientID = Convert.ToInt32(clientTableAdapter.InsertStoredProc(cboClientSalutation.SelectedItem, txtClientName,text ......) );|||

The 1 your getting is most likely the default result of an Insert which is to return the number of records that have been entered.

I think you need to set the ExecuteMode property on your function to scalar.

|||

Actually, I've just looked at your sql again and your selecting an entire record at the end, not the identity on its own.

Simply put SELECT SCOPE_IDENTITY() if you want just the id returned, plus make sure you set the executemode on the function to scalar as I said before.

Hope that helps.