locked
using SqlDatASource programmatically - output parameters RRS feed

  • Question

  • User706013099 posted

    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:

    ALTER PROCEDURE [dbo].[InsertAlbum]
    (@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)
    AS
    DECLARE
    @err int
    INSERT
    INTO dbo.ALBUMS (ARTISTID, TITLE)
    VALUES (@ArtistID, @Title)
    SELECT @err = @@error IF @err <> 0 RETURN @err
    SET @NewID = SCOPE_IDENTITY()

    This works fine when run from Sql Server Management Studio and @NewID has the correct value.

    My data access code is roughly as follows:

    dsrc = New SqlDataSource()
    dsrc.ConnectionString = ConnectionString
    dsrc.InsertCommand =
    "InsertAlbum"
    dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedure
    Dim parms As ParameterCollection = dsrc.InsertParameters
    Dim newid As Integer
    AddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)
    AddParameter(parms,
    "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)
    Dim p As New Parameter("NewID", TypeCode.Int32)
    p.Direction = ParameterDirection.Output
    parms.Add(p)
    Try
       Dim rv As Integer = dsrc.Insert()
       newid = parms("NewID")
       Return newid
    Catch ex As Exception
       
    Return -1
    End Try

    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

     

    Saturday, May 13, 2006 2:48 AM

All replies

  • User706013099 posted

    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?

     

     

    Saturday, May 13, 2006 12:09 PM
  • User1224304770 posted
    It seems that the SqlParameter can't be added to SqlDataSource.InsertCommand,  I'm not sure. But why not use SqlCommand to call the stored procedure? It works fine in my testing
    Monday, May 15, 2006 5:45 AM
  • User942311321 posted

    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 

    Friday, March 9, 2007 2:02 PM
  • User1199920040 posted

    awesome.  thanks for posting this.

    Friday, July 13, 2007 5:41 PM
  • User-259515800 posted

    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

    Wednesday, September 19, 2007 6:46 AM
  • User-881099725 posted

    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.

    Friday, September 21, 2007 9:03 AM
  • User401578957 posted

    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="original_ZoneID" Type="Int32" />

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

    <asp:Parameter Name="MSG" Type="string" Direction="Output" Size="4000" /></UpdateParameters>

    And on my Code behind(yes..it is in VB.Net)

    Protected Sub SqlDataSource1_Updated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Updated

    Dim Msg as String 

    msg = e.Command.Parameters(
    "@Msg").Value

    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

    ) AS

    Begin

    Begin Try

    BEGIN Tran

    UPDATE ZoneMst

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

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

    WHERE ZoneID=@original_ZoneID

     

    COMMIT

    Set @Msg = 'Record Updated Successfully'

    End try

    Begin Catch

    <some code deleted>

     

    Monday, March 10, 2008 3:08 AM
  • User1477435862 posted

    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;
    	}
    }
    
    Monday, October 27, 2008 1:05 PM
  • User1553129408 posted

    Thank you so Much You literally saved my ass. I was about to Switch to SQLDataAdapters, Massive code refactoring, now I can continue using SQLDatasources. Which are so easy to use.

    Wednesday, March 25, 2020 2:57 PM