Using a WHERE clause in an sqldatasource with Reorderlist ( Well defined problem)

Home ›

ASP.NET Forums » ASP.NET AJAX » ASP.NET AJAX Control Toolkit » Using a WHERE clause in an sqldatasource with Reorderlist ( Well defined problem)

Shortcuts

Previous Next

Using a WHERE clause in an sqldatasource with Reorderlist ( Well defined problem)

Last post 08-20-2008 5:23 AM by Thomas Sun – MSFT. 1 replies.

Sort Posts:

Using a WHERE clause in an sqldatasource with Reorderlist ( Well defined problem)

08-15-2008, 9:54 AM

Contact

372 point Member
Nuwanda
Member since 09-25-2007, 3:42 PM
Posts 207

I'm using the Reorderlist ajax control to reorder a list of items, this works PERFECTLY by using an sqldatasource with the reorderlist untill I add a WHERE clause to the SELECT statement, then nothing gets saved to the database. Any thoughts on why? or how I can make it work....I need a where clause since I have lots of independent lists inside this one table ( one list for each user).

Here is the code for the sqldatasource.....the one that works....and the one with the where clause that does not work. Nothing else gets changed.

WORKS...

<asp:SqlDataSource ID="LocalServer" runat="server" ConnectionString="Data Source=MySQLServer;Initial Catalog=MyDatabase;User ID=admin;Password=admin"

SelectCommand="SELECT [WishID], [WishDescription], [WishOrder], [WishParentID] FROM [WishList] ORDER BY [WishOrder]"

UpdateCommand="UPDATE [WishList] SET [WishDescription] = @WishDescription, [WishOrder] = @WishOrder WHERE [WishID] = @WishID">

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

<asp:Parameter Name="WishOrder" Type="Int32" />

<asp:Parameter Name="WishID" Type="Object" />

<asp:Parameter Name="WishParentID" Type="Int32" />

</UpdateParameters>

</asp:SqlDataSource>

DOES NOT WORK...

<asp:SqlDataSource ID="LocalServer" runat="server" ConnectionString="Data Source=MySQLServer;Initial Catalog=MyDatabase;User ID=admin;Password=admin"

SelectCommand="SELECT [WishID], [WishDescription], [WishOrder], [WishParentID] FROM [WishList] WHERE [WishParentID] = @WishParentID ORDER BY [WishOrder]" UpdateCommand="UPDATE [WishList] SET [WishDescription] = @WishDescription, [WishOrder] = @WishOrder, [WishParentID] = @ParentID WHERE [WishID] = @WishID">

<asp:Parameter Name="WishParentID" Type="int32" DefaultValue="77" />

</SelectParameters>

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

<asp:Parameter Name="WishOrder" Type="Int32" />

<asp:Parameter Name="WishID" Type="Object" />

<asp:Parameter Name="WishParentID" Type="Int32" />

</UpdateParameters>

</asp:SqlDataSource>

This is killing me......if you have any suggestions I would appriciate them very much.

Nuwanda

You learn from experience, everything else is just information.

Re: Using a WHERE clause in an sqldatasource with Reorderlist ( Well defined problem)

08-20-2008, 5:23 AM

Contact

Answer

62,254 point All-Star
Thomas Sun – MSFT
Member since 06-06-2007, 2:12 AM
Posts 5,377

Hi,

From your description, I understand that the SqlDataSource control doesn't return data with where clause.

How do you set the Select parameter? Based on the code above, please make sure that there is data whose WishParentID is "77". We also can bind this select parameter to session, control, or set it in the codebehind, etc. For more information about using parameterized queries with the sqlDataSource, see http://www.asp.net/learn/data-access/tutorial-48-cs.aspx.

For example, I use DropDownList to filter category and set SqlDataSource's select parameter to this DropDownList's SelectValue:

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:dropdownlist ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2"
            DataTextField="CategoryName" DataValueField="CategoryID">
        </asp:dropdownlist>
        <asp:sqldatasource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:aspnetforumConnectionString %>"
            SelectCommand="SELECT [CategoryID], [CategoryName] FROM [CategoryTable]"></asp:sqldatasource>
            
        <ajaxtoolkit:reorderlist ID="ReorderList1" runat="server" AllowReorder="True" DataSourceID="SqlDataSource1"
            PostBackOnReorder="False">
            <ItemTemplate>
                <%# Eval("ProductName")%>
            </ItemTemplate>
        </ajaxtoolkit:reorderlist>
        <asp:sqldatasource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:aspnetforumConnectionString %>"
            SelectCommand="SELECT [ProductName] FROM [ProductTable] WHERE ([CategoryID] = @CategoryID)">
            <selectparameters>
                <asp:controlparameter ControlID="DropDownList1" Name="CategoryID" PropertyName="SelectedValue"
                    Type="Int32" />
            </selectparameters>
        </asp:sqldatasource>
        <asp:scriptmanager ID="ScriptManager1" runat="server">
        </asp:scriptmanager>
         
    </form>
</body>
</html>

I look forward to hearing from you.

Thomas Sun
Microsoft Online Community Support

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

Page 1 of 1 (2 items)

RSS Available