table adaptes and scope_identity()

  • Thread starter Thread starter Rain
  • Start date Start date
R

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
 
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
 
Rain said:
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
Click to expand...

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
 
Back
Top