One of the often asked questions on ASP.NET forum I visit is how to change SQLDataSource parameter. I know two common solutions to this problem, though I only tried one in my own web-pages.

I’ve just learned about another interesting solution which is presented in Peter Kellner’s blog. See also another blog discussing this approach.

The solution I’m familiar with and used is to set the parameter in Selecting (Inserting / Updating for Insert / Update) events of SQLDataSource. Also if we want to retrieve an Output parameter of stored procedure, for example, we can use the Inserted (Updated / Deleted) events of SQLDataSource.

This is a sample of retrieving an output parameter’s value in Inserted event of SQLDataSource

C#
1
2
3
4
5
6
7
8
9
#region DataSource Inserted
    protected void DataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        if (e.Command.Parameters["@NewPersonID"].Value != DBNull.Value)
        { this.NewPersonID = Convert.ToInt32(e.Command.Parameters["@NewPersonID"].Value); }
        else
        { this.NewPersonID = 0; }
    }
    #endregion
#region DataSource Inserted
    protected void DataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
    {
        if (e.Command.Parameters["@NewPersonID"].Value != DBNull.Value)
        { this.NewPersonID = Convert.ToInt32(e.Command.Parameters["@NewPersonID"].Value); }
        else
        { this.NewPersonID = 0; }
    }
    #endregion

where SQLDataSource definition in ASPX page looks like

 <asp:SqlDataSource runat="server" ID="PeopleNoneDataSource" ConnectionString="<%$ ConnectionStrings:FCCMSConnectionString %>"
                InsertCommand="PersonInsert" InsertCommandType="StoredProcedure" OnInserted="DataSource_Inserted">
                <InsertParameters>
                    <asp:Parameter Name="NewPersonID" Direction="Output" Type="Int32" />
                    <asp:ProfileParameter Name="SiteID" PropertyName="SiteID" Type="Int32" />
                    <asp:QueryStringParameter Name="PersonType" QueryStringField="ptype" Type="Char"
                        DefaultValue="N" />
                    <asp:Parameter Name="Title" />
                    <asp:Parameter Name="Occupation" />
                    <asp:Parameter Name="BusinessName" />
                    <asp:Parameter Name="BusAdd1" />
                    <asp:Parameter Name="BusAdd2" />
                    <asp:Parameter Name="BusCity" />
                    <asp:Parameter Name="BusState" />
                    <asp:Parameter Name="BusZip" />
                    <asp:Parameter Name="BusEMail" />
                    <asp:Parameter Name="Pager" />
                    <asp:Parameter Name="Fax" />
                    <asp:Parameter Name="BusinessPhone" />
                    <asp:Parameter Name="SpouseFN" />
                    <asp:Parameter Name="SpouseLN" />
                    <asp:Parameter Name="SpouseID" />
                    <asp:Parameter Name="Anniversary" />
                </InsertParameters>
            </asp:SqlDataSource>

Same code by limno for VB.NET (see ASP.NET forum’s thread)

vb.net
1
2
3
4
        
 Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As   System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
  Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@ContactID").Value.ToString()) + "<br/>")   
 End Sub  
        
 Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As   System.Web.UI.WebControls.SqlDataSourceStatusEventArgs)
  Response.Write("Record Inserted: " + Server.HtmlEncode(e.Command.Parameters("@ContactID").Value.ToString()) + "<br/>")   
 End Sub  

Here is the sample I found of setting parameter in Inserting event

C#
1
2
3
4
5
6
7
8
9
#region SQL DataSource Inserting - setting parameters
 
    protected void DataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
   
        e.Command.Parameters["@PersonType"].Value = ViewState["PersonType"];//this.PersonType ;
        e.Command.Parameters["@SiteID"].Value = ConfigurationManager.AppSettings["SiteID"]; 
    }
    #endregion
#region SQL DataSource Inserting - setting parameters

    protected void DataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
   
        e.Command.Parameters["@PersonType"].Value = ViewState["PersonType"];//this.PersonType ;
        e.Command.Parameters["@SiteID"].Value = ConfigurationManager.AppSettings["SiteID"]; 
    }
    #endregion

Alternative method of setting parameter’s value is to use DefaultValue of parameter, like this

vb.net
1
2
3
Protected Sub FormView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles FormView1.ItemUpdating
        SqlDataSource2.UpdateParameters("DateCompleted").DefaultValue = Now()
    End Sub
Protected Sub FormView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles FormView1.ItemUpdating
		SqlDataSource2.UpdateParameters("DateCompleted").DefaultValue = Now()
	End Sub

The above solution was used by Don Freeman and shared with UniversalThread members.

Here is a link demonstrating usage of the first method

http://forums.asp.net/t/1395687.aspx

I would also like to give a link to a pure ADO.NET solution for retrieving Identity field value after insert (without using SQLDataSource on the page)
Getting ID of the newly inserted record in SQL Server Database using ADO.Net

Important information can be found at Using Parameters with Data Source Controls

Hope this blog helps you with your problem!

You may also visit our ASP.NET forum at LTD here ASP.NET Forum at LTD