ASP.NET

Setting the SelectParameters of SqlDataSource RSS

7 replies

Last post Jun 01, 2009 09:31 PM by limno

‹ Previous Thread|Next Thread ›

- Reply
Bar

Member

20 Points

4 Posts

Setting the SelectParameters of SqlDataSource

Dec 30, 2005 03:43 PM|LINK

Hi All,

I'm trying to set the SelectParameters of SqlDataSource from the code behind.

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.

How can I do it??

Thanks in advance

Bar

Bar E.
- Moved by tmorton on Dec 30, 2005 03:45 PM
- Reply
Eilon

Contributor

5753 Points

976 Posts

Microsoft

Re: Setting the SelectParameters of SqlDataSource

Dec 30, 2005 06:42 PM|LINK

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

Blog: http://weblogs.asp.net/LeftSlipper/
- Reply
Bar

Member

20 Points

4 Posts

Re: Setting the SelectParameters of SqlDataSource

Dec 30, 2005 08:07 PM|LINK

Thanks Eilon,

This is what I tried to do at the first place, but it's looks like its not working.

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

The debugger is stopping on this line but I still do not get any results, any ideas why?

Here is my code:
    protected void SqlDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e)

    {

        SqlDataSource1.SelectParameters["OwnerID"].DefaultValue = Membership.GetUser(true).ProviderUserKey.ToString();

    }

Thanks Bar

Bar E.
- Reply
Eilon

Contributor

5753 Points

976 Posts

Microsoft

Re: Setting the SelectParameters of SqlDataSource

Dec 30, 2005 09:36 PM|LINK

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

Blog: http://weblogs.asp.net/LeftSlipper/
- Reply
USFbobFL

Member

195 Points

39 Posts

Re: Setting the SelectParameters of SqlDataSource

Apr 26, 2006 08:07 PM|LINK

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" />
- Reply
klpatil

Participant

1290 Points

245 Posts
Re: Setting the SelectParameters of SqlDataSource

Apr 23, 2009 07:18 AM|LINK
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 
```
Let me know if you need further assistance. I will be happy to help you.

HTH

-Kiran
For more solution like this my blog is here
- Reply
acole86

Member

14 Points

2 Posts
Re: Setting the SelectParameters of SqlDataSource

Jun 01, 2009 06:31 PM|LINK
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";
        } 
```
- Edited by acole86 on Jun 01, 2009 06:31 PM
- Reply
limno

All-Star

117336 Points

8003 Posts

Moderator
MVP

Re: Setting the SelectParameters of SqlDataSource

Jun 01, 2009 09:31 PM|LINK

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()

}

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm