none
SqlDataSource insert: How to get values from a SQL Command? RRS feed

  • Question

  • Hi there,

     

    I'm using a SqlDataSource to connect to my database, an then using its Insert() method to write data. However, when I insert it, I want to get the SCOPE_IDENTITY() value, because I need the ID from the last inserted item.

     

    If I were using the SqlCommand approach, that would be easier. But I have no clue how can I get rows from a Insert() method. Anybody helps me?

     

    Thanks.

     

    Sunday, October 19, 2008 2:34 PM

Answers

  • Here is the general ideia:
    Code Snippet

    InsertCommand = "INSERT INTO Foo(Bar) VALUES(@value); Select @myID = @@Identity;"


    Add an additional parameter to the InsertParameters of the SQLDataSource:

    Code Snippet

    <InsertParameters>
    <asp:Parameter Name="name" Type="type"/>
    <asp:Parameter Direction="output" Name="myID" Size=4 Type=int64/>
    </InsertParameters>


    Then:
    Code Snippet
    protected void sqldsProjects_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
    myID = (long)((IDbDataParameter)e.Command.Parameters["myID"]).Value;
    }





    Sunday, October 19, 2008 4:02 PM

All replies

  • Here is the general ideia:
    Code Snippet

    InsertCommand = "INSERT INTO Foo(Bar) VALUES(@value); Select @myID = @@Identity;"


    Add an additional parameter to the InsertParameters of the SQLDataSource:

    Code Snippet

    <InsertParameters>
    <asp:Parameter Name="name" Type="type"/>
    <asp:Parameter Direction="output" Name="myID" Size=4 Type=int64/>
    </InsertParameters>


    Then:
    Code Snippet
    protected void sqldsProjects_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
    myID = (long)((IDbDataParameter)e.Command.Parameters["myID"]).Value;
    }





    Sunday, October 19, 2008 4:02 PM
  • Thanks, Hermano. As always, you were helpful and concise. Just one thing:
    myID = (long)((IDbDataParameter)e.Command.Parameters["myID"]).Value;

    Should looks like:

    myID = (long)((IDbDataParameter)e.Command.Parameters["@myID"]).Value;

    The rest went just fine.


    Monday, October 20, 2008 11:52 AM