locked
Use "output Inserted.ID" in SqlDataSource RRS feed

  • Question

  • User-1555682044 posted

    i wanna use the vale of Inserted.ID in this Insert SqlDataSource Insert Command "INSERT INTO [SubMenu] ([Menu_ID], [Title], [Name]) output Inserted.ID VALUES (@Menu_ID, @Title, @Name)".

    i dont want use Scop_Identity.

    now, i declare  <asp:Parameter Name="InsertedID" Direction="Output" Type="Int32"/> in insertParameters, but dident work.

    Plz Help :(

    Friday, June 17, 2011 7:09 AM

Answers

  • User-1555682044 posted
    Alter	Procedure	Person_Save
    @FirstName nVarchar(200) ,
    @LastName nVarchar(200) ,
    @ReturndID BigInt out
    As Begin

    Declare @T Table(Name nVarchar(200))

    Insert Into Person(FiratName , LastName)
    Output Inserted.ID Into @T
    Values(@FirstName , @LastName )

    Select Top(1)
    @ReturndID = Name
    From @T
    End
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 3, 2011 1:51 PM

All replies

  • User-269404413 posted

    behoorz,

    try this code

    string query = "AddCategory";
    
    int ID;
    
    string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
    
    using (SqlConnection conn = new SqlConnection(connect))
    
    {
    
      using (SqlCommand cmd = new SqlCommand(query, conn))
    
      {
    
        cmd.CommandType = CommandType.StoredProcedure;
    
        cmd.Parameters.AddWithValue("@Category", Category.Text);
    
        cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID");
    
        cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;
    
        conn.Open();
    
        cmd.ExecuteNonQuery();
    
        ID = (int)cmd.Parameters["@CategoryID"].Value;
    
      }
    
    }
    
    
    also refer this page too
    Friday, June 17, 2011 7:14 AM
  • User-1555682044 posted

    i khow that, but i used SqlDataSource and SqlDataSource.Insert() methoud.

    Friday, June 17, 2011 7:17 AM
  • User-269404413 posted

    i think you didnt refered the link which i gave you. check this link

    Friday, June 17, 2011 8:11 AM
  • User-1555682044 posted

    in that link use SCOPE_IDENTITY() instade of "Output Inserted.ID".

    i need use "output Inserted" in insert stetment.

    Friday, June 17, 2011 8:49 AM
  • User-269404413 posted

    behrooz,

    try this

    Saturday, June 18, 2011 3:29 AM
  • User3866881 posted

    i wanna use the vale of Inserted.ID in this Insert SqlDataSource Insert Command "INSERT INTO [SubMenu] ([Menu_ID], [Title], [Name]) output Inserted.ID VALUES (@Menu_ID, @Title, @Name)".

    i dont want use Scop_Identity.

    now, i declare  <asp:Parameter Name="InsertedID" Direction="Output" Type="Int32"/> in insertParameters, but dident work.

    Plz Help :(

    Hello:)

    Please make sure that this should be stored procdure, something like this:

    create stored procdure XXX

    @Menu_ID int,

    @Title varchar(max),

    @Name varchar(max),

    @InsertedID int output,

    As

    INSERT INTO [SubMenu] ([Menu_ID], [Title], [Name]) output Inserted.ID VALUES (@Menu_ID, @Title, @Name)

    set @InsertedID = value

    And in the page you can call:

    SqlDataSource1.InsertParameters["Menu_Id"].DefaultValue = xxxx;

    ………………………………

    SqlDataSource1.Insert();

    SqlDataSource1.InsertParameters["InsertedID"].DefaultValue;   //Get the output value

    Monday, June 20, 2011 9:43 PM
  • User-1555682044 posted

    "set @InsertedID = value"

    in this line, value is not Tsql object or variable.

    i try this, but i cant creat stored procedure with "set @InsertedID = value",

    * column ID is Identity.

    create Procedure        InsertedID
    @Name nVarchar(250) ,
    @Content nVarchar(max) ,
    @InsertedID int output
    As Begin
    INSERT INTO [Page] (Name , Content)
    output Inserted.ID
    VALUES (@Name , @Content)

    set @InsertedID = value
    End
    
    
    -------------------------------
    MSSQL error : Invalid column name 'value'.
    
    
    if possible, please explane me more.
    Saturday, July 30, 2011 11:52 AM
  • User3866881 posted

    Hello again:)

    Here the "value" isn't itself. I mean "value" is the latest value of your inserted record's ID.

    You can try like this——

    select @InsertedId= top 1 from xxx order by Id desc

    If your primary key is identitied from 1 step by 1.

    Saturday, July 30, 2011 9:41 PM
  • User-1555682044 posted

    Hello

    this way is work, but , between run insert and Select Top or other Tsql that get last inserted item, meybe other prcess or machine run insert procedure, so Select Top statemen, get me value of other new item.

    if i want use this approch, i must lock Stored procedure. 

    Sunday, July 31, 2011 1:10 AM
  • User3866881 posted

    Hello

    this way is work, but , between run insert and Select Top or other Tsql that get last inserted item, meybe other prcess or machine run insert procedure, so Select Top statemen, get me value of other new item.

    if i want use this approch, i must lock Stored procedure. 

    Hello:)

    It seems that maybe you are running multi-thread app……

    You can use something like lock(this){……}to lock each thread and do inserting, and get the result.

    Thx again

    Sunday, July 31, 2011 4:05 AM
  • User-1555682044 posted
    Alter	Procedure	Person_Save
    @FirstName nVarchar(200) ,
    @LastName nVarchar(200) ,
    @ReturndID BigInt out
    As Begin

    Declare @T Table(Name nVarchar(200))

    Insert Into Person(FiratName , LastName)
    Output Inserted.ID Into @T
    Values(@FirstName , @LastName )

    Select Top(1)
    @ReturndID = Name
    From @T
    End
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 3, 2011 1:51 PM