table adaptes and scope_identity()

[Rain]

Hi,

Im having a small problem. I have created an insert function which works
fine and dandy. In order to get the new row id I did the following.


Insert BLAH BLAH BLAH ;
select scope_identity();


Now I set the return type to scalar, but it insists on returning the value
1, which is the number of rows affected. Can anyone tell me what I need to
do in order to get the value without having to resort to creating another
select function to obtain it.


Thanks

 

[Rain]

Dont worry I fixed it. I had created an insert query, but left the name the
same. Insert. So the designer appears to ignore the changes you make if the
name of your query happens to be the same as the four standard ones. As soon
as I created a new one called InsertReturnId, everything worked, just one
small issue, I had to cast the return value in the sql statement first.
Cast( scope_identity() as int ).

Apart from that it works like a dream now..

Table adapters are great as long as you know the the pitfalls I think :|

Cheers

 

[Göran Andersson]

Hi,

Im having a small problem. I have created an insert function which works
fine and dandy. In order to get the new row id I did the following.


Insert BLAH BLAH BLAH ;
select scope_identity();


Now I set the return type to scalar, but it insists on returning the value
1, which is the number of rows affected. Can anyone tell me what I need to
do in order to get the value without having to resort to creating another
select function to obtain it.


Thanks

Your result contains to sets of data. The first set comes from the
insert query, it's empty and just contains the number of records
affected. The second set is from the select query.

To keep the insert from returning a result, do this first:

set nocount on