SQL Server, SQL Server Express, and SqlDataSource Control

Re: using SqlDatASource programmatically - output parameters

stevenbey — Mon, 27 Oct 2008 17:05:02 GMT

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;
	}
}

Re: using SqlDatASource programmatically - output parameters

Amit M — Mon, 10 Mar 2008 07:08:34 GMT

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:

<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

Re: using SqlDatASource programmatically - output parameters

degt — Fri, 21 Sep 2007 13:03:36 GMT

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.

Re: using SqlDatASource programmatically - output parameters

raj2006 — Wed, 19 Sep 2007 10:46:33 GMT

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

Re: using SqlDatASource programmatically - output parameters

jmsween — Fri, 13 Jul 2007 21:41:24 GMT

awesome. thanks for posting this.

Re: using SqlDatASource programmatically - output parameters

CSharpSean — Fri, 09 Mar 2007 19:02:32 GMT

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

Re: using SqlDatASource programmatically - output parameters

Iori_Jay — Mon, 15 May 2006 09:45:31 GMT

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

Re: using SqlDatASource programmatically - output parameters

jstranger — Sat, 13 May 2006 16:09:23 GMT

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?

using SqlDatASource programmatically - output parameters

jstranger — Sat, 13 May 2006 06:48:17 GMT

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