Modify SqlDataSource parameters before select

Discussion in 'ASP .Net' started by =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?=, Oct 20, 2005.

  1. Hi,
    is it possible to modify the values of a SqlDataSource's select parameters
    in the code behind before the select command is executed?

    Example:

    I have an SqlDataSource with a ControlParameter

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:XYZ %>"
    SelectCommand="myStoredProcedure"
    SelectCommandType="storedProcedure">
    <SelectParameters>
    <asp:ControlParameter ControlID="txtTitle" Name="Title"
    PropertyName="Text" Type="String" DefaultValue="%" />
    </SelectParameters>
    </asp:SqlDataSource>

    What I'd like to do is to modify the value of the Title parameter before the
    select command is executed. E.g. if the user enters "abc*" in txtTitle, I'd
    like to change the parameter's value to "abc%".

    Thanks for any help,
    Martin
     
    =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?=, Oct 20, 2005
    #1
    1. Advertising

  2. Hi Martine,

    Welcome to ASPNET newsgroup.
    As for the modifying SqlDataSource's parameters before the select command
    actually get executed, we can utilize the
    SqlDataSource control's "Selecting" event, this event get fired before the
    actual execute command being executed. the event will have a
    SqlDataSourceSelectingEventArgs paramter passed in which can help us
    access the paramters used to perform the select comand:

    protected void SqlDataSource1_Selecting(object sender,
    SqlDataSourceSelectingEventArgs e)
    {

    }

    In fact, in asp.net 2.0 , all the datasource controls support some buildin
    pre/post processsing events such as
    "selecting/selected", "updating/updated"..... which get fired before/after
    a certain command executing.

    Hope helps. Thanks,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)


    --------------------
    | Thread-Topic: Modify SqlDataSource parameters before select
    | thread-index: AcXVY3bXKFhY+ZLJTQeWe1aVqsr9nA==
    | X-WBNR-Posting-Host: 194.209.202.1
    | From: =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?= <>
    | Subject: Modify SqlDataSource parameters before select
    | Date: Thu, 20 Oct 2005 03:46:02 -0700
    | Lines: 24
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
    | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
    | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
    | Xref: TK2MSFTNGXA01.phx.gbl
    microsoft.public.dotnet.framework.aspnet:132687
    | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    |
    | Hi,
    | is it possible to modify the values of a SqlDataSource's select
    parameters
    | in the code behind before the select command is executed?
    |
    | Example:
    |
    | I have an SqlDataSource with a ControlParameter
    |
    | <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    | ConnectionString="<%$ ConnectionStrings:XYZ %>"
    | SelectCommand="myStoredProcedure"
    | SelectCommandType="storedProcedure">
    | <SelectParameters>
    | <asp:ControlParameter ControlID="txtTitle" Name="Title"
    | PropertyName="Text" Type="String" DefaultValue="%" />
    | </SelectParameters>
    | </asp:SqlDataSource>
    |
    | What I'd like to do is to modify the value of the Title parameter before
    the
    | select command is executed. E.g. if the user enters "abc*" in txtTitle,
    I'd
    | like to change the parameter's value to "abc%".
    |
    | Thanks for any help,
    | Martin
    |
     
    Steven Cheng[MSFT], Oct 21, 2005
    #2
    1. Advertising

  3. Hi Steven,
    thanks for your reply. In fact, what you describe is exactly what I have
    done so far. But how to continue from there, how do I access and modify the
    SqlDataSource's parameter values? I have not found any documentation / sample
    about that.

    Martin Bischoff


    "Steven Cheng[MSFT]" wrote:

    > Hi Martin,
    >
    > Welcome to ASPNET newsgroup.
    > As for the modifying SqlDataSource's parameters before the select command
    > actually get executed, we can utilize the
    > SqlDataSource control's "Selecting" event, this event get fired before the
    > actual execute command being executed. the event will have a
    > SqlDataSourceSelectingEventArgs paramter passed in which can help us
    > access the paramters used to perform the select comand:
    >
    > protected void SqlDataSource1_Selecting(object sender,
    > SqlDataSourceSelectingEventArgs e)
    > {
    >
    > }
    >
    > In fact, in asp.net 2.0 , all the datasource controls support some buildin
    > pre/post processsing events such as
    > "selecting/selected", "updating/updated"..... which get fired before/after
    > a certain command executing.
    >
    > Hope helps. Thanks,
    >
    > Steven Cheng
    > Microsoft Online Support
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    >
    > --------------------
    > | Thread-Topic: Modify SqlDataSource parameters before select
    > | thread-index: AcXVY3bXKFhY+ZLJTQeWe1aVqsr9nA==
    > | X-WBNR-Posting-Host: 194.209.202.1
    > | From: =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?= <>
    > | Subject: Modify SqlDataSource parameters before select
    > | Date: Thu, 20 Oct 2005 03:46:02 -0700
    > | Lines: 24
    > | Message-ID: <>
    > | MIME-Version: 1.0
    > | Content-Type: text/plain;
    > | charset="Utf-8"
    > | Content-Transfer-Encoding: 7bit
    > | X-Newsreader: Microsoft CDO for Windows 2000
    > | Content-Class: urn:content-classes:message
    > | Importance: normal
    > | Priority: normal
    > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
    > | Newsgroups: microsoft.public.dotnet.framework.aspnet
    > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
    > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
    > | Xref: TK2MSFTNGXA01.phx.gbl
    > microsoft.public.dotnet.framework.aspnet:132687
    > | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    > |
    > | Hi,
    > | is it possible to modify the values of a SqlDataSource's select
    > parameters
    > | in the code behind before the select command is executed?
    > |
    > | Example:
    > |
    > | I have an SqlDataSource with a ControlParameter
    > |
    > | <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    > | ConnectionString="<%$ ConnectionStrings:XYZ %>"
    > | SelectCommand="myStoredProcedure"
    > | SelectCommandType="storedProcedure">
    > | <SelectParameters>
    > | <asp:ControlParameter ControlID="txtTitle" Name="Title"
    > | PropertyName="Text" Type="String" DefaultValue="%" />
    > | </SelectParameters>
    > | </asp:SqlDataSource>
    > |
    > | What I'd like to do is to modify the value of the Title parameter before
    > the
    > | select command is executed. E.g. if the user enters "abc*" in txtTitle,
    > I'd
    > | like to change the parameter's value to "abc%".
    > |
    > | Thanks for any help,
    > | Martin
    > |
    >
    >
     
    =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?=, Oct 21, 2005
    #3
  4. Thanks for your response Martin,

    So in SqlDataSource's "Updating" event, we can access the parameters
    through the SqlDataSourceEventArg 's

    Command.Parameters collection. For example:

    protected void SqlDataSource1_Updating(object sender,
    SqlDataSourceCommandEventArgs e)
    {
    Response.Write("<br>CommandType: " + e.Command.CommandType);
    Response.Write("<br>Commandparammeters: " + e.Command.Parameters.Count);
    foreach (DbParameter param in e.Command.Parameters)
    {
    Response.Write("<br>" + param.ParameterName + ": " + param.Value);
    }

    e.Cancel = true;
    }



    Also, for intercepting the updating or deleting operation and do our
    customziation, I'd suggest you consider do it at databound control level
    instead of at datasource level as much as possible. Because the DataBound
    controls also provide some certain data accessing related pre/post
    processing events such as GridView's RowUpdating/RowUpdated , Formview's
    ItemUpdating/ItemUpdated .... and they're more easiser to use then
    DataSource control's events.

    e.g.
    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs
    e)
    {
    //e.Keys;

    //e.NewValues;

    //e.OldValues;
    }


    Hope also helps. thanks,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)


    --------------------
    | Thread-Topic: Modify SqlDataSource parameters before select
    | thread-index: AcXWDTPeMZdfCKCyT8unaRTKrcH8lw==
    | X-WBNR-Posting-Host: 194.209.202.1
    | From: =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?= <>
    | References: <>
    <o$>
    | Subject: RE: Modify SqlDataSource parameters before select
    | Date: Fri, 21 Oct 2005 00:01:04 -0700
    | Lines: 97
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
    | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
    | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
    | Xref: TK2MSFTNGXA01.phx.gbl
    microsoft.public.dotnet.framework.aspnet:132958
    | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    |
    | Hi Steven,
    | thanks for your reply. In fact, what you describe is exactly what I have
    | done so far. But how to continue from there, how do I access and modify
    the
    | SqlDataSource's parameter values? I have not found any documentation /
    sample
    | about that.
    |
    | Martin Bischoff
    |
    |
    | "Steven Cheng[MSFT]" wrote:
    |
    | > Hi Martin,
    | >
    | > Welcome to ASPNET newsgroup.
    | > As for the modifying SqlDataSource's parameters before the select
    command
    | > actually get executed, we can utilize the
    | > SqlDataSource control's "Selecting" event, this event get fired before
    the
    | > actual execute command being executed. the event will have a
    | > SqlDataSourceSelectingEventArgs paramter passed in which can help us
    | > access the paramters used to perform the select comand:
    | >
    | > protected void SqlDataSource1_Selecting(object sender,
    | > SqlDataSourceSelectingEventArgs e)
    | > {
    | >
    | > }
    | >
    | > In fact, in asp.net 2.0 , all the datasource controls support some
    buildin
    | > pre/post processsing events such as
    | > "selecting/selected", "updating/updated"..... which get fired
    before/after
    | > a certain command executing.
    | >
    | > Hope helps. Thanks,
    | >
    | > Steven Cheng
    | > Microsoft Online Support
    | >
    | > Get Secure! www.microsoft.com/security
    | > (This posting is provided "AS IS", with no warranties, and confers no
    | > rights.)
    | >
    | >
    | > --------------------
    | > | Thread-Topic: Modify SqlDataSource parameters before select
    | > | thread-index: AcXVY3bXKFhY+ZLJTQeWe1aVqsr9nA==
    | > | X-WBNR-Posting-Host: 194.209.202.1
    | > | From: =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?= <>
    | > | Subject: Modify SqlDataSource parameters before select
    | > | Date: Thu, 20 Oct 2005 03:46:02 -0700
    | > | Lines: 24
    | > | Message-ID: <>
    | > | MIME-Version: 1.0
    | > | Content-Type: text/plain;
    | > | charset="Utf-8"
    | > | Content-Transfer-Encoding: 7bit
    | > | X-Newsreader: Microsoft CDO for Windows 2000
    | > | Content-Class: urn:content-classes:message
    | > | Importance: normal
    | > | Priority: normal
    | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
    | > | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | > | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
    | > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
    | > | Xref: TK2MSFTNGXA01.phx.gbl
    | > microsoft.public.dotnet.framework.aspnet:132687
    | > | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    | > |
    | > | Hi,
    | > | is it possible to modify the values of a SqlDataSource's select
    | > parameters
    | > | in the code behind before the select command is executed?
    | > |
    | > | Example:
    | > |
    | > | I have an SqlDataSource with a ControlParameter
    | > |
    | > | <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    | > | ConnectionString="<%$ ConnectionStrings:XYZ %>"
    | > | SelectCommand="myStoredProcedure"
    | > | SelectCommandType="storedProcedure">
    | > | <SelectParameters>
    | > | <asp:ControlParameter ControlID="txtTitle" Name="Title"
    | > | PropertyName="Text" Type="String" DefaultValue="%" />
    | > | </SelectParameters>
    | > | </asp:SqlDataSource>
    | > |
    | > | What I'd like to do is to modify the value of the Title parameter
    before
    | > the
    | > | select command is executed. E.g. if the user enters "abc*" in
    txtTitle,
    | > I'd
    | > | like to change the parameter's value to "abc%".
    | > |
    | > | Thanks for any help,
    | > | Martin
    | > |
    | >
    | >
    |
     
    Steven Cheng[MSFT], Oct 24, 2005
    #4
  5. Thanks Steven, this was the information I was looking for.

    Best regards,
    Martin

    "Steven Cheng[MSFT]" wrote:

    > Thanks for your response Martin,
    >
    > So in SqlDataSource's "Updating" event, we can access the parameters
    > through the SqlDataSourceEventArg 's
    >
    > Command.Parameters collection. For example:
    >
    > protected void SqlDataSource1_Updating(object sender,
    > SqlDataSourceCommandEventArgs e)
    > {
    > Response.Write("<br>CommandType: " + e.Command.CommandType);
    > Response.Write("<br>Commandparammeters: " + e.Command.Parameters.Count);
    > foreach (DbParameter param in e.Command.Parameters)
    > {
    > Response.Write("<br>" + param.ParameterName + ": " + param.Value);
    > }
    >
    > e.Cancel = true;
    > }
    >
    >
    >
    > Also, for intercepting the updating or deleting operation and do our
    > customziation, I'd suggest you consider do it at databound control level
    > instead of at datasource level as much as possible. Because the DataBound
    > controls also provide some certain data accessing related pre/post
    > processing events such as GridView's RowUpdating/RowUpdated , Formview's
    > ItemUpdating/ItemUpdated .... and they're more easiser to use then
    > DataSource control's events.
    >
    > e.g.
    > protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs
    > e)
    > {
    > //e.Keys;
    >
    > //e.NewValues;
    >
    > //e.OldValues;
    > }
    >
    >
    > Hope also helps. thanks,
    >
    > Steven Cheng
    > Microsoft Online Support
    >
    > Get Secure! www.microsoft.com/security
    > (This posting is provided "AS IS", with no warranties, and confers no
    > rights.)
    >
    >
     
    =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?=, Oct 24, 2005
    #5
  6. You're welcome Martin,

    Best Regards,

    Steven Cheng
    Microsoft Online Support

    Get Secure! www.microsoft.com/security
    (This posting is provided "AS IS", with no warranties, and confers no
    rights.)

    --------------------
    | Thread-Topic: Modify SqlDataSource parameters before select
    | thread-index: AcXYotCCpDtRXFvJSf2plq1QOZvQ+w==
    | X-WBNR-Posting-Host: 194.209.202.1
    | From: =?Utf-8?B?TWFydGluIEJpc2Nob2Zm?= <>
    | References: <>
    <o$>
    <>
    <>
    | Subject: RE: Modify SqlDataSource parameters before select
    | Date: Mon, 24 Oct 2005 06:57:04 -0700
    | Lines: 60
    | Message-ID: <>
    | MIME-Version: 1.0
    | Content-Type: text/plain;
    | charset="Utf-8"
    | Content-Transfer-Encoding: 7bit
    | X-Newsreader: Microsoft CDO for Windows 2000
    | Content-Class: urn:content-classes:message
    | Importance: normal
    | Priority: normal
    | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
    | Newsgroups: microsoft.public.dotnet.framework.aspnet
    | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
    | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA03.phx.gbl
    | Xref: TK2MSFTNGXA01.phx.gbl
    microsoft.public.dotnet.framework.aspnet:133435
    | X-Tomcat-NG: microsoft.public.dotnet.framework.aspnet
    |
    | Thanks Steven, this was the information I was looking for.
    |
    | Best regards,
    | Martin
    |
    | "Steven Cheng[MSFT]" wrote:
    |
    | > Thanks for your response Martin,
    | >
    | > So in SqlDataSource's "Updating" event, we can access the parameters
    | > through the SqlDataSourceEventArg 's
    | >
    | > Command.Parameters collection. For example:
    | >
    | > protected void SqlDataSource1_Updating(object sender,
    | > SqlDataSourceCommandEventArgs e)
    | > {
    | > Response.Write("<br>CommandType: " + e.Command.CommandType);
    | > Response.Write("<br>Commandparammeters: " +
    e.Command.Parameters.Count);
    | > foreach (DbParameter param in e.Command.Parameters)
    | > {
    | > Response.Write("<br>" + param.ParameterName + ": " + param.Value);
    | > }
    | >
    | > e.Cancel = true;
    | > }
    | >
    | >
    | >
    | > Also, for intercepting the updating or deleting operation and do our
    | > customziation, I'd suggest you consider do it at databound control
    level
    | > instead of at datasource level as much as possible. Because the
    DataBound
    | > controls also provide some certain data accessing related pre/post
    | > processing events such as GridView's RowUpdating/RowUpdated ,
    Formview's
    | > ItemUpdating/ItemUpdated .... and they're more easiser to use then
    | > DataSource control's events.
    | >
    | > e.g.
    | > protected void GridView1_RowUpdating(object sender,
    GridViewUpdateEventArgs
    | > e)
    | > {
    | > //e.Keys;
    | >
    | > //e.NewValues;
    | >
    | > //e.OldValues;
    | > }
    | >
    | >
    | > Hope also helps. thanks,
    | >
    | > Steven Cheng
    | > Microsoft Online Support
    | >
    | > Get Secure! www.microsoft.com/security
    | > (This posting is provided "AS IS", with no warranties, and confers no
    | > rights.)
    | >
    | >
    |
    |
     
    Steven Cheng[MSFT], Oct 25, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?RGFiYmxlcg==?=

    modify SqlDataSource parameters in code behind?

    =?Utf-8?B?RGFiYmxlcg==?=, Apr 4, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    28,466
    =?Utf-8?B?RGFiYmxlcg==?=
    Apr 4, 2006
  2. David W
    Replies:
    3
    Views:
    895
    David W
    Feb 15, 2008
  3. Replies:
    2
    Views:
    724
    Alessandro Zifiglio
    Jul 20, 2006
  4. palmiere
    Replies:
    1
    Views:
    464
    Erwin Moller
    Feb 9, 2004
  5. WebRaster
    Replies:
    1
    Views:
    289
    Francisco Spaeth
    Oct 26, 2005
Loading...

Share This Page