DataList / PagedDataSource / DataTable - How to allow for filter Control Parameter?

[CharlieChilds]

Prior to adding paging using PagedDataSource I had dropdownlist and search box filters for my DataList. The guts of the code in aspx page for those parameters is below:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [ProductCode], [ImgUrl], [ImageField], [Featuring], [ProdCategoryID], [Title] FROM [TableSG1] WHERE (([ProdCategoryID] = @ProdCategoryID) AND ([Featuring] LIKE '%' + @Featuring + '%')) ORDER BY [Featuring]">
 
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="ProdCategoryID" 
                PropertyName="SelectedValue" Type="String" />
 
            <asp:ControlParameter ControlID="TextBox1" Name="Featuring" PropertyName="Text" 
                Type="String" />
 

When creating the pager using PagedDataSource I did a tutorial which did not have parameters and all the code went in the code behind page. The guts of it is below:

     private void BindGrid()
     {
         string sql = "SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] DESC";
         SqlDataAdapter da = new SqlDataAdapter(sql, "Data Source=tcp:s02.winhost.com;Initial Catalog=DB_5890_maindb1;User ID=DB_5890_maindb1_user;Password=xxxxx;Integrated Security=False;");
         DataTable dt = new DataTable();
         da.Fill(dt);
 
         pds.DataSource = dt.DefaultView;
         pds.AllowPaging = true;
         pds.PageSize = Convert.ToInt16(ddlPageSize.SelectedValue);
         pds.CurrentPageIndex = CurrentPage;
         lnkbtnNext.Enabled = !pds.IsLastPage;
         lnkbtnPrevious.Enabled = !pds.IsFirstPage;
 
         DataList1.DataSource = pds;
         DataList1.DataBind();
 
         doPaging();
}

The pager is great but now I need to add my filters. The page wont load if I add the "SelectParameters" code (above) to my aspx page and if I simply add the "WHERE" clause to the "SELECT" clause in the code behind I get an error stating that I need to add a scalar variable.

I have found some examples through googling but nothing specific enough.

I need assistance if anyone would be kind enough to help.

[CroCrew]

You have posted your question in the asp (Classic ASP) section rather than the asp.net forum section.

I have moved it across for you.

CroCrew~

[Frinavale]

I'm not sure why you're having problems with the where clause.
What is the error message exactly?

[CharlieChilds]

Hi Frinavale,

The error message is "must declare scaler variable @ProdCategoryID"
I have made some progress in getting as far as the following: NB I have shortened the SELECT clause to include only one parameter.

         string ddl = DropDownList1.SelectedItem.Value;
         SqlConnection conn = new SqlConnection("Data Source=tcp:s02.winhost.com;Initial Catalog=DB_5890_maindb1;User ID=DB_5890_maindb1_user;Password=bootsie23000;Integrated Security=False;");
 
         SqlCommand cmd = new SqlCommand ("SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE [ProdCategoryID] = @ProdCategoryID");
         SqlDataAdapter da = new SqlDataAdapter ("SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE ([ProdCategoryID] = @ProdCategoryID", conn );
         cmd.Parameters.AddWithValue("@ProdCategoryID", "ddl");
         DataTable dt = new DataTable();
         da.Fill(dt);

ProdCategoryID is the name of drop down list as seen in the aspx page control parameter code as follows:

  <SelectParameters>
                 <asp:ControlParameter ControlID="DropDownList1" Name="ProdCategoryID" 
                PropertyName="SelectedValue" Type="String" />

DropDownList1 is unbound - code from aspx page is below :

 <asp:DropDownList ID="DropDownList1" runat="server" 
            Height="28px" Width="176px" Font-Bold="True" 
            Font-Size="Medium"  AutoPostBack="True">
            <asp:ListItem>Giant Posters</asp:ListItem>
            <asp:ListItem>Extra Long Posters</asp:ListItem>
            <asp:ListItem>Regular Posters</asp:ListItem>
            <asp:ListItem>Slim Posters</asp:ListItem>
            <asp:ListItem>Small Posters</asp:ListItem>
            <asp:ListItem>Lenticular Posters</asp:ListItem>
            <asp:ListItem>Stickers</asp:ListItem>
            <asp:ListItem>Key Rings</asp:ListItem>
            <asp:ListItem>Calendars</asp:ListItem>
        </asp:DropDownList>

I have probably got a bit to far ahead of myself for a newbie. The filter worked when using VWD 2008 Express wysiwag without the pager. But in following the pager tutorial I stuck with its format which did not include a WHERE clause in the SELECT clause. I was hoping to workout how to add them back in afterwards and that is where I am. The pager tutorial took me away from my VB training ( which I am only a beginner of ) and into C#. I have been googling like mad for advice on scalar variables and thought I had done it with the "string ddl = DropDownList1.SelectedItem.Value;" but I am still getting the same error.

Any help is very much appreciated.

[CharlieChilds]

SqlConnection conn = new SqlConnection("Data Source=tcp:s02.winhost.com;Initial Catalog=DB_5890_maindb1;User ID=DB_5890_maindb1_user;Password=bootsie23000;Integrated Security=False;");
         SqlCommand cmd = new SqlCommand("SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE [ProdCategoryID] = @ProdCategoryID ORDER BY [Featuring]", conn);
 
         SqlParameter param = new SqlParameter();
         param.ParameterName = "@ProdCategoryID";
 
         param.Value = DropDownList1.SelectedItem.Value;
         cmd.Parameters.Add(param);
 
         SqlDataAdapter da = new SqlDataAdapter(cmd);
         DataTable dt = new DataTable();
         da.Fill(dt);

[Frinavale]

Hmm interesting.
I've never used the AddWithValue method before.

I was about to recommend that you use the add method instead but it looks like you figured it out before I got the chance.


Thanks for sharing your solution!

-Frinny