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
Introduction to Web Applications Development
Simply JavaScript - Free 150 Page Preview!
The Beginner's Guide to Joomla