Getting Identity Back

Discussion in 'ASP .Net' started by Tina, Jan 6, 2007.

  1. Tina

    Tina Guest

    The following insert stored procedure was generated by the 2.0 tableadapter
    wizard (I have applied the latest service pack to vs2005)
    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'NewInsertCommand' AND
    user_name(uid) = 'dbo')
    DROP PROCEDURE dbo.NewInsertCommand
    GO

    CREATE PROCEDURE dbo.NewInsertCommand
    (
    @FranchiseID nvarchar(16),
    @PrimaryVendorID int,
    @Status nvarchar(15),
    @DateEntered datetime,
    @Customer nvarchar(50)
    )
    AS
    SET NOCOUNT OFF;
    INSERT INTO [Order] ([FranchiseID], [PrimaryVendorID], [Status],
    [DateEntered], [Customer]) VALUES (@FranchiseID, @PrimaryVendorID, @Status,
    @DateEntered, @Customer);

    SELECT FranchiseID, PrimaryVendorID, Status, DateEntered, Customer, OrderID
    FROM [Order] WHERE (OrderID = SCOPE_IDENTITY())
    GO

    If I execute this SP in SQL Server Management Studio it returns zero
    USE [mydb]
    GO

    DECLARE @return_value int

    EXEC @return_value = [dbo].[NewInsertCommand]
    @FranchiseID = N'19930601',
    @PrimaryVendorID = 1,
    @Status = N'Pending',
    @DateEntered = N'1/12/07',
    @Customer = N'myCustomer'

    SELECT 'Return Value' = @return_value

    GO

    I think it is supposed to return the fields from the new record. Why
    doesn't it? Does this wizard still not generate working stored procedures?

    Thanks,
    T
     
    Tina, Jan 6, 2007
    #1
    1. Advertisements

  2. Tina

    Mark Rae Guest

    Do you mean it returns zero records, or you it returns you a value of
    zero...?
     
    Mark Rae, Jan 7, 2007
    #2
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.