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:
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.
Hi NightStalker,
Very kind of you to respond again.
Bear in mind that since we last spoke I’ve been trying to discover what a “scalar variable” is :injured: . I am fascinated by all this tech but time is of the essence - I was hoping to discover that, sling one in and get back to work.
However I did take your advice seriously, and given that I couldn’t see where you got “DataSet” from - I started googling. To start with I was looking up how to replace a Datatable with a DataReader - Oh the follies of opperating on a trial and error basis.
Anyway, I think I discovered that what you are suggesting is that I use DataReader instead of DataAdapter. Some of the advantages from that are clear because I don’t need to update, insert or delete from my web page - and I can see that Dataset uses Datareader anyway. But I am very concerned about the coding difficulties re the change and if it will interfere with the pager or even the label.text business you helped me with. I think i read something about the page needing to refresh everytime DataReader gets used.
I wouldn’t know where to begin with the adjustment to a DataReader but I have gained some knowledge today and saved a lot of pages for reading.
Having got a bit stuck with that and having come accross such a lot of general info, I spent a while trying something like the following to try and get what i need from my existing code: -
It feels like it’s on the right track.
If DataReader needs to be a thing for the future do you have any helpful suggestions re how I can make this code work so that I can allow for user selection from a dropdownlist whilst keeping my pager.
Error is - Cannot compute Count for a data source that does not implement ICollection Re :
lnkbtnNext.Enabled = !pds.IsLastPage;
Have googled - some say use ICollector - some say DataReader cant impliment ICollection and say use datatable instead.:injured:
I would be grateful if you would be kind enough to give my code above a quick check ( there are no swiggly lines under code ) and offer some guidence re the ICollector error.:)
From what I have been able to discover today DataReader does not support Icollection unless you use custom paging. I think I use custom paging - it didn’t specifically say it in the pager tutorial but it seems customised to me.
Another thing I think I have learnd taday is that I will need to loop (which is not at all difficult for me on a personal level at present but as far as code goes - I have seen but never used.
If you think I am better off going back to DataTable and trying to overcome scalar variable error, do not hesitate to let me know - your efforts are much appreciated either way.
The amount of records returned by my pager are user definable by dropdown list.
I have added the full code for my pager below so you can see the specifics of my pager if it helps your arrive at decision - under that there is a temporary link to the working pager online
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class WholesaleCSHARP_2 : System.Web.UI.Page
{
PagedDataSource pds = new PagedDataSource();
public void example()
{
string ddl = DropDownList1.SelectedItem.ToString();
}
private void BindGrid()
{
string ddl = DropDownList1.SelectedItem.ToString();
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", conn );
cmd.Parameters.AddWithValue("@ProdCategoryID","ddl");
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
pds.DataSource = dr;
pds.AllowPaging = true;
pds.PageSize = Convert.ToInt16(ddlPageSize.SelectedValue);
pds.CurrentPageIndex = CurrentPage;
dr.Close();
conn.Close();
lnkbtnNext.Enabled = !pds.IsLastPage;
lnkbtnPrevious.Enabled = !pds.IsFirstPage;
DataList1.DataSource = pds;
DataList1.DataBind();
doPaging();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
public int CurrentPage
{
get
{
if (this.ViewState["CurrentPage"] == null)
return 0;
else
return Convert.ToInt16(this.ViewState["CurrentPage"].ToString());
}
set
{
this.ViewState["CurrentPage"] = value;
}
}
private void doPaging()
{
DataTable dt = new DataTable();
dt.Columns.Add("PageIndex");
dt.Columns.Add("PageText");
for (int i = 0; i < pds.PageCount; i++)
{
DataRow dr = dt.NewRow();
dr[0] = i;
dr[1] = i + 1;
dt.Rows.Add(dr);
}
dlPaging.DataSource = dt;
dlPaging.DataBind();
}
protected void dlPaging_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName.Equals("lnkbtnPaging"))
{
CurrentPage = Convert.ToInt16(e.CommandArgument.ToString());
BindGrid();
}
}
protected void lnkbtnPrevious_Click(object sender, EventArgs e)
{
CurrentPage -= 1;
BindGrid();
}
protected void lnkbtnNext_Click(object sender, EventArgs e)
{
CurrentPage += 1;
BindGrid();
}
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
CurrentPage = 0;
BindGrid();
}
protected void dlPaging_ItemDataBound(object sender, DataListItemEventArgs e)
{
LinkButton lnkbtnPage = (LinkButton)e.Item.FindControl("lnkbtnPaging");
if (lnkbtnPage.CommandArgument.ToString() == CurrentPage.ToString())
{
lnkbtnPage.Enabled = false;
lnkbtnPage.Font.Bold = true;
}
}
}
If you want to go back to using a datatable thats fine. I just prefer to stay away from those things as far as possible. Use the SqlCommand with parameters like I showed you, then in your data adapter, use the sqlcommand and not the string. Then all should be fine.
You cannot use @ProdCategoryID in this context because sql server expects @ProdCategoryID which you have not declared. If the value you want to use for your filter is the value from the dropdownlist the you should have smth like this
SqlCommand cmd = new SqlCommand ("SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE [ProdCategoryID] = " + DropdownList1.SelectedItem.Value )
But this in itself is a bad practice, cos it’s prone to sql injection. I see you tried using parameters, but they should be declared first before being used.
Or simply add value to @ProdCategoryID before using it for ur command statement, so
cmd.Parameters.AddWithValue(“@ProdCategoryID”, “ddl”);
SqlCommand cmd = new SqlCommand (“SELECT [ProductCode], [ImgUrl], [Featuring], [Title] FROM [TableSG1] WHERE [ProdCategoryID] = @ProdCategoryID”);
Does that mean the snippet corrected earlier from “String()” to “Value” should be “SelectedValue” like below.
string ddl = DropDownList1.SelectedValue;
If you guys any other ideas as to why I am running into problem with this code not working I am very grateful for advice. Error is " You must declare scalar variable @ProdCategoryID" in the code behind near “da.Fill(dt);” :)
Yes to USPatriot I think that makes sense to me because I read that the DataTable works from memory - so not at run time.
What I am absolutly stuck with and am ashamed to confess to is I cannot work out how to declare what needs to be declared. This is the first web page I have used with C#. I have only been seriously trying to learn programming for 3 months in spare time and mainly with VB. The pager tutorial was in C# and couldn’t be converted using online converters so I thought I would give it a bash and have ended up working with C# for the first time.
To go with Chieftain’s advice :-
I need to declare @ProdCategoryID
OR
If I go for the other option and simply add the value before the cmd parameter befor the sql command. If I do that I get a red squiggle under the “cmd” saying that I need to declare “cmd” before using it.
So I have a problem with declaring!
I have tried decaring @ProdCategoryID as follows but no joy.
string @ProdCategoryID = DropDownList1.SelectedItem.Value;
string @ProdCategoryID = DropDownList1.SelectedValue;
I thought that cmd is declared in the sqlcommand but using it before that command obviously rules that out.
I would be very grateful if someone could show me how to do these declarations. :injured::):)
I was hoping for an example and you gave me the code I needed. Visual Studio shows no errors . Big cheeers to you but I still got the online error -Titled “Server Error in ‘/’ Application” sub title “Must declare scalar variable” . The page works fine online (were I have been debugging ) until the parameter is added.
I have looked up the error number on google - advice in some cases says to ask web host to adjust confuration. I have submitted ticket to web hosts
explaining error and am now waiting to see what they say.
I have not been able to work out how to debug this on my local PC since adding pager - niether the connection string from the .aspx page or the web config file seem to work but its fine to debug online. I think I need to actually include “localhost” in my string - I will try and suss that out while I am waiting which may help point to the problem.
I think the code you sent is exactly what I needed.
I had a peek at the SqlDataSource/Properties/SelectQuery/parameters / advanced properties - Everything looked set correctly -though DBType set to Object which is above my knowledge so I don’t know if that is right.
Here is the code as I completed it with your advice - it looks to have everything it needs.
string ddl = DropDownList1.SelectedItem.Value;
SqlConnection conn = new SqlConnection("Connection String ");
SqlParameter param = new SqlParameter();
param.ParameterName = "@ProdCategoryID";
param.Value = DropDownList1.SelectedItem.Value;
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();
da.Fill(dt);
I have uploaded a text file copy of the online error page if it interests anyone.
I will wait for response from my web hosts and post here re what they say. If there is no joy I will find another way such as use new web pages for each product in the dropdown list.
Thanks for all the effort and kind advice received throughout this thread.
:)
I still have to sort out the selectedindex or something in properties of DropDownList1 because it is not returning different categories by itself ( I do have autopostback selected) - However I know this code is working because if I select a category in the DropDownList and adjust the page size with the pager, the page loads the page size with the product selected in the dropdownlist.
Also will try to add a textbox search parameter using same technique and will post full working code for Paging a Datalist with control filter parameters as soon as I am done.
It would be especially useful to have this thread available to search engines because I have visited zillions of pages, searched in all corners through - google - paged datalist / datatable + “add filter” + “parameter” etc, many many other variations until virtually every page that came back had already been visited - and I could not find an example or a lesson.
