I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues.

The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key:

The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value.

Has anyone tried to do this and can tell me what I am doing wrong?

Jon

After further investigation it seems that the only way to pick up the output parameters is to add an Inserted event handler in my DAL and use this to save the parameter value from SqlDataSourceStatusEventArgs.Command.Parameters into a local variable in my DAL which can then be returned and referenced in a webpage code-behind event handler for my ObjectDataSource Inserted event. Just seems a bit long-winded (and possibly not thread safe). Why couldn't the output parameter value be put in the SqlDataSource InsertParameters collection?

I finally figured this out, thanks to the help of a co-worker. . .. and figured I would post it here, since this is the first thread I found relating to the subject.... (sqldsProjects is my sqldatasource)

Im working with my sqldatasource in the code behind. On my projects.aspx page, I have a gridview with the list of products. It has a add project area, which it only adds a project title, then it redirects to the projectdetails.aspx page to complete the rest of the information and then it can be updated.

     //grab the output parameter value and assign it to a variable in my page class.[int idProject;]
    protected void sqldsProjects_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        idProject = (int)((IDbDataParameter)e.Command.Parameters["idProject"]).Value;
    }

    // You insert the output parameter in the Inserting event
    protected void sqldsProjects_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter();
        param.DbType = DbType.Int32;
        param.Direction = ParameterDirection.Output;
        param.ParameterName = "idProject";

        e.Command.Parameters.Add(param);
    }

A button click event triggers a save - -

 protected void btnAddProject_OnClick(object sender, EventArgs e)
    {
        if (!String.IsNullOrEmpty(txtProject.Text.Trim()))
        {
            sqldsProjects.InsertCommand = "InsertProject";
            sqldsProjects.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;

            sqldsProjects.InsertParameters.Add("fldActive", TypeCode.Boolean, "False");
            sqldsProjects.InsertParameters.Add("fldFeatured", TypeCode.Boolean, "False");
            sqldsProjects.InsertParameters.Add("fldName", TypeCode.String, txtProject.Text.Trim());
            sqldsProjects.InsertParameters.Add("fldLocation", TypeCode.String, string.Empty);
            sqldsProjects.InsertParameters.Add("fldOwner", TypeCode.String, string.Empty);
            sqldsProjects.InsertParameters.Add("fldCmpDate", TypeCode.DateTime, DBNull.Value.ToString());
            sqldsProjects.InsertParameters.Add("fldDesc", TypeCode.String, string.Empty);
            sqldsProjects.InsertParameters.Add("fldStatus", TypeCode.String, "0");

            sqldsProjects.Insert();

            Response.Redirect("projectdetails.aspx?id=" + idProject.ToString() );
        }
    } 

 --Stored procedure

ALTER PROCEDURE dbo.InsertProject
    (
    @fldActive bit,
    @fldFeatured bit,
    @fldName varchar(50),
    @fldLocation varchar(50),
    @fldOwner varchar(30),
    @fldCmpDate DateTime,
    @fldDesc text,
    @fldStatus varchar(20),
    @idProject int output
    )
AS
    SET NOCOUNT ON --stops the server from indicating the number of rows affected
    INSERT INTO tblProjects
    (fldActive, fldFeatured, fldName, fldLocation,
     fldOwner, fldCmpDate, fldDesc, fldStatus)
    VALUES
    (@fldActive, @fldFeatured, @fldName, @fldLocation,
     @fldOwner, @fldCmpDate, @fldDesc, @fldStatus)

SET @idProject = Scope_Identity()

Hope this helps anyone else looking for this info. I spent too much time on it... lol 

awesome.  thanks for posting this.

Hi,

I am having a similar issue but I have to read the data from the sqldatabase by a PK field and to display on an asp.net form which I could possibily achieve by sqldatasource and detailview control.  I could also use your coding to insert data to another table once user clicked a button.  But the issue is to read the data from the detailview. I am having difficulty to read the data from the sqldatasource / detailview.  Appreciate if you could provide the coding to read directly from sqldatabase and display it in a text box.

thanks

Thanks for sharing this as I was having the same problem. I guess it is one of those things the team overlooked. I tried by adding the parameters to the InsertParameter collection of the source in design mode but when I tried to retrieve them in the Inserted handler i got an exception saying that parameter was not in the collection.

I then used your approach to programmatically add the Output and ReturnValue parameters to the collection during the Inserting event and now I am able to retrieve these values on the Inserted event.

The problem is - and I figure that is a framework bug- you can only retrieve those. The parameters that are declared in the collection at design time (can be seen in the control markup on the ASPX/ASCX) are not accessible on the Inserted event using this method. So basically now I can access the Return and Output I added programmatically during Insert but not those that were already declared. Very weird.

I was trying the same thing and come across a very wired conclusion. I can define an output parameter in my aspx page too as it is said in thread that one cant. It works perfectly. When its type is "String", you must provied a size for it. Or it will give a run time error.

The wired thing is, this size cant be greater than 4000. else you will return a null value from your stored procedure. I am using SQL 2005 which allow me to define string with size of 8000. But in aspx you can only give it upto 4000. It's wired? isnt it?

any ways. This is what i'd done:

<UpdateParameters>

<asp:Parameter Name="ZoneCode" Type="String" Size="3"/>

End Sub

  and my stored Procedure is:

Alter PROCEDURE uspUpdateZoneMst

@original_ZoneID INT,

@ZoneCode VARCHAR(20),

@ZoneName VARCHAR(250),

@SF VARCHAR(20),

@UBy INT,

@Msg varchar(100) output

Begin Try

UPDATE ZoneMst

SET ZoneCode=Ltrim(@ZoneCode),ZoneName=Ltrim(@ZoneName),

SF=@SF,UpdatedBy=@UBy,Updatedon=GetDate()

Set @Msg = 'Record Updated Successfully'

End try

Begin Catch

<some code deleted>

Thanks Sean, this helped me find a solution for my problem:  I have a SqlDataSource inside a Repeater ItemTemplate, which retrieves data for another, nested Repeater.  The output parameter is used in the OnItemDataBound method of the nested Repeater.  The problem that I had was how to store the value relevant for each RepeaterItem without some complex storage/matching value solution. As it turned out, it was quite simple.

The object passed into the SqlDataSourceStatusEventHandler is a SqlDataSourceView and, unfortunately, there isn't anyway of programatically accessing the original SqlDataSource, however I had a guess that, if I changed the DefaultValue of the appropriate parameter in the view object, the DefaultValue of the source control would be changed.  This proved to be true.  That then meant that I could then reference the source control from inside the OnItemDataBound method and retrieve the correct output value. For example:

// ItemDataBound EventHandler for outer Repeater 
protected void rptSections_ItemDataBound(object Sender, RepeaterItemEventArgs e)
{
	if(e.Item.ItemType.Equals(ListItemType.Item) || e.Item.ItemType.Equals(ListItemType.AlternatingItem))
	{
		SqlDataSource ds = e.Item.FindControl("sqlCategories") as SqlDataSource;
		ds.SelectParameters["Section"].DefaultValue = ((DataRowView) e.Item.DataItem)["ID"].ToString();
		ds.Selected += new SqlDataSourceStatusEventHandler(sqlCategories_Selected);
	}
}

//SqlDataSource.Select EventHandler 
void sqlCategories_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
	((SqlDataSourceView) sender).SelectParameters["UserGuides"].DefaultValue = e.Command.Parameters["@UserGuides"].Value.ToString();
}

// ItemDataBound EventHandler for inner Repeater 
protected void rptCategories_ItemDataBound(object src, RepeaterItemEventArgs e)
{
	if(e.Item.ItemType.Equals(ListItemType.Item) || e.Item.ItemType.Equals(ListItemType.AlternatingItem))
	{
		SqlDataSource ds = ((Control) src).Parent.FindControl("sqlCategories") as SqlDataSource;
		DataRowView v = e.Item.DataItem as DataRowView;
		HyperLink hl = e.Item.FindControl("hlCategory") as HyperLink;
		hl.NavigateUrl = string.Format("Category.aspx?id={0}&ug={1}", v["ID"], ds.SelectParameters["UserGuides"].DefaultValue);
		hl.Text = v["Category"] as string;
	}
}