locked
Setting the SelectParameters of SqlDataSource RRS feed

  • Question

  • User-1116701184 posted

    Hi All,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    I'm trying to set the SelectParameters of SqlDataSource from the code behind.<o:p></o:p>

    This is my query: SELECT * FROM [UploadSessions] WHERE ([OwnerID] = @OwnerID)
    And I need to set the value of the @OwnerID at the code behind since it is stored in an object.<o:p></o:p>

    How can I do it??<o:p></o:p>

    Thanks in advance<o:p></o:p>

    Bar<o:p></o:p>

     

    Friday, December 30, 2005 11:43 AM

All replies

  • User-109060882 posted

    In some event (for example Page_Load, or SqlDataSource.Selecting) add this code:

    SqlDataSource1.SelectParameters["OwnerID"].DefaultValue = "some value";

    Make sure you add a declarative parameter to the SelectParameters collection, too:

    <asp:SqlDataSource ... >

       <SelectParameters>

          <asp:Parameter Name="OwnerID" />

       </SelectParameters>

    </asp:SqlDataSource>

    Thanks,

    Eilon

     

    Friday, December 30, 2005 2:42 PM
  • User-1116701184 posted

    Thanks Eilon,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    This is what I tried to do at the first place, but it's looks like its not working.<o:p></o:p>

    When i debug it and place a break point on:
    SqlDataSource1.SelectParameters["OwnerID"].DefaultValue = "some value";<o:p></o:p>

    The debugger is stopping on this line but I still do not get any results, any ideas why?<o:p></o:p>

    Here is my code:
        protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)<o:p></o:p>

        {<o:p></o:p>

            SqlDataSource1.SelectParameters["OwnerID"].DefaultValue = Membership.GetUser(true).ProviderUserKey.ToString();<o:p></o:p>

        }<o:p></o:p>

    <o:p> </o:p>

    Thanks Bar<o:p></o:p>

    <o:p> </o:p>

     

    Friday, December 30, 2005 4:07 PM
  • User-109060882 posted

    Check what the actual valud of the ProviderUserKey is and see that the value actually makes sense. For example, maybe that OwnerID doesn't return any results because there simply aren't any. Try manually setting the value to be a value that you know returns results and see what happens.

    Thanks,

    Eilon

    Friday, December 30, 2005 5:36 PM
  • User1493530428 posted

    Here is how I did it in VS 2005 (VB.NET) in the Page_Load event.

     

    SqlDataSource1.SelectParameters.Item("UserName").DefaultValue = My.User.Name

    ----------------- exerpts from SqlDataSource

    WHERE (CustRequests.UserName = @UserName)

    <asp:Parameter Name="UserName" Type="String" />

    Wednesday, April 26, 2006 4:07 PM
  • User1677479917 posted

    Hi All,

    As per my knowledge you can achieve it using following 2 ways:

    1. Set Select Parameters value from Code-behind like as shown below: 

    <asp:SqlDataSource ID="SqlDataSource1" runat="server">
    <SelectParameters>
    <asp:Parameter Name="UserID" Type="Int64" />
    </SelectParameters>
    </asp:SqlDataSource>
     

    In Page_Load event or after binding your code behind variable do set the select parameters value as following:

      //set SqlDataSource parameters value

     SqlDataSource1.SelectParameters.Add("@UserID", userID.ToString()); //Where userID is your variable
     OR

    2. Use Control Parameters as shown below:

     <asp:SqlDataSource ID="SqlDataSource1" runat="server">
            <SelectParameters>
                <asp:ControlParameter Name="UserID" ControlID="hdnUserID" PropertyName="Value" />
            </SelectParameters>
            </asp:SqlDataSource>
           
            <%--Hidden field to store UserID--%>
            <asp:HiddenField ID="hdnUserID" runat="server" />

     In Page_Load event or after binding your code behind variable do set the select parameters value as following:

     //set value of hidden field
    hdnUserID.Value = userID.ToString();
    I prefer to use first option. Let me know if it works or even not then also..:)
    If you face any problem feel free to give me a shout...I will be happy to help you.
    Regards,
    Kiran Patil 
    Thursday, April 23, 2009 3:18 AM
  • User1240956533 posted

     I know this is an old topic, but for those still searching for this... Here is the best method I found for binding parameters...

     

    First create an event handler for your datasource OnSelecting Event like so:

     

    <asp:SqlDataSource ... OnSelecting="ds_Selecting">

    Then, set the values using the event arguments:
    protected void ds_Selecting(object sender, SqlDataSourceCommandEventArgs e)
    {
    e.Command.Parameters[0].Value = "Value";
    }
    Monday, June 1, 2009 2:31 PM
  • User77042963 posted

    The event arg should be:

    protected void ds_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

    {

    //e.Command.Parameters[0].Value=User.Identity.Name;

    //Label1.Text = Membership.GetUser().ProviderUserKey.ToString()

    }

    Monday, June 1, 2009 5:31 PM