View Single Post
  #13 (permalink)  
Old November 6th, 2006, 07:12 PM
y2kstephen y2kstephen is offline
Registered User
 
Join Date: Nov 2006
Location: , , Hong Kong.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

A SOLUTION HERE! (hopefully it works for everyone...)

ok... firstly i had similar problems
the stored procedure OUTPUT parameter always return null
(i was trying to get the id of a new inserted row)

** code below are not completed, just trying to give u the main idea


so here is my table and stored procedure

/*--------------------------------------------*/

CREATE TABLE myItem(
    itemID INT PRIMARY KEY IDENTITY(1, 1),
    itemName VARCHAR(50)
)

/*--------------------------------------------*/
CREATE PROCEDURE itemCreate

@itemName VARCHAR(50),
@itemIdReturn INT OUTPUT

AS
BEGIN
    --create new record in 'myItem' table
    INSERT INTO myItem (itemName) VALUES (@itemName)

    --return new record 'itemID'
    SELECT @itemIdReturn = @@IDENTITY
END
GO

/*--------------------------------------------*/


and here is my site source code
just a textbox for input, a button for submit,
a label for the return id, and a sqlDataSource control

(this is the NOT WORKING version)

/*--------------------------------------------*/


        <asp:TextBox ID="txtItemName" runat="server"></asp:TextBox>&nbsp;<asp:Button ID="btnCreate"
            runat="server" Text="Create" OnClick="btnCreate_Click" /><br />
        <br />
        <asp:Label ID="lblIdReturn" runat="server"></asp:Label><br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="..."
            InsertCommand="itemCreate" InsertCommandType="StoredProcedure" ProviderName="System.Data.SqlClient">
            <InsertParameters>
                <asp:ControlParameter ControlID="txtItemName" Name="itemName" PropertyName="Text"
                    Type="String" DefaultValue="0" />
                <asp:ControlParameter ControlID="lblIdReturn" Direction="Output" Name="itemIdReturn"
                    PropertyName="Text" Type="Int32" DefaultValue="0" />
            </InsertParameters>
        </asp:SqlDataSource>

/*--------------------------------------------*/

and here is the code behind
just call the Insert() when submit button clicked

/*--------------------------------------------*/

    protected void btnCreate_Click(object sender, EventArgs e)
    {
        SqlDataSource1.Insert();
    }

/*--------------------------------------------*/


up to here...
supposingly, the label should return the new item id when the button clicked
(at least that what i think), but it always return null

and here is how i got it work !!!
just adding two things
add -- OnInserted ="On_Inserted" -- in the asp:SqlDataSource
and a fucntion call -- public void On_Inserted() -- in the code behind

so here is the WORKING version of the source and code behind
new added code highligthed in red

/*--------------------------------------------*/

        <asp:TextBox ID="txtItemName" runat="server"></asp:TextBox>&nbsp;<asp:Button ID="btnCreate"
            runat="server" Text="Create" OnClick="btnCreate_Click" /><br />
        <br />
        <asp:Label ID="lblIdReturn" runat="server"></asp:Label><br />
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="..."
            InsertCommand="itemCreate" InsertCommandType="StoredProcedure" ProviderName="System.Data.SqlClient" OnInserted ="On_Inserted">
            <InsertParameters>
                <asp:ControlParameter ControlID="txtItemName" Name="itemName" PropertyName="Text"
                    Type="String" DefaultValue="0" />
                <asp:ControlParameter ControlID="lblIdReturn" Direction="Output" Name="itemIdReturn"
                    PropertyName="Text" Type="Int32" DefaultValue="0" />
            </InsertParameters>
        </asp:SqlDataSource>

/*--------------------------------------------*/

using System.Data.Common; // <---- REMEMBER THIS
    protected void btnCreate_Click(object sender, EventArgs e)
    {
        SqlDataSource1.Insert();
    }

    public void On_Inserted(Object sender, SqlDataSourceStatusEventArgs e)
    {
        DbCommand command = e.Command;

        lblIdReturn.Text = command.Parameters["@itemIdReturn"].Value.ToString();
    }

/*--------------------------------------------*/


HOPEFULLY now you should be able to see the return id of the new inserted row in the label control
(at least i did... :))

hope this code is clear enuf to understand
cos i seldom post...

a reference to the Inserted thing...
http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted(V S.80).aspx

Reply With Quote