Asp.Net News
Free Tech Magazines
Asp.Net Web Hosting
Suggest Us
Link to Us
Feeds Subscription
Questions & Answers
Tips & Tricks
 DataList / PagedDataSource / DataTable - How to allow for filter Control Parameter?
Posted by Charlie Childs on  4/18/2010 12:49:38 PM Category: ADO.NET
Add to my favorites
Email to friend

I use VWD 2008 Express and followed EXCELLENT tutorial about paging a datalist using PagedDataSource ( article produced by Moderator1 )

Prior to adding paging I had a dropdownlist and a search box filter 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]">

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

<asp:ControlParameter ControlID="TextBox1" Name="Featuring" PropertyName="Text"
Type="String" />

When creating the pager the tutorial did not have filter 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;Initial Catalog=DB_5890_maindb1;User ID=DB_5890_maindb1_user;Password=xxxxx;Integrated Security=False;");
DataTable dt = new DataTable();

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;


I am very happy with the pager but I am hoping 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 saying 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.

PS. For Moderator1's info - I don't mind because the tutorial was EXcellent, but I thought it may interest you to know that I am debugging online because VWD 2008 Express debug has an exception at "SqlDataAdapter da = new SqlDataAdapter(sql, . . HERE. ." in "private void BindGrid ()" re my string from my aspx page or the one from my webconfig file. Web Config file connection string is this: -
"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MainDB1.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlCl". A few errors pop up when trying to debug to localhost with that. It doesn't like the escape sequence and has a probelm with "providerName" as well. However, I love my new pager. If this has cought your eye, one otherthing that may interest you is - link buttons were not highlighting in Firefox when I last checked.

Post Your Reply Post New Question

 Viewer's Reply
Progress and correction
Answered By Charlie Childs on 4/20/2010 4:02:28 PM
Correction re Firefox comment above. It occurred to me that I should have cross checked my pager against your "Paging A DataList" working example - Yours works in Firefox - I am newbie and sorry for comment.

Progress is as follows.

I have simplified my SELECT clause to :-
"SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE [ProdCategoryID] = @ProdCategoryID"

The filter [ProdCategoryID] is an unbound dropdownlist.

I have got as far as the following with the code:-

string ddl = DropDownList1.SelectedItem.ToString();
SqlParameter param = new SqlParameter("@ProdCategoryID",SqlDbType.VarChar);
param.Value = ddl;

I am unsure as to what to do with the <SelectParameters> html in the aspx page. Do I need to loose it once the parameter code is in place in the code behind?

If anyone gets a moment, I am very grateful for any guidence re how to proceed.
More Progress?
Answered By Charlie Childs on 4/21/2010 6:22:21 AM
I have tried to pick the bones out of various tutorials and believe I am making progress:
Changed the first line to "SelectedItem.Value" / created new Sql Command and parameter code:-
string ddl = DropDownList1.SelectedItem.Value;
SqlConnection conn = new SqlConnection("Connection String;");
SqlCommand cmd = new SqlCommand ("SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE [ProdCategoryID] = @ProdCategoryID");
cmd.Parameters.AddWithValue("@ProdCategoryID", "ddl");
SqlDataAdapter da = new SqlDataAdapter ("SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE ([ProdCategoryID] = @ProdCategoryID", conn );

DataTable dt = new DataTable();

But I am still getting "Must declare scalar variable at @ProdCategoryID" with the "da.Fill(dt) line highlighted in red.

I am hoping this thread is becoming less of a nightmare - I figure it is best if I keep up the effort and hopefully make my problem easier for you to comment on.

Related Solution for this Issue
Answered By Moderator1 on 4/23/2010 10:28:13 AM
Hi all,

Related solution found at below links,
Post Your Reply If you still have problem, post it again under this reply for better response.

Sponsored by
Programmers Heaven C# School Book - Free 338 Page eBook
Take Advantage of Oracle's 2 Day DBA Course
Backup and Recovery Best Practices for Microsoft SQL Server 2005