Last post Oct 06, 2009 09:58 PM by Naom
Mar 12, 2009 02:55 PM|mulch|LINK
My app was working fine and now SELECT SCOPE_IDENTITY() is always returning 1 from my DataSet. I don't get any errors, just always returns 1.
Here is the code:
INSERT INTO [dbo].[Families] ([LastName], [FirstName], [Address], [Telephone], [EmailAddress], [GuestChildren], [HasCreditCard], [HasVehicle], [RespiteNameAgeGender], [Relationship], [RespiteCentreID], [Comments], [DateEntered], [DateUpdated], [AdminID])
VALUES (@LastName, @FirstName, @Address, @Telephone, @EmailAddress, @GuestChildren, @HasCreditCard, @HasVehicle, @RespiteNameAgeGender, @Relationship, @RespiteCentreID, @Comments, @DateEntered, @DateUpdated, @AdminID);
Dim familyTableAdapter As New TableAdapters.FamiliesTableAdapter
Dim intFamilyID As Integer = familyTableAdapter.InsertFamily(txtLastName.Text, txtFirstName.Text, txtAddress.Text, txtTelephone.Text, txtEmailAddress.Text, txtGuestChildren.Text, drpHasCreditCard.SelectedValue, drpHasVehicle.SelectedValue, txtRespiteChild.Text,
txtRelationship.Text, drpRespiteCentre.SelectedValue, txtComments.Text, Now(), Now(), 1)
Am I missing something?
Mar 12, 2009 03:42 PM|mudassarkhan|LINK
Mar 12, 2009 07:01 PM|Naom|LINK
Instead of the using ReturnValue, which you forgot to assign
(e.g. in your case you need
I suggest to use stored procedure and return new ID through the Output parameter instead of the return value.
Mar 14, 2009 09:55 AM|TATWORTH|LINK
SQL Books online defines:
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
Therefore Naom's answer of RETURN SCOPE_IDENTITY() is the best one.
Please click the "Mark As Answer" against Naom's reply.
Oct 06, 2009 09:53 PM|Temgee|LINK
Since the other posts didn't really answer the question directly ... I just ran into this, and after much back and forth I realized that the execute mode for the query has to be SCALAR .. if it's set to NON QUERY it returns the number of rows affected instead,
which in this case would always be 1.
Oct 06, 2009 09:58 PM|Naom|LINK
Good point - I think this post also deserves a star.