Error binding DataAdapter->DataSet->LINQ->ASP.Net DataGrid

Question

I have the following code:

using (SqlConnection cn = new SqlConnection(Connection.Instance.ConnectionString))
{
    // Open the connection
    using (SqlCommand cmd = new SqlCommand())
    {
        try
        {
            cmd.Connection = cn;

            cmd.CommandText = "Select Customers.CustomerID, Addresses.AddressCode, Addresses.FirstName, Addresses.LastName, Addresses.Address1, Addresses.City, Addresses.State, " +
            "Addresses.Zip, Addresses.Home AS HomePhone, Addresses.Phone AS WorkPhone, Addresses.EmailAddress  From Customers " +
            "LEFT OUTER JOIN Addresses ON Addresses.ID=Customers.AddressID " +
            "Where CustomerType IN ('HomeOwner', 'Home Owner') AND Customers.ResellerID=@ResellerID ";

            cmd.Parameters.AddWithValue("@ResellerID", base.UserID);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet dsCustomer = new DataSet();
            da.Fill(dsCustomer);

            var customers = from c in dsCustomer.Tables[0].AsEnumerable().AsQueryable()
                            where c.Field<string>("CustomerID") == txtSearchCriteria.Text
                            select c;

            dgCustomers.CurrentPageIndex = 0;

            dgCustomers.DataSource = customers;
            dgCustomers.DataBind();
        }
        catch (Exception e)
        {
            throw new Exception(e.Message + e.StackTrace);
        }
        finally
        {
            if ((cn != null) && (cn.State != ConnectionState.Closed))
                cn.Close();
        }
    }
}

Which is giving me the error

AllowCustomPaging must be true and VirtualItemCount must be set for a DataGrid with ID 'dgCustomers' when AllowPaging is set to true and the selected data source does not implement ICollection.   at System.Web.UI.WebControls.DataGrid.CreateControlHierarchy(Boolean useDataSource)

How do I convert this LINQ query so that it can be pagable?

Note: This is a simplified version of what I'm trying to do. I know in this example I could simply modify the SQL statement to include "And CustomerID=@CustomerID" and bypass LINQ completely. But, in the bigger picture, I can't do that.

onof · Accepted Answer · 2011-09-14 18:42:02Z

2

The error message is clear, you need to implement your paging logic to take advantage from paging. BTW, to make your code to work just use a ICollection as DataSource, changing this line:

dgCustomers.DataSource = customers.ToList();

answered Sep 14, 2011 at 18:42

onof

17.4k7 gold badges52 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error binding DataAdapter->DataSet->LINQ->ASP.Net DataGrid

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related